Think beyond phpMyAdmin to access MySQL on a shared host

So, do you really need phpMyAdmin to access MySQL database on a shared hosting?

NO, you don’t need it, but popular hosting services like GoDaddy integrate phpMyAdmin to facilitate access to MySQL server. Of course phpMyAdmin is a nice tool, no offense, but what if you prefer a desktop application over phpMyAdmin, or you are a developer working on an application that needs to access the MySQL database on a shared hosting? That desktop application I’m referring to is SQLyog. So how does SQLyog access the MySQL database on a shared hosting? And how you can do the same with your own application?

The Problem

Shared hosting is your best option if you have a personal website, or you own a small business and wish to leverage your online presence to get it noticed. They are cheap, you don’t have to manually configure the MySQL server settings, without worrying about security. Your website might have some dynamic content and a contact page that will read/write into the MySQL database.

So far, so good. The problem arises when you want to read/write to the MySQL database from your desktop application. But why would you even want a desktop application to access the MySQL database on a shared hosting? Common use cases include scheduled backups, schema migration, etc or maybe you are doing it just for fun. Unfortunately shared hosting in general won’t allow direct access to MySQL (typical host : port method); so your solution of using C/C++/C#/Java with libmysql or various connectors is bound to fail. Also, they don’t allow SSH tunneling. Now, you’re left with the only option of using phpMyAdmin. Wait a second, if that was the case I wouldn’t be writing this blog post. Would I?

The Solution

The problem tells us two things.

  • No direct access to MySQL.
  • Your other web pages and phpMyAdmin can access MySQL. And you can browse them using Firefox.

There can be two reasons for this; either a firewall preventing access to the MySQL port or MySQL is configured to connect only with local accounts. Basically, it is asking you to connect to MySQL the same way your other web pages do. So the solution is to first connect to the web server on your shared hosting and then connect to MySQL. Create a special page that connects to your MySQL database and execute the query you sent. We call this process HTTP tunneling because from your local machine to the remote host (or remote network) data transfer happens using the HTTP protocol as an XML (or JSON) data stream. The rest of the post explains how to create such an application.

Tools and Technologies

Here I will be using PHP for server side scripting. The script will be then uploaded to the web server and transfer the data in XML format back to our program. We need an XML parser so that we can read the data embedded in XML. I recommend TinyXml as it is simple and sufficient for the purpose. For application development I use C/C++ with Win32. We also need to implement the communication layer between our program and the PHP script. Anyone with the knowledge on socket programming can implement this in their C/C++ application. If socket programming sounds scary, then you might consider using some high level HTTP library. In fact it is recommended that you go for a high level library which does all the heavy lifting for you. I will be using WinINet library as we require HTTP protocol only.

Needless to say, all these tools and technologies can be substituted. For example you can use C# to develop the program, JSON as data transfer format, Curl library to build the communication layer and ASP as the server side script.

Data Transfer Format (XML)
So, lets get started with the XML design.

<!ELEMENT myhttp (versioninfo, table, noresult, error)>
<!ELEMENT versioninfo EMPTY>
<!ATTLIST versioninfo
         mysqlversion CDATA #REQUIRED
         tunnelversion CDATA #REQUIRED>
<!ELEMENT table (tr )>
<!ATTLIST table
         rowcount CDATA #REQUIRED
         colcount CDATA #REQUIRED>
