Here it is. Enjoy.

Subscribe to the Notebook RSS Feed

Article Options

Archives by Category

Archives by Year

2011 (1)
2009 (5)
2008 (4)

Prepared Statements in PHP and MySQLi

Apr 9

Published in: Web Development
with 36 comments

This article is intended for readers who have experience using PHP and MySQL. You should also have a general understanding of databases and programming (both procedural and object-oriented) as well as how to use PHP to execute a simple query to MySQL. I will not cover how to install PHP or MySQL, however at the end of the article are some links to help you get started with the installation process and for some further reading on the subject. I will be covering the basics of prepared statements in PHP and MySQLi and why you should consider using them in your own code as well as some technical explanation as to why you should use them.

Introduction

If you are like me and most other people, you probably have not taken the time to learn about web security when you first started writing server-side code. This is very dangerous as most people never even go back and try to make their code secure (or they simply forget). Writing their code in the same way that they originally learned how to can cause some serious vulnerabilities in the code, allowing hacking techniques such as SQL injections to be fairly easy. If you have no idea what MySQL injections or cross side scripting is, then you should do some research, for example just go to Google and type in “SQL Injections” and there will be plenty of reading for you. I also would recommend a book called, “How to Break Web Software“, it is a fantastic book that one of my professors told one of my classes about. It can teach you a lot about security, it is highly recommended. I will have an article written shortly on SQL Injections, so check back soon! If you do know what some of these nasty hacking techniques are then you are probably wondering why you should want to use prepared statements. There are basically three reasons why you should seriously consider writing prepared statements to execute your queries.

Prepared statements are more secure.
Prepared statements have better performance.
Prepared statements are more convenient to write.

Now that we know why prepared statements are better, let’s build an example so you can see for yourself. We’ll build a simple login example using prepared statements. First, I’ll show you the way most people would write it, then I’ll show you the way you could do it with a prepared statement which will be more secure, have better performance and be more convenient to write. Let’s get started!

The Well-Known Way

If you are reading this article, chances are you already know how to execute a simple MySQL query in PHP. For those of you who do not know how to do this, it would look similar to this:


   /* Connect to the Database */
   $dbLink = mysql_connect("localhost", "username", "password");

   if (!dbLink) {
      echo 'db link fail';
   }

   /* Select the database */
   mysql_select_db("databaseName");

   /* Query and get the results */
   $query = "SELECT * FROM testUsers WHERE username='$user' AND
             password='$pass'";
   $result = mysql_query($query);

   /* Loop through the results */
   while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
      echo "Username: " . $row["username";
   }

What is the problem with this code? Simple, someone could use a simple SQL injection to get around the password authentication. Why is this code angerous? If you know what an SQL injection does, it basically bypasses the password condition by commenting it out and uses an always true statement which allows access. Building strings on the fly like this should make you very nervous, but how do we make it more secure? Say hello to prepared statements.

Prepared Statements

What is so great about prepared statements and why are they more secure? The simple answer is because prepared statements can help increase security by separating the SQL logic from the data being supplied. In the previous example we saw how the data is basically built into the SQL logic by building the query as a string on the fly. Let’s take a look at what a prepared statement can look like.


   /* Create a new mysqli object with database connection parameters */
   $mysqli = new mysql('localhost', 'username', 'password', 'db');

   if(mysqli_connect_errno()) {
      echo "Connection Failed: " . mysqli_connect_errno();
      exit();
   }

   /* Create a prepared statement */
   if($stmt = $mysqli -> prepare("SELECT priv FROM testUsers WHERE username=?
   AND password=?")) {

      /* Bind parameters
         s - string, b - boolean, i - int, etc */
      $stmt -> bind_param("ss", $user, $pass);

      /* Execute it */
      $stmt -> execute();

      /* Bind results */
      $stmt -> bind_result($result);

      /* Fetch the value */
      $stmt -> fetch();

      echo $user . "'s level of priviledges is " . $result;

      /* Close statement */
      $stmt -> close();
   }

   /* Close connection */
   $mysqli -> close();

Doesn’t look too bad, right? In short, the above code basically creates a new mysqli object and connects to the database. We then create a prepared statement and bind the incoming parameters to that statement, execute it and get the result. We then close the statement and connect and we’re done! Pretty easy! Let’s take a look at where the security happens in these few lines:


   if($stmt = $mysqli -> prepare("SELECT priv FROM testUsers WHERE username=?
   AND password=?")) {
      $stmt -> bind_param("ss", $user, $pass);

Instead of grabbing and building the query string using things like $_GET['username'], we have ?'s instead. These ?'s separate the SQL logic from the data. The ?'s are place holders until the next line where we bind our parameters to be the username and password. The rest of the code is pretty much just calling methods which you can read about by following some of the links at the end of the article.

Summary

In this article we have covered why and how you should use prepared statements. You should now have a solid understanding of the benefits associated with using prepared statements as well as how to use prepared statements. If you did not know before, after reading this article you should have a basic understanding of the object-oriented interface to MySQLi. I hope this was helpful to you and if you have any questions feel free to post some comments below!

36 people left comments

paleopterix on May 10, 2012 at 4:34 am wrote:

I’ve been using the “old” style mysql queries for a long time, but I begin to see that using mysqli can be a bit more convenient. But there is one thing I’m not very sure about:
You say that prepared statements prevent sql injection by separating the logic from the data. Well, after reading your article (and others found in internet), I am still not convinced there is a real separation. To be more exact: it is obvious that there is such a separation when you WRITE the php code. But what happens when your code gets executed? Couldn’t it be that, in the end, an sql query will be formed replacing those “?” with the actual parameter values, and this query will be sent to the database server? And if so, isn’t it the exact same thing as creating the sql statement yourself (as in your first example)? If php transparently replaces “?” with the parameter values, before executing the query, how does it prevent sql injection anyway?
Jeff Z. on February 24, 2012 at 9:53 am wrote:

Password example given by you is insecure, because you forgot about mysql_real_escape_string(), which kills all injection attempts.
Lets be honest here.
Not that I argue against prepared statements, as they are much better tool for the job.
raj prashanth on October 14, 2011 at 4:03 pm wrote:

really very good work man. It makes it very easy to understand
pankaj on September 15, 2011 at 4:57 am wrote:

this is mindblowing site
Andy Powell on September 14, 2011 at 10:32 am wrote:

Of course for the ‘well known’ way you could just

$user=md5($username);
$pass=md5($password);

$query = “SELECT * FROM testUsers WHERE MD5($username)=’$user’ AND
md5(password)=’$pass’”;

If you deliberately set out to write poor code you’ll get it.
Andy on January 20, 2011 at 8:17 am wrote:

Don’t worry about making prepared statements yourself. Use this PHP class and it does all of the hard work for you:

http://www.aplweb.co.uk/blog/php/mysqli-wrapper-class/
Dave on November 2, 2010 at 10:12 pm wrote:

For anyone having issues, this is a great resource to parallel with this article.

Great job btw!

http://php.net/manual/en/mysqli-stmt.bind-param.php
Tony Arnold on August 13, 2010 at 4:56 pm wrote:

[code]$mysqli = new mysql('localhost', 'username', 'password', 'db');[/code]

Forgive me, but you have left off the i on “new mysqli” object declaration .
foo on May 31, 2010 at 3:46 pm wrote:

$mysqli = new mysql(‘localhost’, ‘username’, ‘password’, ‘db’);
gives Class ‘mysql’ not found in …
should be
$mysqli = new mysqli(…
Paul Dunderdale on March 28, 2010 at 9:56 am wrote:

It’s bind_result not bind_results
Solow on March 24, 2010 at 9:40 pm wrote:

Nice resource. thanks for sharing this :)
matt on February 12, 2010 at 3:58 pm wrote:

What if there’s more than one row?
nadine on January 11, 2010 at 11:45 am wrote:

I like the old vs new examples. Very helpful. Do you have to create a new object/open and close the connection for multiple prepared statements?
svnlabs on October 29, 2009 at 10:15 am wrote:

This is very good resource….

Thanks
andrew on October 15, 2009 at 7:22 am wrote:

Hi!

It would be better if you showed an example where parameters change – in a for loop and similar. That would give a better understanding of the functions. Also, this is where prepared statements are better performing (by a wider margin).

Otherwise, nice post.

Enjoy!
directory on July 2, 2009 at 2:16 pm wrote:

Just dropping in to let you know you have an interesting site. I hope you’ll continue to work on it. Wishing you all the best.
Zac on June 29, 2009 at 5:11 pm wrote:

You know, I’ve changed my mind. I like Prepared Statements much better now, especially in OOP. I’m soon to abandon the old mysql() function in PHP.
Aaron on June 17, 2009 at 8:34 pm wrote:

Hey Matt, I was looking for some reading on this topic so I could understand it more, and this was just the thing. Thanks for sharing this article, and nice site!
Michael on May 23, 2009 at 9:28 pm wrote:

I just surfed in and found your site, I really enjoyed it and will come back soon. Nice site!
play free internet poker on April 22, 2009 at 5:13 am wrote:

I’m writing a db abstraction layer driver for MySQLi. I’m glad to finally get a chance to play around with these new functions, but am completely stumped by this question: Is there no way to get back a standard results ot when using prepared statement queries? I don’t see the solution, but I hope I’m just missing something because I’ve been staring at it too long.
roulette tricks on March 28, 2009 at 2:06 am wrote:

I am a newbie to both PHP and MySQL. This tutorial is exceptionally well written. It is so easy to read and understand. I also find it very informative. I spent hours going through books and web sites trying to understand the topic. The tutorial has given me the very knowledge I need.
TAN on March 19, 2009 at 5:00 am wrote:

Some good info here, although doesn’t this:

$mysqli = new mysql(‘localhost’, ‘username’, ‘password’, ‘db’);

want to be:

$mysqli = new mysqli(‘localhost’, ‘username’, ‘password’, ‘db’);

I intend to change my code to use this mysqli lark.

check it out: this aint news – TAN news links comments
Zac on February 1, 2009 at 2:18 am wrote:

Otherwise, sweet article.
Zac on February 1, 2009 at 2:10 am wrote:

Not to drag up an old topic, but I’d have to agree with Ryan S. on this subject. Why not use mysql_real_escape_string() to protect your inputs, especially on small systems?
Jeff on December 19, 2008 at 8:55 am wrote:

This is a great article, thanks for publishing this!
Steven on October 23, 2008 at 10:06 pm wrote:

Just wanted to point out an error…
/* Bind parameters
s – string, b – boolean, i – int, etc */
$stmt -> bind_param(“ss”, $user, $pass);

The ‘b’ is for ‘blob’, not boolean.
See this article for more http://devzone.zend.com/node/view/id/686#Heading10
Chuck on May 28, 2008 at 11:16 am wrote:

I’m a total rookie so took me a few minutes to figure this out:
$stmt -> bind_results($result);

above should be:
$stmt -> bind_result($result);

“bind_result”, not “bind_results”

:)
Matt Bango on April 29, 2008 at 4:36 pm wrote:

@Ryan – Thank you for the great, in-depth post. There is a lot of great information in there that I didn’t know/think about and will be very valuable to the readers. Thanks for sharing your knowledge on the subject!

@Rob – Thanks for posting. If anyone is interested, there is a great SQL injection cheat sheet here: http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/
Ryan S. on April 28, 2008 at 10:49 am wrote:

One thing to take note of: prepared statements are _not_ guaranteed better performance. The reason for this is that, in a prepared statement, there are actually at least two calls performed to the server. The first is the prepare(), when the statement is compiled; the second is the actual execution after all parameters are bound.

In many cases, a single SELECT (especially in an unbuffered result set) can run much slower than if you queried directly. The alternative, in this case, would be to use mysqli::real_escape_string() to properly escape your external GPC variables without the extra call to the MySQL server. The speed benefits of prepared statements really only kick in when you’re executing the same query within a script multiple times (think: saving the line items of a shopping cart to an order invoice, where you’ll reuse the same INSERT, rebound for each line item).

The advantage of the real_escape_string() approach is that the mysqli driver can properly escape whichever character set you’re working in, without adding extra server overhead. For most sites out there, this is the recommended solution. The only exceptions would be those who regularly switch the default MySQL character set through SQL statements, those who use custom session handlers to save to and load from MySQL, and those who employ the same CRUD query multiple times in the same script.
Rob S. on April 27, 2008 at 12:03 pm wrote:

You forgot ‘ OR 1 = 1 –. Great post. Dan would be proud.
Matt Bango on April 14, 2008 at 2:28 pm wrote:

I’m assuming you’re talking about the SQL Injections? If you look back at the code in the “Well-Known Way” and focus your attention to the query line, you will see that this query is being built on the fly as a string. Now pay attention to this part:

WHERE username=’$user’

A visitor/hacker could easily do something like this in the username input:

‘ OR true –

The first ‘ closes the string, then the OR true part is always true and finally the — comments the rest of the query out, ultimately resulting in running an always true query which will all the visitor/hacker to gain access to your site. So when I say comment out the server side code, I’m referring to commenting out the rest of the query by using — or #. I hope this helps, if you have any other questions feel free to post!
k on April 13, 2008 at 12:12 am wrote:

I’m not an expert on the subject, but how is it possible to comment a server side code? from a visitor/hacker perspective.
html form connect to mysql database on March 1, 2012 at 10:27 am wrote:

[...] Yes, but you are using regular statements without sanitizing your inputs, rather than using Prepared Statements like I suggested. Your method above will work, once the syntax errors are corrected, but it will leave you wide open to SQL Injection attacks. For a tutorial on Prepared Statements, read this article: Prepared Statements in PHP and MySQLi – Notebook | MattBango.com [...]
PHP5 and MySQLi « Abner’s Postgraduate Days on September 6, 2011 at 7:36 pm wrote:

[...] Prepared Statements in PHP and MySQLi – Notebook | [...]
Anonymous on April 1, 2011 at 3:32 pm wrote:

[...] [...]
Ellis Giles » Blog Archive » PHP Class for MySQL and Prepared Statements Reference on August 27, 2010 at 5:03 pm wrote:

[...] http://mattbango.com/notebook/web-development/prepared-statements-in-php-and-mysqli/ [...]

Here it is. Enjoy.

Article Options

Archives by Category

Archives by Year

Prepared Statements in PHP and MySQLi

Introduction

The Well-Known Way

Prepared Statements

Summary

Further Reading

36 people left comments

paleopterix on May 10, 2012 at 4:34 am wrote:

Jeff Z. on February 24, 2012 at 9:53 am wrote:

raj prashanth on October 14, 2011 at 4:03 pm wrote:

pankaj on September 15, 2011 at 4:57 am wrote:

Andy Powell on September 14, 2011 at 10:32 am wrote:

Andy on January 20, 2011 at 8:17 am wrote:

Dave on November 2, 2010 at 10:12 pm wrote:

Tony Arnold on August 13, 2010 at 4:56 pm wrote:

foo on May 31, 2010 at 3:46 pm wrote:

Paul Dunderdale on March 28, 2010 at 9:56 am wrote:

Solow on March 24, 2010 at 9:40 pm wrote:

matt on February 12, 2010 at 3:58 pm wrote:

nadine on January 11, 2010 at 11:45 am wrote:

svnlabs on October 29, 2009 at 10:15 am wrote:

andrew on October 15, 2009 at 7:22 am wrote:

directory on July 2, 2009 at 2:16 pm wrote:

Zac on June 29, 2009 at 5:11 pm wrote:

Aaron on June 17, 2009 at 8:34 pm wrote:

Michael on May 23, 2009 at 9:28 pm wrote:

play free internet poker on April 22, 2009 at 5:13 am wrote:

roulette tricks on March 28, 2009 at 2:06 am wrote:

TAN on March 19, 2009 at 5:00 am wrote:

Zac on February 1, 2009 at 2:18 am wrote:

Zac on February 1, 2009 at 2:10 am wrote:

Jeff on December 19, 2008 at 8:55 am wrote:

Steven on October 23, 2008 at 10:06 pm wrote:

Chuck on May 28, 2008 at 11:16 am wrote:

Matt Bango on April 29, 2008 at 4:36 pm wrote:

Ryan S. on April 28, 2008 at 10:49 am wrote:

Rob S. on April 27, 2008 at 12:03 pm wrote:

Matt Bango on April 14, 2008 at 2:28 pm wrote:

k on April 13, 2008 at 12:12 am wrote:

html form connect to mysql database on March 1, 2012 at 10:27 am wrote:

PHP5 and MySQLi « Abner’s Postgraduate Days on September 6, 2011 at 7:36 pm wrote:

Anonymous on April 1, 2011 at 3:32 pm wrote:

Ellis Giles » Blog Archive » PHP Class for MySQL and Prepared Statements Reference on August 27, 2010 at 5:03 pm wrote:

Share your thoughts