Using MySQL Prepared Statements in PHP

28Jul/0914

Using MySQL Prepared Statements in PHP

Prepared statements in MySQL are an alternative to writing raw SQL code to execute. Instead, you write a statement with placeholders (?) where you want variable to go, then attach variables to those placeholders.

A prepared statement is basically a template that can be reused with different variables. There are some benefits and drawbacks to prepared statements that should be considered:

Pros:

Prevents SQL injection without needing to escape data
Allows you to repeat the same statement without the overhead of parsing the SQL
Allows you to send raw binary data in packets
Creates code that is easier to read by separating SQL logic from data

Cons:

Slower for one time queries since it requires two requests from the MySQL server
Does not work for ALL queries (only data manipulation queries)
Placeholders can only be used for values and not table/column names

Conclusion: I'd say prepared statements win due to security benefits alone

PHP supports MySQL prepared statements using the Mysqli (MySQL Improved) extension in PHP 5 via the MySQLi_STMT class. They are fairly easy to use once you get used to the differences from writing raw SQL statements. This tutorial will explain how to use prepared statements.

Inserting Data

First, we need a valid database connection...

<?php
// Create Mysqli object
$db = new mysqli('localhost', 'username', 'password', 'database');

Now we create a new statement object using the mysqli:: stmt_init() method.

// Create statement object
$stmt = $db->stmt_init();

We use the mysqli_stmt::prepare() method to prepare a statement. Put a ? where values should be placed.

// Create a prepared statement
if($stmt->prepare("INSERT INTO `table` (`name`, `age`, `bio`) VALUES (?, ?, ?)")) {

Now this is where we select the variables to put in place of the ?'s using the mysqli_stmt::bind_param() method. The first parameter is a string made up of i (integer), d (double), s (string), and b (binary data). Each character corresponds to the variable in the same position. Any parameters following that are the variables to use. In this case we use "sis" since $name is a string, $age is an integer, and $bio is a string.

    // Bind your variables to replace the ?s
    $stmt->bind_param('sis', $name, $age, $bio);
 
    // Set your variables
    $name = 'John Doe';
    $age = 32;
    $bio = 'Unknown...';

Finally, we execute the statement with the current values of the variables. We can run the mysqli_stmt::execute() method in a loop, changing the variables as needed, to run the same statement. When we are done with the statement, use the mysqli_stmt::close() method to discard it and free resources.

    // Execute query
    $stmt->execute();
 
    // Close statement object
    $stmt->close();
}

Fetching Data

Now we will use a prepared statement to fetch data from the database. Much of the process is the same, so I'll only explain the differences.

// Create statement object
$stmt = $db->stmt_init();
 
// Create a prepared statement
if($stmt->prepare("SELECT `name`, `bio` FROM `table` WHERE `age` = ?")) {
 
    // Bind your variable to replace the ?
    $stmt->bind_param('i', $age);
 
    // Set your variable	
    $age = 32;
 
    // Execute query
    $stmt->execute();

Once we prepare and execute the statement, we need to receive the data. Here, we use the mysqli_stmt::bind_result() method to specify variables to store each column's data.

    // Bind your result columns to variables
    $stmt->bind_result($name, $bio);

Now we fetch each row in a while loop using the mysqli_stmt::fetch() method, which populates the bound variables for one row of result data.

    // Fetch the result of the query
    while($stmt->fetch()) {
        echo $name . ' - ' . $bio; // John Doe - Unknown...
    }
 
    // Close statement object
   $stmt->close();
}
?>

Conclusion

This was just a basic overview of prepared statements in PHP. If you have experience with MySQL in PHP, this should give you enough to replace your regular queries with prepared statements.

Similar Posts:

About Steve

Steve is the owner of UltraMega Tech. He is a freelance Web designer and developer who specializes in PHP and AJAX development.

Tagged as: MySQL, PHP, prepared statements, security, SQL Leave a comment

Comments (14) Trackbacks (2) ( subscribe to comments on this post )

johnny
August 6th, 2009 - 23:43

Hello. Thank you for this great info! Keep up the good job!

( REPLY )
molamola
August 8th, 2009 - 02:51

Didn’t understood the last part :s could you explain better please?

( REPLY )
- UltraMega
  August 8th, 2009 - 10:34
  
  You mean fetching rows? It’s the same as inserting, except that you have to bind variables to store the results and then fetch each row.
  
  In the example the query gets the name and bio fields from the data, so we need to assign these to variables. Each time we run the fetch method, the values of the current row are assigned to the bound variables.
  
  It’s basically the same as the regular mysql_fetch_row, except we use individual variables instead of an array to store values. I hope that makes sense.
  
  ( REPLY )
