Give me back my MySQL Command Line!

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

Give me back my MySQL Command Line!
Fig. 1: The Web-Based MySQL Command Line

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

Give me back my MySQL Command Line!
Fig. 2: Displaying the results of a simple query

That 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 (&lt;, &gt;, and &amp; 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, 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 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.

Replay

Category: programming Time: 2001-12-07 Views: 4
Tags:

Related post

  • How do I configure MySQL command line client key bindings? 2011-04-04

    I often use the mysql command-line client on an Ubuntu box. It works fine, except that the key bindings are wrong. Specifically, pressing "Home", "End", or "Delete" makes it output a ~ (tilde) character, and beep. I've determ

  • Fix Control-C in mysql command line programme? 2009-07-15

    In the mysql command line, pressing Control-C will cancel the programme, and bring you back to bash. In psql, the one for postgres, it will kill the current query and will not stop the psql programme. Is there any way to get the psql style behaviour

  • mysql command line not working 2011-01-12

    I have mysql running in my fedora system. I have xampp setup on the system and php projects present in the webspace are working fine. PhpMyAdmin is working fine. echoing phpinfo() in a PHP script also shows mysql enabled. But running mysql connect co

  • Replace in MySQL without entering MySQL command line? 2011-08-26

    Currently I'm using mysql -u root -p and fill the password and then in MySQL command line, I'm using: use dbname; update node_revisions set body = replace(body,'textone','texttwo'); How can I doing this in terminal without entering MySQL command line

  • MySQL command line color prompt 2012-03-05

    I want to add colors to the MySQL command line color prompt. I have so far in a script (database.sh): mysql -uroot -hlocalhost -A --prompt="\[email protected]\h:\d> " I would like root to be red, @ to be blue, localhost to be green and database to be cyan:

  • php pdo connection cannot be established, but mysql command line client can 2014-07-02

    situation as follows: php script on server a, run by user 'web' (nginx + php-fpm) should access mysql on server b via pdo library, but gets SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (4) server a centos 6.5, php 5.3; se

  • Unable to import dump.sql via a .sql file which will be run through mysql command line 2014-12-04

    I have a MySQL database that contains almost 100 tables. I want to set up N additional MySQL databases on the same server, each running on a different port. And I want each additional database to have the same schema/table structures as the original

  • Mac Terminal: How to get back to the command line? 2011-03-30

    I'm on a Mac OS X 10.6.7. I started a node.js script, which starts an HTTP server. The problem is that I lost the command line, it looks like this: $me cd directory $me node test.js Message from test.js: Server is running on localhost ..| | = indicat

  • How to promote nodes to front page using MySQL command line 2011-05-06

    What is the command to promote unpromoted nodes to front page using MySQL command line? I want to promote 200 nodes at once. The node type is "story." --------------Solutions------------- UPDATE node SET promote=1 WHERE type="story" AN

  • How to update path alias using MySQL command line 2011-05-12

    Many words have been renamed in the node using the below code: update node set title = replace(title,'Apple','Orange') update node_revisions set title = replace(title,'Apple','Orange'); But now I need to know how to update the path aliases using the

  • Is there a password agent for the mysql command-line client? 2011-09-21

    I use the mysql command line client frequently. Sometimes I wind up typing in my password dozens of times per day, and I'm getting really tired of it. SSH has a neat utility called ssh-agent which lets you type in your password just once at the begin

  • How to change the display of an empty string in mysql command line tool? 2012-09-05

    I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table): `val0` varchar(30) default NULL, `val1` varchar(30) default NULL, etc... From the mysql command line I am searching for NULL entries in th

  • How can I use Mysql command line client in Ubuntu 12.04? 2013-01-16

    I just upgraded Ubuntu 11.10 to 12.04 (installed within windows 7) and during installation the process asked me to set my Mysql root password. According to Ubuntu Documentation, Mysql server is pre-istalled in Ubuntu version 12.04. However, when I se

  • Clear screen for MYSQL command line client 2013-11-22

    I am unable to clear the screen of the mysql command line client like I know in case of java it is 'cls'. --------------Solutions------------- There is a number of solutions in Linux like this one, but the consensus seems to be there is none for Wind

  • Creating a database with only MySQL command line? 2014-08-07

    The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable to "build" the correct install (he knows much

  • MacOSX Terminal with MYSQL command line how to highlight and copy/paste 2015-01-20

    Is there a default keyboard command when using the Terminal on MacOSX while in MYSQL command line for highlighting and copying/pasting text? I'm looking for a native solution without having to use another program to achieve this. I find myself writin

  • Clear screen for MYSQL command line client 3 2015-07-09

    I tried many of the commands clear the screen in mysql command line on windows but not working... commands are clear screen; cl scr; cls; system clear; system clear screen; ctrl + l; tell me the solution if any body have idea in this topic.... ------

  • Can't open MySQL Command Line Client on Mac 2016-01-22

    This is a little bit embarrassing, but I've been working in MySQLWorkbench and have created a database and a local connection. I now want to create a remote connection, but to do this I need to allow connections which is done through the MySQL Comman

  • Why is MySQL command line so fast vs. Python? 2016-02-24

    I need to migrate data from MySQL to Postgres. It's easy to write a script that connects to MySQL and to Postgres, runs a select on the MySQL side and inserts on the Postgres side, but it is veeeeery slow (I have + 1M rows). The MySQL command line ca

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.858 (s). 13 q(s)