HTTP Tunneling is a technique by which communications performed using various network protocols are encapsulated using the HTTP protocol, the network protocols in question usually belonging to the TCP/IP family of protocols.
The HTTP protocol therefore acts as a wrapper for a covert channel that the network protocol being tunneled uses to communicate.
In brief, a "tunnel" is created between your PC and the remote MySQL server and it appears as though the remote MySQL database is run locally.
Based on the article above, we will make our own interface using Delphi and SQLYog HTTP Tunnel script.
<xml>The XML is consists of two parts, one for connection information and one for query information, the attribute e='0', it's mean the value is encrypted (base64encode) or not, if you want to make it more secure, just set the e attribute = '1', but, don't forget to encode each value, for example, localhost for host value in base64encode
<connect_info>
<host e='0'>localhost</host>
<user e='0'>your_hosted_mysql_username</user>
<password e='0'>your_hosted_mysql_password</password>
<db e='0'>your_hosted_mysql_databasename</db>
<charset e='0'>utf8</charset>
<port e='0'>your_local_mysql_port</port>
</connect_info>
<query_info>
<query b='0' e='0'>SELECT * FROM table</query>
<querylen>19</querylen>
</query_info>
</xml>
the attribute b='0' in query info mean, the query is in batch mode or not, you can set this to '1' if you are executing multiple query or non result set query.<host e='1'>bG9jYWxob3N0</host>
Now that we know the input parameter, how about the result? yup, the script result is in XML too. Here's an example result, i'm using simple SHOW DATABASES query.
<xml>As we can see, the result returned from the script is base64decoded, and here's an example result, in case any error returned from MySQL
<result v="8.21">
<e_i></e_i>
<s_v>5.0.22-community-nt</s_v>
<m_i></m_i>
<a_r>12</a_r>
<i_i>0</i_i>1
<f_i c="1">
<f>
<n>Database</n>
<t>SCHEMATA</t>
<m>18</m>
<d></d>
<ty>varchar</ty>
</f>
</f_i>
<r_i c="12">
<r>
<c l="18">aW5mb3JtYXRpb25fc2NoZW1h</c>
</r>
<r>
<c l="5">bXlzcWw=</c>
</r>
</r_i>
</result>
</xml>
<xml>the different is the e_i (error information) tag, e_n stand for error number and e_d stand for error description. Succesfull query returning empty child for e_i tag. You can see more detailed explanation in the php script.
<result v="8.21">
<e_i>
<e_n>1064</e_n>
<e_d>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Memo1' at line 1
</e_d>
</e_i>
</result>
</xml>
So, now we know the result, it's time to make the interface step by step
- We are assuming, all the requirements is already configured, like MySQL, web server, in this example we are using Apache, running in localhost.
- Download and install the trial version of SQLyog from the link above.
- Locate and upload the script from instalation directory (i.e: C:\Program Files\SQLyog Trial\SQLyogTunnel.php) to your webserver.
- Test the script availibility by open your browser and navigate to ie: http://localhost/SQLyogTunnel.php
- After the tunnel script installed and running, now open Delphi and create new project
- Add two TMemo, a TTable, a TDataSource, a TDBGrid
- Set the Dataset property of Datasource1 to Table1, and Datasource property of DBGrid1 to DataSource1
- Add a TIdHTTP (from Indy Clients tab) and a TIdAntiFreeze (from Indy Misc tab)
- For decoding and encoding value, C&P these base64 decode/encode function:
function Base64Encode(const S: string): string;
var
InBuf: array[0..2] of Byte;
OutBuf: array[0..3] of Char;
iI, iJ: Integer;
begin
SetLength(Result, ((Length(S) + 2) div 3) * 4);
for iI := 1 to ((Length(S) + 2) div 3) do begin
if Length(S) < (iI * 3) then
Move(S[(iI - 1) * 3 + 1], InBuf, Length(S) - (iI - 1) * 3)
else
Move(S[(iI - 1) * 3 + 1], InBuf, 3);
OutBuf[0] := B64Table[((InBuf[0] and $FC) shr 2) + 1];
OutBuf[1] := B64Table[(((InBuf[0] and $3) shl 4) or ((InBuf[1] and $F0) shr 4)) + 1];
OutBuf[2] := B64Table[(((InBuf[1] and $F) shl 2) or ((InBuf[2] and $C0) shr 6)) + 1];
OutBuf[3] := B64Table[(InBuf[2] and $3F) + 1];
Move(OutBuf, Result[(iI - 1) * 4 + 1], 4);
end;
if Length(S) mod 3 = 1 then begin
Result[Length(Result) - 1] := '=';
Result[Length(Result)] := '=';
end else if Length(S) mod 3 = 2 then
Result[Length(Result)] := '=';
end;
function Base64Decode(const S: string): string;
var
OutBuf: array[0..2] of Byte;
InBuf : array[0..3] of Byte;
iI, iJ: Integer;
begin
if Length(S) mod 4 <> 0 then raise Exception.Create('Base64: Incorrect string format');
SetLength(Result, ((Length(S) div 4) - 1) * 3);
for iI := 1 to (Length(S) div 4) - 1 do begin
Move(S[(iI - 1) * 4 + 1], InBuf, 4);
for iJ := 0 to 3 do
case InBuf[iJ] of
43: InBuf[iJ] := 62;
48..57: Inc(InBuf[iJ], 4);
65..90: Dec(InBuf[iJ], 65);
97..122: Dec(InBuf[iJ], 71);
else
InBuf[iJ] := 63;
end;
OutBuf[0] := (InBuf[0] shl 2) or ((InBuf[1] shr 4) and $3);
OutBuf[1] := (InBuf[1] shl 4) or ((InBuf[2] shr 2) and $F);
OutBuf[2] := (InBuf[2] shl 6) or (InBuf[3] and $3F);
Move(OutBuf, Result[(iI - 1) * 3 + 1], 3);
end;
if Length(S) <> 0 then begin
Move(S[Length(S) - 3], InBuf, 4);
if InBuf[2] = 61 then begin
for iJ := 0 to 1 do
case InBuf[iJ] of
43: InBuf[iJ] := 62;
48..57: Inc(InBuf[iJ], 4);
65..90: Dec(InBuf[iJ], 65);
97..122: Dec(InBuf[iJ], 71);
else
InBuf[iJ] := 63;
end;
OutBuf[0] := (InBuf[0] shl 2) or ((InBuf[1] shr 4) and $3);
Result := Result + Char(OutBuf[0]);
end else if InBuf[3] = 61 then begin
for iJ := 0 to 2 do
case InBuf[iJ] of
43: InBuf[iJ] := 62;
48..57: Inc(InBuf[iJ], 4);
65..90: Dec(InBuf[iJ], 65);
97..122: Dec(InBuf[iJ], 71);
else
InBuf[iJ] := 63;
end;
OutBuf[0] := (InBuf[0] shl 2) or ((InBuf[1] shr 4) and $3);
OutBuf[1] := (InBuf[1] shl 4) or ((InBuf[2] shr 2) and $F);
Result := Result + Char(OutBuf[0]) + Char(OutBuf[1]);
end else begin
for iJ := 0 to 3 do
case InBuf[iJ] of
43: InBuf[iJ] := 62;
48..57: Inc(InBuf[iJ], 4);
65..90: Dec(InBuf[iJ], 65);
97..122: Dec(InBuf[iJ], 71);
else
InBuf[iJ] := 63;
end;
OutBuf[0] := (InBuf[0] shl 2) or ((InBuf[1] shr 4) and $3);
OutBuf[1] := (InBuf[1] shl 4) or ((InBuf[2] shr 2) and $F);
OutBuf[2] := (InBuf[2] shl 6) or (InBuf[3] and $3F);
Result := Result + Char(OutBuf[0]) + Char(OutBuf[1]) + Char(OutBuf[2]);
end;
end;
end; - Don't forget to add these code on top your code:
const
B64Table = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'; - Add OnClick event handler for Button1
procedure TForm1.Button1Click(Sender: TObject);
var
param: TStringList;
xmlresult: TStringList;
begin
mmStatus.Lines.Clear;
param := TStringList.Create;
xmlresult := TStringList.Create;
try
try
param.Add('<xml>' +
'<connect_info>' +
'<host e=''0''>localhost</host>' +
'<user e=''0''>root</user>' +
'<password e=''0''>*******</password>' +
'<db e=''0''>mysql</db>' +
'<charset e=''0''>utf8</charset>' +
'<port e=''0''>3306</port></connect_info>' +
'<query_info>' +
'<query b=''0'' e=''0''>'+Memo1.Lines.Text+'</query>' +
'<querylen>'+IntToStr(Length(Memo1.Lines.Text))+'</querylen>' +
'</query_info>' +
'</xml>');
IdHTTP1.Request.ContentType := 'text/xml';
xmlresult.Text:= IdHTTP1.Post('http://localhost/SQLyogTunnel.php', param);
xmlresult.SaveToFile('.\result.xml');
except
on E: Exception do
ShowMessage('Error encountered during POST: ' + E.Message);
end;
XMLToTable(Table1);
finally
param.Free;
xmlresult.Free;
end;
end; - Add OnStatus event handler of IdHTTP1
Memo2.Lines.Add( AStatusText );
- Add OnWork event handler of IdHTTP1,
if AWorkMode = wmRead then
Memo2.Lines.Add(Format('Working: READ: %d', [AWorkCount]))else
Memo2.Lines.Add(Format('Working: WRITE: %d', [AWorkCount])); - XMLToTable function
Interface section:
procedure XMLToTable(tbl: TTable);
Implementation section:
procedure TForm1.XMLToTable(tbl: TTable);
var
iResNode, iError_InfoNode, iField_Info_Node, iFields, iRow_Info_Node, iRows, iRowCol : IXMLNode;
colCount, rowCount, i, errNo: Integer;
fieldtype: TFieldType;
colValue: Variant;
colName, colMaxLen, colType: Variant;
errDesc: string;
begin
tbl.Close;
//clear temporary table fields
tbl.FieldDefs.Clear;
xml.LoadFromFile('.\result.xml');
xml.Active := True;
//result
iResNode := xml.DocumentElement.ChildNodes.First;
if iResNode.NodeName = 'result' then begin
// Get Error Info: e_i
iError_InfoNode := iResNode.ChildNodes.FindNode('e_i');
if iError_InfoNode.HasChildNodes then begin
// Get Error Number: e_n
errNo := iError_InfoNode.ChildNodes.FindNode('e_n').NodeValue;
// Get Error Description: e_d
errDesc := iError_InfoNode.ChildNodes.FindNode('e_d').NodeValue;
MessageDlg(Format('Error #%d: %s', [errNo, errDesc]), mtError, [mbOK], 0);
Exit;
end;
// Get Field Info: f_i
iField_Info_Node := iResNode.ChildNodes.FindNode('f_i');
if iField_Info_Node <> nil then begin
//get column count: c
colCount := Integer(iField_Info_Node.Attributes['c']);
if colCount > 0 then begin
tbl.Fields.Clear;
iFields := iField_Info_Node.ChildNodes.First; // f
// field defenition
while iFields <> nil do begin
// column name
if iFields.ChildNodes.FindNode('n') <> nil then
colName := iFields.ChildNodes.FindNode('n').NodeValue;
// column type
if iFields.ChildNodes.FindNode('ty') <> nil then
colType := iFields.ChildNodes.FindNode('ty').NodeValue;
// column max length
if iFields.ChildNodes.FindNode('m') <> nil then
colMaxLen := iFields.ChildNodes.FindNode('m').NodeValue;
if (colType = 'varchar') or (colType = 'char') then fieldtype := ftString
else if colType = 'longtext' then fieldtype := ftMemo
else if colType = 'date' then fieldtype := ftDate
else if colType = 'time' then fieldtype := ftTime
else if colType = 'datetime' then fieldtype := ftDateTime
else if (colType = 'int') or (colType = 'tinyint') or (colType = 'mediumint') or (colType = 'smallint') then fieldtype := ftInteger
else if VarToStr(colType) = '' then fieldtype := ftDateTime
else fieldtype := ftUnknown;
// create the columns
if (fieldtype = ftString) then
tbl.FieldDefs.Add(colName, fieldtype, Integer(colMaxLen))
else
tbl.FieldDefs.Add(colName, fieldtype);
iFields := iFields.NextSibling;
end;
tbl.CreateTable;
// get row info: r_i
iRow_Info_Node := iResNode.ChildNodes.FindNode('r_i');
rowCount := Integer(iRow_Info_Node.Attributes['c']);
if rowCount > 0 then begin
tbl.Open;
iRows := iRow_Info_Node.ChildNodes.First; // r
while iRows <> nil do begin
iRowCol := iRows.ChildNodes.First; // c
tbl.Append;
i := 0;
while iRowCol <> nil do begin
if (iRowCol.Text = '(NULL)') or (iRowCol.Text = '_') then colValue := '' else colValue := Base64Decode(iRowCol.Text);
tbl.Fields[i].AsVariant := colValue;
iRowCol := iRowCol.NextSibling;
Inc(i);
end;
tbl.Post;
iRows := iRows.NextSibling;
end;
end; // rowCount > 0
end; // colCount > 0
end; // iField_Info_Node <> nil
end; //iResNode.NodeName = 'result'
xml.Active := False;
tbl.Open;
end; - And finally, OnCreate event handler for Form1
Table1.Close;
Table1.DatabaseName := ExtractFilePath(ParamStr(0));
Table1.TableName := 'temp.db';
Table1.TableType := ttParadox; - That's it, test it, hit F9, put some query and hit the button. :)
You can download the complete source code here