Joe
October 24th, 2009 - 07:44

This is very easy to understand explanation how prepared statements work. Thanks

( REPLY )
Dinesh
January 22nd, 2010 - 07:27

Good work man. This really saved me.

( REPLY )
John
February 15th, 2010 - 18:51

Is there any way to do this with XML? I’m using regular queries right now (which is fine because there is no user-input), but the where column = ‘ . $var; doesn’t work because the variable needs to be in quotation marks while concatenated… you know what I mean? Prepared Statements are a great way to do this but I can’t figure out how to get an XML from it. If you could help that’d be awesome.

( REPLY )
- Steve
  February 16th, 2010 - 14:27
  
  I’m not sure what you mean by using XML. Prepared statements are specific to certain database engines. If your problems are simply with concatenation, you might want to look at using sprintf or simply using double quotes around the string.
  
  ( REPLY )
bryan
April 19th, 2010 - 09:15

I have a pages setup that seems to work fine, but I want to get some professional feedback as I am a bit of a newb at the prepared statement usage. I have a PHP page that uses several fields from a MySQL database table in various places. Can I place the prepared statement at the top of my page, then use variables within page, and then close at bottom of page? Is there a better way? Here is my example…

===================================
PHP Code:

prepare(“SELECT `pageID`, `pageTitle`, `pageContent`, `metaTitle`, `metaDescription`, `metaKeywords` FROM `page` WHERE `pageTitle`=?”);
$result->bind_param(“s”, $pageTitle);
$result->execute();
$result->bind_result($pageID, $pageTitle, $pageContent, $metaTitle, $metaDescription, $metaKeywords);
$result->fetch();
?>

<meta name="description" content="” />
<meta name="keywords" content="” />

blah, blah, blah

close();

// Close connection
$db->close();
?>
===================================

Any help is greatly appreciated.

Bryan

( REPLY )
- Steve
  April 19th, 2010 - 12:32
  
  Yeah, I see no issues with that. You can close your statement as soon as you’re done inserting/fetching records, so you can actually move the close statement to the top of the page after fetch. Your local variables will still be populated with the latest fetched record after you close.
  
  ( REPLY )
bryan
April 19th, 2010 - 09:17

Sorry here is code..

PHP Code:

prepare(“SELECT `pageID`, `pageTitle`, `pageContent`, `metaTitle`, `metaDescription`, `metaKeywords` FROM `page` WHERE `pageTitle`=?”);
$result->bind_param(“s”, $pageTitle);
$result->execute();
$result->bind_result($pageID, $pageTitle, $pageContent, $metaTitle, $metaDescription, $metaKeywords);
$result->fetch();
?>

<meta name="description" content="” />
<meta name="keywords" content="” />

blah, blah, blah

close();

// Close connection
$db->close();
?>

( REPLY )
Danny
August 18th, 2010 - 17:16

This is the only article on this subject that actually worked for me, in fact examples on the actual PHP web site didn’t work. Heh.

Thanks for the write up. Although there is a part that I’d like clarification on, “Slower for one time queries since it requires two requests from the MySQL server”. So far your site the only one to say this, other sites I went to say this actually will SPEED UP your MySQL queries.

When you say “one time queries”, do you mean something simple like a SELECT that gets say, a single username from the DB?

( REPLY )
- Danny
  August 18th, 2010 - 17:19
  
  Also one more thing, do you suppose that mysqli is part of all PHP5+ installations by default?
  
  ( REPLY )
  - Steve
    August 18th, 2010 - 19:22
    
    By default it is not enabled in PHP. PHP has to be compiled with it enabled. Most precompiled packages have support enabled, so it’s usually just a matter of installing the Mysqli module.
    
    More details: http://www.php.net/manual/mysqli.installation.php
    
    ( REPLY )
- Steve
  August 18th, 2010 - 19:14
  
  When you prepare a statement, a request is made from the MySQL server in addition to any real queries you make with the statement. If you have to make several queries using the same statement, then it will run faster than standard queries because there is no overhead of parsing the SQL each time.
  
  For queries you only use once, there is no speed benefit, and actually is less efficient. You would be making one request to parse your SQL and another to send your data, where you could do both in one request using a standard query.
  
  Basically, if you are only running execute() once on a statement, then that is a one time query.
  
  Of course you need to make sure to escape your values if you go with a standard query.
  
  ( REPLY )