Thursday, March 19, 2015

Secure Access to a MySQL Database with PHP

In a previous post I explained how one can access a MySQL database with PHP. There I chose a simple, deprecated variant for demonstration purposes, namely the class mysql. Nowadays the class mysqli should be used though, the i stands for improved.
I want to present especially one new, important function, namely the possibility to use so called prepared statements.
One reason for this is that the method of communication with the database from the previous posts allows a serious attack: An SQL-injection is possible. This means the injection and execution of code on the server. Why? Well, let's take a look at the communication with the SQL server: We used the POST parameters in the script, for example to get the entered username and write it to the database. An attacker now could insert a semicolon, quotation marks etc. in the username, this way finish the statement and continue with a new query, which would then also be executed, because the database simply gets to see 2 queries!
To prevent, or at least try to prevent, this (100% security never can be guaranteed), one could for example try to escape special characters like ; in the SQL query by hand. I wil here choose a higher abstraction level and use an existing method of the class mysqli, which does this for us. The keyword for this is prepared statements, which prepare a query and then bind the "clean" parameters to it. Prepared statements offer a very good protection against SQL-injections.
The basic principle of a prepared statement is the following:
First we create a new connection variable:

$conn = new mysqli(server, username, password, database);

Then we use this for the query:

$stmt = $conn->prepare("query ... ?");
$stmt->bind_param("s...", params ...);
$stmt->execute();

In the 1. line we prepare the query, in it we replace the parameters by ?. In the 2. line we bind the parameters to these place holders, the first string expresses the typ. s stands for string, i for integer, d for double. The next arguments are the parameters. Finally we execute the query.

To state a query and read its output, we also execute the 3 lines from above. The answer we get with the following structure:

$stmt->bind_result(params);
while($row = $stmt->fetch())
   {
echo params .... ;
$stmt->bind_result($sender, $message);
   }

I now want to rewrite the script from the previous post about MySQL with mysqli, I think the principle then should be clear:

<?php
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];

$conn = new mysqli("db4free.net", "csharptricks", "12345678", "csharptricks");

$stmt = $conn->prepare("INSERT INTO Customers (FirstName, LastName) VALUES (?, ?);");
$stmt->bind_param("ss", $FirstName, $LastName);
$stmt->execute();
  
$stmt = $conn->prepare("SELECT FirstName, LastName FROM Customers;");
$stmt->execute();
$stmt->bind_result($output1, $output2);
while($row = $stmt->fetch())
   {
          echo "$output1<br />";
          echo "$output2<br />";
          $stmt->bind_result($output1, $output2);
   }
?>

No comments:

Post a Comment