UltraMega Tech.
28Jul/0929

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
  • Limited to SELECT, INSERT, REPLACE, UPDATE, DELETE, and CREATE TABLE 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.

Posted by Steve

Comments (29) Trackbacks (4)
  1. Hello. Thank you for this great info! Keep up the good job!

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

    • 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.

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

  4. Good work man. This really saved me.

  5. 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.

    • 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.

  6. 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

    • 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.

  7. 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();
    ?>

  8. 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?

    • Also one more thing, do you suppose that mysqli is part of all PHP5+ installations by default?

    • 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.

  9. The examples your provided were are OO based examples. My sites run procedural code instead of OO. How do prepared statements compare in a procedural environment?

  10. Great stuff! Thanks a lot.
    I’m just new with OOP. I think I’ll move all my regular SQL queries to prepared mysli.

  11. Thank you for posting this stuff. It really helped me alot to understand this :)

  12. “Does not work for ALL queries (only data manipulation queries)”

    SELECT is not a “data manipulation” query since the data is not changing. Maybe you could give some examples of what you mean by this statement since I’m sure you will confuse people with it.

    • OK, checking dev.mysql.com it says that prepared statements are limited to data manipulation (INSERT, REPLACE, UPDATE, and DELETE), CREATE TABLE, and SELECT queries. Please update the article to include CREATE TABLE and SELECT as well so that people are not mislead.

  13. Thanks so much, I’ve been all around google for days and I’ve finally found an easy straightforward tutorial on how to secure sql transactions, every other tutorial expects for you to know OO PHP. Im new to OO PHP, and I was wondering if I could still use procedural PHP to echo stuff and make if statements to further process the retrieved sql data that has been binded into variables from your example. if not, does this mean that I have to go OO PHP all the way?

    • All the functions used here have procedural equivalents, so there’s no need to use OO code at all. Just look at the manual page for each function for the procedural alias an example using it.

  14. We have a custom built ecommerce website running McAfee Secure, and it detected SQL Injection vulnerability. I’m replacing mysql statements with prepared statements with help of this tutorial. Thanks.

  15. Hi
    Great article and very useful but i’m struggling with an update query.
    A form on the web page allows user to input gifts into a textarea.
    This is then used by getting
    $gifts = $_POST['gifts'];
    and then the db is UPDATED with the new values of $gifts .
    This is then retrieved and displayed using nl2br to format it.

    How do I use a statement in this instance, the problem i seem to have is using the posted value inside the statement.
    (also, I use a seperate file with the db connection in and include it when needed so there are no db connection details in the pages code)

    thanks
    Bob

  16. Thanks for this! It was really concise and gave me exactly what I needed.

  17. Awesome article!

    I do have one question though if I may. I’m in the processing of porting over all my code into MySQLi prepared statements mainly for it’s security abilities but I’ve ran into a snag when trying to display multiple results on one page.

    ============= CODE =============
    <?php

    //Display numbers 1 – 20;

    echo "”;
    if($stmt->num_rows == NULL){
    echo “No results found.”;
    }else{
    while($stmt->fetch()){

    echo “”.$numbers.”";
    }

    }
    echo “”;

    ?>
    ======== END CODE ============

    Now, the above code works to display dynamic data stored in the db, but if I were to place another block of code, either the exact same fetch request or one requesting data from another table, both joined and not joined, under the first block of code, the results do not get displayed.

    Why is this? I can do it using regular MySQL methods but not when using prepared statements.

    Also, I’ve tried both keeping the stmt closed and open for testing purposes in each request block but still no luck.

  18. Trying to use “LIKE” with prepared statements but it only throws me an error whatever I try.
    What’s the exact syntax for use with wildcards and the LIKE statement?

  19. thax for u.


Leave a comment