Pasimbung, Garambang, Sulengka: Connecting to MySQL Server Through HTTP Tunnel

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.

First, we need to know how the tunnel script working. The script it self, included in SQLyog setup, you can download trial verison from their website. the script is written in php, it takes one POST parameter in XML format here's the example:

<xml>
<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 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

<host e='1'>bG9jYWxob3N0</host>

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.

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>
    <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>

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

<xml>
    <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>

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.

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. :)