mysql and mysqli

It isn't just for connecting to databases where the mysqli_ interface is an improvement over the mysql_ one. Perhaps one of the biggest advantages it has is with its ability to keep the data to be processed separate from the query itself and this eliminate the possibility of sql injection via the data. Yes I did say eliminate.

Consider this simple piece of code using mysql_ calls to read a couple of fields from a database table.

$query = sprintf(
"SELECT address, age FROM friends WHERE firstname='%s' AND lastname='%s'",
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
die($message);
}
while ($row = mysql_fetch_assoc($result)) {
$address = $row['address'];
$age = $row['age'];
}

The main thing to note in this code is the use of mysql_real_escape_string() on the data values being passed into the query. The purpose of this is to esacape any characters within the data that may potentially be misinterpreted as being a part of the query itself. Forget to escape one field and depending on how thoroughly you validate the field beforehand and what the field is allowed to contain you can end up either with broken code or with someone being able to inject their own SQL into your query to get it to do something totally different.

Now let's look at the mysqli_ calls to do exactly the same lookup.

if ($stmt = $mysqli->prepare(
"SELECT address, age FROM friends WHERE firstname=? AND lastname=?")) {
$stmt->bind_param("ss", $firstname, $lastname);
$stmt->execute();
$stmt->bind_result($address, $age);
$stmt->fetch();
$stmt->close();
} else {
$message = 'Invalid query: ' . $mysqli->error() . "\n";
die($message);
}

The first thing you'll notice is that this code is significantly shorter than the prior version. The second thing you'll notice is that we are not using mysqli_real_escape_string() since even though that call does exist it isn't necessary when we write our code as we have because the SQL is processed by the prepare() statement (using ? to represent each data field) and the data itself is processed by the bind_param() statement. There is therefore no need to escape the data in order to avoid confusing it with the SQL since unlike with the prior code where they both appear in the one statement, here we have them completely separate and so SQL injection through the data is completely impossible.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate