Innovative Data Solutions

Why Prepared Statements in MySQL are a Good Thing

This was originally intended to be a blog on useful tips to get around the limitations of PHP’s bind variable methods when using prepared statements, however the ‘short introduction’ got a little out of hand, so instead you’ve got a post about using prepared SQL statements with PHP (which is probably a more interesting choice of topic for most people anyway).

Prepared statements come in handy when you have a query that will be executed multiple times with slight variations, such as:

SELECT title, author FROM books WHERE book_id = 1;
SELECT title, author FROM books WHERE book_id = 2;
SELECT title, author FROM books WHERE book_id = 3;

You might have this functionality in a script which looks something like:

function get_book ($id)
{
   $sql = "SELECT title, author FROM books WHERE book_id = " . $id . ";";
   $query = $this->mysqli->query($sql);
   $row = $query->fetch_row();
   printf("Title: %s, Author: %s\n", $row[0], $row[1]);
}
 
$booklist = array(1,2,3);
foreach ($booklist as $book)
{
   get_book($book)
}

There are two problems with the above approach:

  1. The first is that each time the get_book() function is called, you are incurring the costs of compiling the PHP, sending it to the server, executing the database query, and returning the results back to the client. This is an expensive process – especially if you have hundreds of clients sending requests at once, and each request requires multiple table joins or any other computationally intensive process.
  2. You are leaving your database susceptible to SQL injection attacks.

Using prepared statements solves both these problems. The prepared statement is like a template query which can be combined with parameters to form a complete query. First, the prepared statement is sent to the server where it is precompiled and saved. It can then be executed multiple times with different parameters. This means that the database engine only parses the query and generates its execution path once, rather each and every time you make a request. Furthermore, mySQL ensures that the parameters supplied are properly escaped, protecting you from people sneaking malicious SQL snippets into your functions.

The following code shows how the above example can be implemented using a prepared statement:

// Prepare the statement. The '?' denotes a location where a parameter will be substituted in.
// Note the absence of a semi-colon at the end of the SQL string.
$stmt = $mysqli->prepare("SELECT title, author FROM books WHERE book_id = ?");
 
// Bind the parameters. The 'i' flag indicated that an integer value is expected.
$stmt->bind_param('i', $id);
 
foreach($book_list as $book_id)
{
   // Set the value of the bind parameters. You can do this before or after calling bind_param(), as long as it is set before calling execute().
   $id = $book_id;
 
   // Execute the query at this stage the results will still be on the SQL server.
   $stmt->execute();
 
   // If your query returns a result set and you want to save this to your PHP variables, call bind_result() with a variable for each column in your result set.
   $stmt->bind_result($title, $author);
 
   // Now fetch the results on the SQL server into your variables.
   $stmt->fetch();
 
   printf("Title: %s, Author: %s\n",$title, $author);
}
 
// And finally deallocate the prepared statement.
$stmt->close();

You can immediately see that several more lines of code are required: you need to call bind_param() to bind your PHP variables to the query, make an explicit call to execute(), and if you are planning on using your results in PHP variables, you will need to use bind_results() before calling fetch() to retrieve the data. However, for a little bit of extra work, you have simultaneously improved the performance of your system and helped secure it against malicious attacks. Go you.

Note 1: the use of parameters in prepared statements is limited: only data values are permitted; column and table names are not.

Note 2: the performance improvements observed are greatest when the following two criteria are met: 1) the query is executed many times, and 2) the query is particularly complex, therefore saving more time per query execution. Therefore if you have a simple query only executed a handful of times, any performance benefit gained may well be negated by the extra work required in the handling of the prepared statement.

3 Responses to Why Prepared Statements in MySQL are a Good Thing
  1. HOnza
    September 1, 2011 | 7:12 am

    Great suggestion. Sure will help with optimizing many PHP/MySQL solutions. Now, do you know about anything similar in PHP/FileMaker environment?

    BTW, all instances of > are being displayed as raw HTML in your code snippet, which may confuse readers…

  2. Tabitha Goodall
    September 2, 2011 | 4:55 pm

    @HOnza
    Ah, thought I’d sorted that. Fixed now, thanks!

    I’m afraid I can’t help you with FileMaker – I’m helping out with the web dev side of things and have only used PHP and MySQL. One of the FileMaker guys might know something though.

    Glad you liked the post. :)

  3. Damian Kelly
    September 19, 2011 | 10:57 am
    Damian Kelly

    HOnza,

    Using prepared statements is pretty easily in FileMaker too. The SQL for the prepared statement is formed the same way as Tabitha’s example and executed directly through the perform SQL script step. Unfortunately though there is no way to leverage this when using ESS.

    For ESS speed improvements consider using views and triggers. I detailed this in another blog post here:http://www.linearchat.co.uk/2011/02/mysql-optimisation-strategies-to-improve-ess-performance/

Leave a Reply

Wanting to leave an <em>phasis on your comment?

Trackback URL http://www.linearchat.co.uk/2011/08/why-prepared-statements-in-mysql-are-a-good-thing/trackback/
Email
Print