Give me back my MySQL Command Line!
About the Author
Kevin Yank
Kevin began developing for the Web in 1995 and is a highly respected technical author. He wrote Build your own Database Driven Website using PHP and MySQL, a practical step-by-step guide published by SitePoint, and he's co-author of the SitePoint Tech Times, a bi-weekly newsletter for technically-minded web developers. Kev believes that any good webmaster should have seen at least one episode of MacGyver.
By: Kevin Yank
December 7th, 2001
One of the essential skills you must acquire to become proficient in the development of PHP/MySQL driven websites is a good understanding of Structured Query Language (SQL). In Chapter 2 of my article series, Build your own Database Driven Website using PHP & MySQL [1], I focus on getting beginners comfortable with typing SQL queries on the MySQL command line.
A common problem faced by people getting started with MySQL is that most Web hosts these days don't provide shell access to the server, nor do they allow remote connections to their MySQL servers. The net result is that the MySQL command line is not available to users of most Web hosts. To learn SQL, developers are often forced to install a MySQL server on their own computer just to have a command line to play with.
In this article, I'll provide a convenient alternative -- a Web-based MySQL command line! Written in PHP, this script will let you type SQL queries into a text field and view the results or, in the case of error, any error messages generated.
We'll look at each component of the script separately, and I'll provide the complete code at the end of the article, so focus on the code segment at hand, and we'll see how it all fits together at the end.
A Simple Form
The look of our Web-based command line (Fig. 1) will be only slightly more advanced than the simplistic interface we are trying to emulate.
Fig. 1: The Web-Based MySQL Command Line
Fig. 1: The Web-Based MySQL Command LineThe only real convenience feature we've added to the interface is a drop-down list, to indicate the database that will be the target of the query. To generate that list, we'll need a connection to the MySQL database server, so that's what our script begins with:
<?php
// FILL THESE IN WITH YOUR SERVER'S DETAILS
$mysqlhost = 'localhost';
$mysqlusr = 'user';
$mysqlpass = 'password';
mysql_connect($mysqlhost,$mysqlusr,$mysqlpass);
?>
<html>
<head><title>MySQL Command Line</title></head>
Now, to generate the drop-down list in the form, we need to retrieve a list of the databases in the MySQL server. We do that with the PHP function mysql_list_dbs, then we use a for loop to go through that list and use the mysql_db_name function to produce an <option> in the <select> list for each database:
<p>Target Database:
<select name="db">
<?php
$dbs = mysql_list_dbs();
for ($i=0;$i<mysql_num_rows($dbs);$i++) {
$dbname = mysql_db_name($dbs,$i);
echo("<option>$dbname</option>");
}
?>
</select>
</p>
The rest of the form is pretty straightforward -- a <textarea> and a submit button. For convenience, I've set the <textarea> to select the text it contains whenever it gains focus (notice the onFocus attribute). I've also assigned an accesskey attribute to the submit button to allow it to be triggered with the keyboard:
<p>SQL Query:<br />
<textarea onFocus="this.select()" cols="60" rows="5" name="query">
</textarea>
</p>
<p><input type="submit" name="submitquery" value="Submit Query (Alt-S)"
accesskey="S" /></p>
As for the <form> tag that contains all these elements, it will be set to submit the form back to the same URL using the PHP variable $PHP_SELF as the action attribute:
<form action="<?=$_SERVER['PHP_SELF']?>" method="POST">
...
</form>
So in addition to displaying the form, this one script also needs to process the form submission. We'll look at the code to do that next.
Handling Queries
Our script can tell when it is being run as a result of a submission of the form we just saw by checking for the presence of the $submitquery variable. This variable is produced by the submit button in the form, the name attribute of which was set to "submitquery". Thus, the code to process form submissions will begin with this if statement:
if (isset($_POST['submitquery'])) {
Now, there is a feature of PHP called magic quotes that experienced PHP developers will be quite familiar with. Basically, when it is enabled, all values submitted to a script (e.g. as a result of a form submission) are automatically escaped by adding backslashes before special characters like quotes ('), which if not marked with backslashes in this way would interfere with SQL queries.
The problem is that, in this case, the value passed from the form ($_POST['query']) is the query, and if it is escaped by the magic quotes feature, it won't work as intended. The PHP function stripslashes can undo the escaping that is done by magic quotes, but if magic quotes is turned off then stripslashes can similarly prevent a query from working properly. We therefore need to determine if magic quotes is enabled, and if so unescape the query with stripslashes. The magic quotes setting can be detected with get_magic_quotes_gpc:
if (get_magic_quotes_gpc()) $_POST['query'] = stripslashes($_POST['query']);
Next, we print out the query for the user's benefit, so that if the results weren't quite what he or she expected, the query will be there to help determine the cause. We use the nl2br function to convert linebreaks in the query into <br> tags, so that the query is displayed as typed.
echo('<p><b>Query:</b><br />'.nl2br($_POST['query']).'</p>');
With the preliminaries out of the way, we can finally execute the query ($_POST['query']) against the MySQL database specified by the $_POST['db'] variable, which comes from the drop-down list in the form:
mysql_select_db($_POST['db']);
$result = mysql_query($_POST['query']);
Then we verify whether the MySQL query was successful by checking if the $result variable we have just obtained is 'true':
if ($result) {
Now we know that the query executed successfully, but we don't know whether it returned a result set. SQL queries such as SELECT, DESCRIBE, and SHOW TABLES all produce sets of results, while queries such as INSERT, UPDATE, and DELETE only tell you how many rows were affected by their execution. We can determine whether a query returned a result set by using the mysql_num_rows function, which attempts to determine the number of rows returned from a result set:
if (@mysql_num_rows($result)) {
Since trying to call mysql_num_rows on a $result variable that does not correspond to a result set will produce an error message, we put an @ symbol in front of the function name to suppress such a message if it occurs.
Having determined that we have a result set on our hands, we need to output it for the user. We'll take a look at the code to do that in the next section. For now, let's see what happens when $result doesn't point to a result set -- the else clause of the if statement above:
} else {
echo('<p><b>Query OK:</b> '.mysql_affected_rows().
' rows affected.</p>');
}
In this case, we know the query executed successfully (because $result evaluated to 'true'), but mysql_num_rows failed or returned zero. So we're either dealing with an empty result set, or a query that doesn't return a result set. In either case, we print out the number of rows affected by the query with the mysql_affected_rows function.
Finally, we need an else clause for the if statement that checks if the query succeeded. In the event that a query fails, we need to display the error message produced with mysql_error:
} else {
echo('<p><b>Query Failed</b> '.mysql_error().'</p>');
}
That covers everything in our query handling code except the code to display result sets, which we'll look at now.
Displaying Result Sets
Since results sets are displayed as tables on the MySQL command line, it only makes sense to do the same for our Web-based equivalent. First, we must determine the columns that exist in the result set, and put one column for each in our table. We use the mysql_num_fields function to determine the number of columns in our result set, then use mysql_field_name to grab their names:
<p><b>Result Set:</b></p>
<table border="1">
<thead>
<tr>
<?php
for ($i=0;$i<mysql_num_fields($result);$i++) {
echo('<th>'.mysql_field_name($result,$i).'</th>');
}
?>
</tr>
</thead>
With our row of column names in place, creating the table is a simple matter of looping through the rows of the result set, writing the values in each column into a table cell. Aside from the use of mysql_num_fields again to determine the number of columns, this is pretty standard stuff for experienced PHP coders, so if you're new to PHP don't worry -- it will all become clear as you gain more experience in the language (see Chapter 4 [2] of my PHP/MySQL series for a good tutorial on this stuff).
<tbody>
<?php
while ($row = mysql_fetch_row($result)) {
echo('<tr>');
for ($i=0;$i<mysql_num_fields($result);$i++) {
echo('<td>'.$row[$i].'</td>');
}
echo('</tr>');
}
?>
</tbody>
</table>
Fig. 2 shows what the output of this code looks like for a simple database query.
Fig. 2: Displaying the results of a simple queryThat covers the bulk of the code for the script! All that's left is to put it all together.
Putting It All Together
Here's a structural view of the script to help you get your bearings:
<?php
// OPEN DATABASE CONNECTION
?>
<html>
<!-- PAGE HEADER -->
<body>
<?php
if (QUERY SUBMITTED) {
// COMPENSATE FOR MAGIC QUOTES
// PRINT OUT THE QUERY
// EXECUTE THE QUERY
if (QUERY SUCCESSFUL) {
if (NON-EMPTY RESULT SET PRODUCED) {
// DISPLAY THE RESULT SET IN A TABLE
} else {
// DISPLAY THE NUMBER OF ROWS AFFECTED
}
} else {
// DISPLAY THE MYSQL ERROR MESSAGE
}
}
?>
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<p>Target Database:
<!-- DISPLAY THE LIST OF DATABASES -->
</p>
<p>SQL Query:<br />
<!-- QUERY TEXT AREA -->
</p>
<p><!-- SUBMIT BUTTON --></p>
</form>
</body>
</html>
Notice that the query form is displayed both before and after the user submits the query. This allows the user to type a new query while viewing the results of the previous query he or she submitted. This is especially useful for correcting mistakes, and in light of this fact we can introduce a couple of usability tweaks.
First, since what the user will almost always do first is type a query, we can place the text cursor in the appropriate field in advance. We do this by adding a little JavaScript to the <body> tag:
<body onLoad="document.forms[0].elements['query'].focus()">
Next, since the user is likely to want to work with the same database from query to query, we'll make sure that the database that was selected in the previous query is selected by default in the drop-down menu of databases. We do this by adding a selected attribute when the database name in the <option> tag matches the submitted $db variable:
$dbname = mysql_db_name($dbs,$i);
if ($dbname == $_POST['db'])
echo("<option selected>$dbname</option>");
else
echo("<option>$dbname</option>");
Finally, we'll copy the previously-submitted query into the <textarea> so that the user can easily make minor changes to and resubmit the same query. The fact that the <textarea> has been set up to highlight its contents when it gains focus makes this especially convenient. We use the htmlspecialchars function to convert any special HTML characters (such as <, >, and &) present in the query into their HTML entity equivalents (<, >, and & respectively).
<p>SQL Query:<br />
<textarea onFocus="this.select()" cols="60" rows="5" name="query">
<?=htmlspecialchars($_POST['query'])?>
</textarea>
</p>
That should do it! The completed script may be grabbed here [3], but before you place it on your server and try it out, be sure to read the warning on the next page...
A Warning
Although this is an extremely convenient way to work with your MySQL database (it's even quicker than using phpMyAdmin [4] 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 OUTFILEquery, 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.
[1] http://www.sitepoint.com/article/527
[2] http://www.webmasterbase.com/article.php/530
[3] http://www.webmasterbase.com/examples/mysqlcmdln/mysqlcmdln.php.txt
[4] http://www.phpwizard.net/projects/phpMyAdmin/