Rob's Blog

Easy MySQL prepared statements with PHP5

June 7th, 2008

UPDATE 16th March 2010: See the new, dedicated EasyPDO Website

Synopsis

Presenting a simple extension for MySQLi that provides convenience functions similar to mysql->fetch_array() and mysql->fetch_assoc() for prepared MySQL statements.

The Problem

It’s the middle of 2008, and web servers everywhere are still being hacked with simple SQL injection attacks. If you’re a web developer, and you don’t know what an SQL injection attack is, you should not write a single line of code until you have read and fully understood this. Allowing an SQL injection attack to occur in your code is just plain lazy, and you can do better.

Since about 50% of all web servers are powered by Apache, it’s a fairly safe bet that the majority of the dynamic sites they host run PHP and MySQL. These free tools make it easy to produce interactive, database-driven websites even for non-programmers, but unfortunately make it too easy for anyone (developer or not) to generate code which opens up your site to SQL injection possibilities.

Before the advent of MySQLi and prepared statements, MySQL statements in PHP queries to a MySQL database would look like:

/*
 * Example 1
 */
$sql ="SELECT ID, Firstname FROM Users WHERE Username = 'myusername' AND Password = SHA1(CONCAT('mypasswordsalt', 'mypassword'))";

(By the way, you’re not storing passwords in plain text, are you? You are adding a salt and storing the hashed password? If not, this explains what you are risking.)

So in PHP, your code might look something like:

