Connecting to MySQL with Remote Access Disabled using RazorSQL via PHP
Many times, web hosting providers include MySQL as part of their hosting packages, but do not allow users to connect to the MySQL database from a remote location. This forces users to use a web based application to access their MySQL database instance, or resort to complex solutions such as SSH tunneling. The RazorSQL MySQL PHP Bridge allows users to connect to a MySQL database with the simple deployment of a PHP page, and allows them to get the added functionality of a desktop application like RazorSQL as opposed to the limited functionality of web-based database access.
The RazorSQL MySQL PHP Bridge provides the ability for users to interact with a MySQL database without making a direct connection to the database. Instead, the user connects to the RazorSQL MySQL PHP Bridge, which then connects to the database. The RazorSQL MySQL PHP Bridge is a PHP program that can be deployed on any web server that supports PHP and has the necessary MySQL PHP libraries installed. Since the PHP page resides on the hosting provider's web server, even if remote access is disabled on the MySQL database, the PHP page will still be able to connect.
As an added bonus, if your web server supports SSL, the RazorSQL MySQL PHP Bridge can be used to securely transmit your data from your desktop to your MySQL database instance.
Requirements
The RazorSQL MySQL PHP Bridge requires a web server with PHP support and the MySQL PHP libraries must be installed and configured.
Deployment
All of the RazorSQL PHP and ASP bridges are located in the following zip file. The MySQL PHP bridge
is named razorsql_mysql_bridge.php.
RazorSQL Bridges
The RazorSQL MySQL PHP Bridge file is also located here:
<RazorSQL Install Directory>/bridge/razorsql_mysql_bridge.php
To deploy the bridge, simply copy this file to a PHP enabled web server. Typically this can be done either using an FTP client, or using your web host's control panel software.
Also included is a test page to check to see if MySQL can be accessed via the web server. The test page is located here:
<RazorSQL Install Directory>/bridge/test_mysql_setup.php
To test the MySQL setup, simply copy this file to a PHP enabled web server, access the page, and follow the instructions.
Security
The RazorSQL MySQL PHP Bridge can work via either HTTP or HTTPS. If retrieving sensitive data, it is recommended that HTTPS be used.
The RazorSQL MySQL PHP Bridge provides the ability to supply a bridge password. Only calls to the brige that include a correct password will be handled. The bridge ships with a default password of radmin. It is recommended that this password be changed. To change the password, simply replace the radmin in the following line of the bridge PHP page with another password.
$checkPassword = 'radmin';
Accessing the Bridge
Once the bridge PHP page is deployed to a web server, the bridge can be called via either HTTP or HTTPS if your web server supports SSL.
To set up a connection profile
that communicates via the MySQL PHP bridge, simply access the Connections -> Add Connection
Profile menu option, select MySQL as the database type and RazorSQL MySQL PHP Bridge
as the connection type. There are then fields displayed such as Bridge URL, Bridge Password,
etc. Listed below is more information on these fields:
Bridge URL:
Enter the web address of the deployed bridge page in this field. For example, http://www.myhost.com/razorsql_mysql_bridge.php.
Bridge Password:
Enter the bridge password in this field. The default is radmin. Enter your own custom password
if you modified the bridge php page to change the default password.
Login: This is your MySQL user id.
Password: This is your password to your MySQL database.
Host or IP Address: This is the host or IP address of your MySQL database. This is
different depending on the web host. For GoDaddy, the host is typically something similar to
*.secureserver.net. For Yahoo, the host is typically simply mysql. For other web hosts,
the host may be localhost, or another name.
Port: The port is generally the default port of 3306.
Database Name: If you have created a MySQL database that you wish to connect to,
enter it here.
Basic Auth. / .htaccess Info.: For added security, some users protect the razorsql bridge page with
some form of basic authentication protection such as using a .htaccess file to require a user name and password before
the web server will allow access to the bridge page. If the bridge page is protected in this manner, click the button
next to this field and add the appropriate user name and password to access the bridge page.
Since the bridge does not hold on to connections, but instead opens and closes connections for each call, the bridge cannot do multiple fetches to the database for the same query. Because of this, there is a field call "max rows to return" located in the add connection profile screen when setting up the bridge. The value of this field tells the bridge the maximum number of rows to fetch per query. If set too high, this could cause performance problems.
Limitations
There are certain features of RazorSQL that are not available when using the RazorSQL MySQL PHP Bridge. The Execute Procedure/Function tool is not available. Also, all connections via the bridge are set to auto commit since connections are not held onto by the bridge.
