Give me back my MySQL Command Line!

A Warning

Although this is an extremely convenient way to work with your MySQL database (it's even quicker than using phpMyAdmin when all you want to do is try out a quick query), you need to be aware of the security issues that arise from a script like this. Basically with this script, you're handing a MySQL username and password combination to anyone who has access to the script. This can mean a number of pretty nasty things:

• Any MySQL data that the MySQL username/password combination you use in the script has access to is freely accessible with this script. User passwords and other sensitive information could be compromised as a result.
• If the MySQL user in the script has write access to any databases, those databases may be freely modified by users of this script. Write access to the 'mysql' database is an open door to the rest of the MySQL server, because users can then modify their own access privileges and gain full access to any database on the server.
• Using a SELECT INTO OUTFILE query, users can create text files on your Web server wherever the MySQL server process has write access if the MySQL user in the script has access to do so. On a Linux machine with a properly secured MySQL server, this means the MySQL data directory; however, a Windows server offers far less protection in this area. This vulnerability, in combination with an improperly-configured MySQL server was largely to blame for the recent hacking of the Apache.org Web server.

Unless you're very confident about your MySQL administration abilities, a script like this should never be left open to the world. At the very least, place it in a password-protected directory on your site! Better yet, configure the directory to refuse access to machines other than your own. The more you can protect a script like this, the better. You have been warned!

Summary

In this article I provided an alternative to the venerable MySQL command line, to which access is not provided by most PHP/MySQL hosts these days. With a relatively simple PHP script, we can allow users to type in any SQL queries they like and view the results (whether successful or not) of those queries.

A script like this is an excellent tool for beginners just learning Structured Query Language (SQL) to experiment with; however, setting up such a script requires some dilligence. If you don't take measures to secure the script from unauthorized access, you could very quickly find yourself the victim of a server hack.