How to use Prepared Statements in MySQL using php

[Shane]

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

Code: Select all

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

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Courtesy of http://www.ultramegatech.com/blog/2009/ ... ts-in-php/