<!ELEMENT tr ((th ) | (td ))>
<!ELEMENT th (#PCDATA)>
<!ELEMENT td (#PCDATA)>
<!ATTLIST th
         length CDATA #REQUIRED>
<!ELEMENT noresult EMPTY>
<!ATTLIST noresult
         affectedrows CDATA #REQUIRED
         warningcount CDATA #REQUIRED>
<!ELEMENT error EMPTY>
<!ATTLIST error
         code CDATA #REQUIRED
         message CDATA #REQUIRED>

This is the DTD for the XML needed We have a <versioninfo> tag that carries MySQL and Tunnel script versions. Then there is a <table> tag which is like a standard HTML table tag with some additional attributes indicating the number of rows and columns in the result. <th> tag in <table> has a length attribute carrying the size of the largest value in the column. We will use this length to provide proper spacing between columns while printing the result. Tag <error> represents the error code and message the query generated, <noresult> represent the DML/DDL queries. Please note that even though we execute only a single query at a time, the XML response can have multiple instances of these tags in case the query contain CALL to a stored procedure.

Tunnel (PHP Script)
Now we need the PHP script which will work as the tunnel. A minimal PHP script that gives an XML response is given below. The sample script assumes the query executed will generate a result set. No error checking or input validation is provided.

<?php
//Frame the XML from the resultset
function GetResultXML($link)
{
   $result = mysqli_store_result($link);
   $colcount = mysqli_num_fields($result);
   $resultxml = sprintf('<table rowcount="%d" colcount="%d"><tr>', mysqli_num_rows($result), $colcount);

   //Loop through the fields and add it to th tag
   $fields = mysqli_fetch_fields($result);
   foreach($fields as $val) {
       $resultxml .= sprintf('<th length="%d">%s</th>', $val->length, $val->name);
   }

   $resultxml .= '</tr>';

   //Now fetch the rows
   while($row = mysqli_fetch_row($result)) {
       $resultxml .= '<tr>';
       for($i = 0; $i < $colcount; $i  ) {
           $resultxml .= sprintf('<td>%s</td>', is_null($row[$i]) ? '' : $row[$i]);
       }
       $resultxml .= '</tr>';
   }
   $resultxml .= '</table>';
   mysqli_free_result($result);
   return $resultxml;
}

//Connect and execute the query with the details embedded in the URL
$link = mysqli_connect($_GET['host'], $_GET['user'], $_GET['password'], $_GET['database']), $_GET['port']);
mysqli_real_query($link, $query);

//Loop through all the available results and add it to the XML
echo '<myhttp>' . GetResultXML($link) . '</myhttp>';
mysqli_close($link);
?>

As you can see, the script connects to MySQL with the details embed in the URL and then executes the query. Assuming the query was successful and returns a resultset, it iterates through the resultset to frame the XML and then echos back to the caller.

The Application (Using C++ with WinINet)
C++ code using WinINet library to access the tunnel script and print the XML received.

int main()
{
   char       caBuffer[DATALEN   1];
   DWORD       dwNumberOfBytesRead = 0;
   HINTERNET hConnect = NULL, hOpenAddress = NULL;
   string       sStr;

   //Initialize the use of WinINet
   hConnect = InternetOpenA("MyHTTP", INTERNET_OPEN_TYPE_PRECONFIG, NULL, NULL, 0);

   //Open URL
   hOpenAddress = InternetOpenUrlA(hConnect,
       "http://localhost/tunnel.php?host=localhost&user=root&password=root&port=3306&query=show databases",
       NULL, 0, INTERNET_FLAG_PRAGMA_NOCACHE, 0
       );

   //Read the response in chunks
   while(InternetReadFile(hOpenAddress, caBuffer, DATALEN, &dwNumberOfBytesRead) &&
       dwNumberOfBytesRead)
   {
       caBuffer[dwNumberOfBytesRead] = 0;
       sStr.append(caBuffer);
   }

   //Print it
   cout << sStr.c_str();

   //Cleanup
   InternetCloseHandle(hOpenAddress);
   InternetCloseHandle(hConnect);
   return 0;
}

Here the code call the URL with all the connection details and the query embedded. Once the URL is open, it reads the response and prints it in the console window. Of course we can use TinyXml library to decode the XML response and frame HTML or whatever we want, after all that is the whole point of doing it. You may download the complete source from here.

The above principles are what we use in SQLyog’s HTTP tunneling and thats how it retrieves data from a shared hosting provider. But SQLyog not only displays data, it also allows editing and it does that by processing MYSQL_RES and other C API structures. If you check SQLyog source code you will understand that there are no special code for a connection using HTTP tunnel.

SQLyog then converts the XML response into the relevant MySQL C API structures. It provides a generic interface where you can execute queries. Doesn’t matter if it is a direct connection or an indirect connection using HTTP tunneling.

Now thats something interesting! I’ll write about it in my next post.

Tweet