/*
 * Example 2
 */
  $username = $_POST["username"];
  $password = $_POST["password"];
  $sql = sprintf("SELECT ID, Firstname FROM Users WHERE Username = `%s` AND Password = SHA1(CONCAT(Salt, `%s`))", mysql_real_escape_string($username, $password);
  $query = mysql_query($sql);
  // etc

That’s all quite a bit of hassle to type out though. In a large application, it’s easy to imagine that a developer might forget to escape the user inputs, leaving their database vulnerable to attack.

Enter MySQLi: This PHP extension provides the solution to all our SQL injection woes: the prepared statement. This article is not about prepared statements however, so if you don’t know what they are, read this.

I think PHP developers are used to the convenience that functions such as mysql->fetch_array() and mysql->fetch_assoc() provide. MySQLi also provides similarly named functions, but they don’t work with prepared statements. The only way to use these functions is if you resort to the old-style query string generation, where you have to remember to escape any data provided from the user. To obtain results from a prepared statement, the process goes something like this:

/*
 * Example 3
*/
  $sql = "SELECT ID, Firstname FROM Users WHERE Username = ? AND Password = SHA1(CONCAT(Salt, ?))";
  $query = $db->prepare($sql);
  $query->bind_param("ss", $username, $password);
  $query->execute();
  $query->bind_result($userid, $firstname);
  $query->fetch();
  $query->close();

Quite a mouthful, huh? I have tired of writing out all this code, time and time again, just to provide secure, SQL injection-proof code. I initially resorted to defining a code template in Eclipse, but it was a poor solution. It’s frustrating that MySQLi doesn’t provide a nice easy implementation of fetch_array, fetch_assoc or similar for prepared statements.

Extending the MySQLi class

Below I will present my solution to the problem. I have extended the mysqli class to:

Execute prepared INSERT, UPDATE and DELETE statements from a single line of code
Fetch a single row from a result set as an associative array, using a prepared query from a single line of code
Fetch a result set as an array of objects, using a prepared query fom a single line of code
Automatically close queries upon completion to help save resources

The code from Example 3 can now be re-written as:

/*
 * Example 4
 */
  $sql = "SELECT id, Firstname FROM Users WHERE Username = ? AND Password = SHA1(CONCAT(Salt, ?))";
  list($id, $firstname) = $db->FetchRow($sql, "ss", $_POST["username"], $_POST["password"]);

The FetchRow function will return either an array containing the results in the order specified by your SQL statement, or a single value if you only selected a single field, for example:

/*
 * Example 5
 */
  $sql = "SELECT COUNT(*) AS Count FROM Users WHERE SignupDate > NOW() - INTERVAL 1 DAY";
  $NewMembers = $db->FetchRow($sql);

Note that I need to provide an alias for the expression COUNT(*).

I can also select multiple rows from the database, obtaining the results as an array of objects:

/*
 * Example 6
 * Find all users that signed up today
 */
  $date = mktime(0, 0, 0, idate("m"), idate("d"), idate("Y"));
  $sql = "SELECT id, Username, Firstname, UNIX_TIMESTAMP(SignupDate) AS SignupDate FROM tblUsers WHERE SignupDate > FROM_UNIXTIME(?)";
  $NewMembersList = $db->FetchAll($sql, "i", $date);
  foreach ($NewMemberList as $member)
    echo $member->Firstname;

The array $NewMemberList in example 6 above contains an object representing every row in the result set from the query. Each object contains properties with the same name as fields in the SQL select statement. This is why we need to provide an alias for expressions such as COUNT(*) or UNIX_TIMESTAMP(SignupDate) – these are not valid property names for a PHP object.

I can also execute prepared SQL statements which do not return a result set (eg INSERT, UPDATE, DELETE or any DDL type statements). For example, if I wanted to disable a particular users account:

/*
 * Example 8
 */
  $db->ExecuteSQL("UPDATE Users SET Enabled = 0 WHERE id = ?", "i", $userid);

If I execute an INSERT statement on a table with an auto-increment field, the ExecuteSQL method will return the auto-increment value, otherwise it will return null.

This is just a first pass attempt at making my code easier to read and maintain, and also significantly less verbose. I know there are a few areas where I could make improvements, but please feel free to post any suggestions or comments you might have.

~~Download the source code here~~.
Please use the EasyPDO library, available here.

This entry was posted on Saturday, June 7th, 2008 at 10:14 pm and is filed under Computers, Software Engineering, Web.

19 Responses to “Easy MySQL prepared statements with PHP5”

janq Says:
July 5th, 2008 at 5:55 am
Notice, that in your Example4 the command
$db->FetchRow($sql, “ss”, $_POST["username"], $_POST["password"]);
will produce an array like this
Array ( [0] => 4 [1] => John )
instead of
Array ( ["id"] => 4 ["Firstname"] => John )

How to fix that ?
Nicolas Says:
July 6th, 2008 at 11:55 pm
I am using this in one of my projects and up to now, it seems to work perfectly. Thanks very much. Why has no one done this before?
RRP Says:
July 7th, 2008 at 8:10 pm
@janq:

To return an associative array of results, simply change the last line of the FetchRow method from:

return (count($fields) === 1)?$bindparams[0]:$bindparams;

to:

return (count($fields) === 1)?$bindparams[0]:$vars;

This is a nicer way to return the result set – thanks for the suggestion.
neron-fx Says:
September 26th, 2008 at 11:44 pm
This is one of the best articles I have read for a long time! Finally someone got as bored as me with manually writing prepared statements. I have a class myself very similar to yours. However up until now it was unusable as I had real problems getting my class to use the bind_param mysqli function. Thi s was due to the fact that you cant pass the bloody thing an array for the parameters! So this one line of code has made me a very very happy man!

if (!call_user_func_array(array($query, ‘bind_param’), $params))
die(“Bind parameters failed”);
}

My class now works perfectly and integrates brilliantly with my custom made framework!

So THANK YOU!!!
MikeD Says:
November 18th, 2008 at 7:58 am
How do you insert a date that uses the now() function?

I tried
$sql = “INSERT INTO GROUPS (name, founded) VALUES (?, ?)”;

$groupID = $this->ExecuteSQL($sql, “sd”, $groupname, “now()”);

but it blows up. Help
RRP Says:
November 18th, 2008 at 10:07 am
Hi MikeD,

NOW() is a MySQL function, so doesn’t need to be added in using a parameter. The code below should work fine:

$groupID = $this->ExecuteSQL(“INSERT INTO GROUPS (name, founded) VALUES (?, NOW())”, “s”, $groupname);

Alternatively, you could use the PHP function time():

$currentTime = time();
$sql = “INSERT INTO GROUPS (name, founded) VALUES (?, FROM_UNIXTIME(?))”;
$groupID = $this->ExecuteSQL($sql, “si”, $groupname, $currentTime);

NB: If copying and pasting, beware of the funky double quote marks
MikeD Says:
November 19th, 2008 at 5:02 am
Rob, you’re the man.

My (hopefully) last question. I’m trying to use this with the limit functionality of mysql and it doesnt work for pagination.

