SQL Command Interface

To pass SQL commands to the database, you need an interface that we just do with an HTML page containing the form.

A more direct solution would be to use PHPMyAdmin, which also allows requests to be submitted if installed. But this does not teach us anything about the use of SQL. Since this basic interface model can be gradually expanded to create a complete database management interface.

Interface form

The text field and the PHP script submit button form the main part 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="Envoyer" />
</form>

The PHP script retrieves the contents of the query variable:

if(!isset($_POST)) die("Acces direct interdit");

include("config.php");

$requete = $_POST['commande']; 

$results = mysqli_query($DB_ID, $requete)
or die("$commande<br>".mysql_error());

In five lines, the script...

1. Denies direct access.
2. Loads a configuration file containing names and passwords (detailed below).
3. Gets the command sent by the form.
4. Executes a query.
5. If it fails, an error is displayed.

Secret code

To prevent indelible visitors from accessing our database, it will be protected with a secret code. You can use the database password or create a special code.
This second option allows us to encode it over MD5, which is the best choice.

Create Code

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

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

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

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

The PHP script buildmd5.php is included in the archive to create encrypted code and avoid copying it. Edit this script to assign a secret code to the code variable.
Running the script, it generates a code.php file containing the $ md5code variable assigned with encrypted code.
Include this string in the command.php script that processes the SQL query .

The buildmd5.php script is obviously used locally and should not be connected to the form .

Show result

The type of result depends on the command.

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

while($arr = mysqli_fetch_array($results))   // chaque tableau correspond à une ligne de la table
{
  foreach($arr as $k => $v)
  {
    if(intval($k) != 0 || $k == '0') continue;
    echo "$k : $v <br>\n";
  }
  echo "<br>";
}

Configuration file

As an example of a WordPress database, the configuration file will have the following lines with values for each installation:

define('DB_NAME', 'mabase');
define('DB_USER', 'utilisateur');
define('DB_PASSWORD', '12345');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');

From this configuration, you can open access to the host and select a database, which is necessary before any request:

include("wp-config.php");

$DB_ID = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
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 placed in the command.php script.

From now on, you get a simple and direct interface to the database of your site or blog, after you post the API page and command.php script extracted from the archive on the Internet.

Sample Query

Show the last 5 tickets added to Worpdress:

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

The table of posts is specific to Wordpress. The prefix wp _ can be replaced by the value specified by the $ table _ prefix variable in wp-ef.php.

This query displays the contents of recent tickets with pictures and videos.

Download archive

• SQL interface.