Building a simple SQL query interface

To pass commands to a SQL database, we need an interface that may be just a HTML page containing a form.

A more direct solution is to use PHPMyAdmin which can also pass requests, if it is installed. But this tells us nothing about how to use SQL. In fact this basic model of interface could be developed gradually thereafter to create a complete administration interface of a database.

Form of interface

A text field and a submit button to a PHP script make the core of the interface:

<form id="sql" name="sql" method="post" action="sql.php">
    <textarea name="query" cols="80" rows="4"></textarea>
    <input type="submit" name="Submit" value="Submit" />
</form>

The PHP script retrieves the contents of the query variable:

if(!isset($_POST)) die("Direct access forbidden");

include("config.php");

$query = $_POST['commande']; 

$results = mysqli_query($DB_ID, $query)
or die("$query<br>".mysqli_error());

In five lines, the script ...

1. Prohibits direct access.
2. Load the configuration file containing the names and passwords (discussed in detail later).
3. Gets the query sent by the form.
4. Performs the query.
5. If unsuccessful, displays the error.

Secret code

To deny access to our database to unscrupulous visitors, it will be protected by a secret code. You can use the password of the database or create a special code.
This second option allows us to encode it by MD5, the best choice.

Creating the code

The md5 function of PHP generates an encrypted code from a password or a sentence of any length.

$md5code = "code";
echo md5($x);

The code entered with the form will then be tested in the same way:

$code = $_POST['code']; 
if(md5($code) != $md5code)
      die("Bad code");

A PHP script, buildmd5.php, is included in the archive to create the encrypted code and avoid to copy it. Modify this script to assign the secret code you choose, to the variable.
Run the script, it generates a file: code.php containing the variable $md5code assigned with the encrypted code.
Include this line in the command.php script that processes the SQL query.

Obviously, the script buildmd5.php is used locally and should not be put online with the form.

Displaying theresults

The result depends on the type to query.

If the application searches in the database, the function mysql_fetch_array returns the columns and values in a table. And we put it in a loop to display each row of the table:

while($arr = mysqli_fetch_array($results))   // each array corresponds to a table row
{
  foreach($arr as $k => $v)
  {
    if(intval($k) != 0 || $k == '0') continue;
    echo "$k : $v <br>\n";
  }
  echo "<br>";
}

The configuration file

Taking for example the database of Wordpress, the configuration file has the following lines with values depending on each installation:

define ( 'DB_NAME', 'mydb'); 
define ( 'DB_USER', 'user'); 
define ( 'DB_PASSWORD', '12345 '); 
define ( 'DB_HOST', 'localhost'); 
define ( 'DB_CHARSET', 'utf8'); 

From this configuration file we can open access to a host and select a database, which is required before any request:

include("wp-config.php");

$DB_ID = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD)
or die("MySQL database '".DB_NAME."' not accessible.<br>\n");

mysqli_select_db($DB_ID, DB_NAME)
or die("Enable to select '".DB_NAME."' database<br>\n");

This code is added to the command.php script.

From there you now have a straightforward interface to the database of your website or blog, once posted the interface.php page and command.php script extracted from the archive.

Example of query

Show last 5 posts added through Wordpress:

SELECT * FROM wp_posts WHERE (post_type = 'post') ORDER BY post_date DESC LIMIT 5

The posts table is specific to Wordpress. The prefix wp_ may be replaced by value assigned to the variable $table_prefix in wp-config.php.

This request displays the contents of recent articles with pictures and possibly videos.

Download the archive

• SQL interface.