$sql = “select name, founded from groups limit ?”;
$rs = $this->FetchAll($sql, “is”, $blogPostID, $limit);

this work if $limit is say 5, but not if it is “0, 5″.

I tried telling it to use s so it would treat it as a string but no go. Is there a way to make this work?
RRP Says:
November 19th, 2008 at 6:47 am
Hi MikeD,

This should work for you:

$sql = “select name, founded from groups limit ?, ?”;
$rs = $this->FetchAll($sql, “is”, $blogPostID, $offset, $limit);

What you’re trying to do only works in MySQL v5 (v4 doesn’t allow parameters in the LIMIT clause).
MikeD Says:
November 25th, 2008 at 4:25 pm
Rob, I’m back.

I’ve been running this on my local system and it works.

$sql = “select vdm_title FROM vd_movies WHERE vdm_type=1 order by vdm_rid LIMIT ?”;
$rs = $this->FetchAll($sql, “ii”, $vdm_type, $limit);

$vdm_type = 1 and $limit = 5

When I move it to my dotster host I get bind parameters failing in this method:

$query = $this->prepare($sql);
if (isset($paramtypes))
{
$params = array(0 => $paramtypes);
$paramcount = func_num_args();
for ($i = 2; $i < $paramcount; $i++)
$params[] = func_get_arg($i);

if (!call_user_func_array(array($query, ‘bind_param’), $params))
die(“Bind parameters failed “);
}

What causes the call_user_func_array method to fail?
MikeD Says:
November 25th, 2008 at 5:12 pm
On mysqli 5.0.51a i don’t have problems with your class, but on my dotster host mysqli 5.0.37, I get bind parameters failed on the same code.

I’m dying over here! Any advice?
RRP Says:
November 25th, 2008 at 9:42 pm
Hi MikeD,

My server runs MySQL 5.0.24a and I haven’t had any problems such as yours, therefore I think the problem isn’t related to the MySQL version.

Sorry if I’m stating the obvious, but try these steps:
1) Check that the structure of the “vd_movies” table is the same on both servers
2) Modify the code to work without the LIMIT clause as a parameter (temporarily hard code it as 5), and see if that works.

Let me know how you go.
Michael Says:
February 4th, 2009 at 4:54 pm
Hi,

I got bored and made some additions to your code.

http://www.princeton.edu/~mmwu/codesense_mysqli.txt

I added CodeSense_mysqli::FetchRowAssoc(), which fetches an associative array.

Also, I didn’t quite like the FetchAll way of doing things, and I wanted to use it in line with bound parameters, so I added code so you could do something like this:

$q = $db->PrepareRows(“SELECT id, title FROM events WHERE author != ?”, ‘s’, $author);

while( list($id, $title) = $q->fetch() )
{
print “$id $title”;
}

or

while( $row = $q->fetch_assoc() )
{
print “$row[id] $row[title]“;
}

or

if( $q->num_rows() > 0 )
print “More than one!”;

It will automatically close the query when it is done.

In order to allow other queries to run while the main one is still being fetched, I used store_result.

However, if we are concerned about the memory needed to store all of that, and we don’t need to run other queries inside the loop (because you can’t if you don’t use store_result() )
CodeSense_mysqli::PrepareRows_nostore

Thanks a lot for the code! I was getting very annoyed at typing all the extra lines over and over again.
RRP Says:
February 4th, 2009 at 7:11 pm
Thanks for your comments Michael. When I get time, and with your permission, I’ll go through your code and see it can be incorporated.

After using the database class extensively for many months now, I have moved away from “FetchAll”, and find the “FetchObj” method a much more natural fit (it also solves the problem of storing an entire result set in memory. See point 4 of this post: http://www.robpoyntz.com/blog/?p=191).

Instead of:

$users = $db->FetchAll(“SELECT UserID, Username FROM tblUsers WHERE LastLogin < FROM_UNIXTIME(?), "i", $date);
foreach ($users as $user)
echo "$user->UserID: $user->Username”;

I now use:

while ($user = $db->FetchObj(“SELECT UserID, Username FROM tblUsers WHERE LastLogin < FROM_UNIXTIME(?), "i", $date))
echo "$user->UserID: $user->Username”;

I have updated the class further over the last few weeks. The latest version now includes simplified transaction support and better use of exceptions. New version if you wish to download is here: http://www.robpoyntz.com/extras/codesense_mysqli/codesense_mysqli.php.zip
chris Says:
August 18th, 2009 at 3:06 pm
Hi Rob,

Thanks for your great class. I am using $db->FetchObj as you suggested.

I have a question about the speed of the prepared statements in your class.

When I use standard mysqli (as in the code below), I know after I put in my SQL statement I can run $query-> execute many times:
$query->bind_param(“ss”, $username, $password);
$query->execute();
$username = ‘bob’;
$query->execute();

And I know I will get very fast speed on the second execute.

But to use your class, I have to use:

while ($user = $db->FetchObj(”SELECT * FROM tblUsers WHERE username=?”, “s”, $username)
$username = ‘bob’;
while ($user = $db->FetchObj(”SELECT * FROM tblUsers WHERE username=?”, “s”, $username)

With this, I don’t think I will get a very fast second run, as it has to prepare the SQL statement again.
Is this correct??

If so, how can I get the fast speed again if I want to use your class?
RRP Says:
August 18th, 2009 at 3:21 pm
Hi Chris,

There are two mechanisms in place that mean the second call (or any subsequent calls) to FetchObj won’t require MySQL to recompile the query:

1) When the query is prepared, we save it for re-use later. Notice we only call “prepare” if the query is null, or if the sql statement we’re executing is different to the last sql statement executed.

2) MySQL itself caches the query plans for all SQL it compiles. This is where parameterised queries help with speed – the SQL itself hasn’t changed therefore MySQL doesn’t need to recompile it, instead it just re-uses the cached version, and uses the updated parameter values.

Hope this helps.
Len Ocin Says:
November 29th, 2009 at 12:16 am
I just started using your work (good stuff, really), but already running into issues:

This works fine : (but obviously not how one should use the function)
$obj = $db->FetchSingleObj(‘SELECT title, description, notes, post, download_link FROM posts WHERE id=’.$id);

Whereas this fail with a nasty long error:
$obj = $db->FetchSingleObj(‘SELECT title, description, notes, post, download_link FROM posts WHERE id=?’,'i’,$id);

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in D:\Repository\xxxx\trunk\Projects\xxx\src\lib\dbconnect.php on line 560

Fatal error: Uncaught exception ‘EDatabaseException’ with message ‘Bind parameters failed’ in D:\Repository\xxxx\trunk\Projects\xxxx\src\lib\dbconnect.php:561 Stack trace: #0 D:\Repository\xxxx\trunk\Projects\xxxx\src\lib\post.php(24): DF_MySQLi->FetchSingleObj(‘SELECT title, d…’, ‘i’, 1) #1 D:\Repository\xxxx\trunk\Projects\xxxx\sandbox\databasei.php(17): Post->retrievePost(1) #2 {main} thrown in D:\Repository\xxxx\trunk\Projects\xxxx\src\lib\dbconnect.php on line 561

Am I missing something? Also do you perhaps have some more examples on using this class? (I know I’m being bad, and first should understand it’s inner working, but in a bit of a rush with a non-critical project)
Tom Says:
May 13th, 2010 at 6:55 am
Would it be possible to get a version of the class with FetchAll included? PHP 5.3 will not work with the older version I am using and I can’t use PDO since it does not support SSL.
Tom Says:
May 19th, 2010 at 12:41 am
I was able to get the mysqli version working with php 5.3+ with the changes below.

New function
function makeValuesReferenced($arr){
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;

}

Replaced:
if (isset($paramtypes))
{
$params = array(0 => $paramtypes);
$paramcount = func_num_args();
for ($i = 2; $i $paramtypes);
$paramcount = func_num_args();
for ($i = 2; $i SQLFailure();
throw new EDatabaseException(“Bind parameters failed”);
}
}
Tom Says:
May 19th, 2010 at 12:44 am
With:

if (isset($paramtypes))
{
$params = array(0 => $paramtypes);
$paramcount = func_num_args();
for ($i = 2; $i SQLFailure();
throw new EDatabaseException(“Bind parameters failed”);
}
}

Pages

Archives

Categories

Meta

Easy MySQL prepared statements with PHP5

Synopsis

The Problem

Extending the MySQLi class

19 Responses to “Easy MySQL prepared statements with PHP5”