Mark’s Site

Prepared statements are the most secure way of passing data to SQL from your programming. (available with MySQL 5+, MSSQL and others)

For these examples, I willl be using PHP and the mysqli extension to use prepared statements. But first things first …

What are prepared statements?

Server side prepared statements are a way of setting up a SQL statement once, which you can then execute X times with different parameters. For PHP/.NET programmers, this may sound similar to just having a dynamic query setup in your code where you pass different parameters to it, but it is not. Prepared statements offer a separation layer between your SQL statement and your parameters that you do not get with standard dynamic queries. To better explain, let’s look at an example prepared statement:

SELECT *
FROM users
WHERE user_id = ?

The ? is what we call a placeholder. When you execute the above query, you need to provide a parameter to that placeholder. Think of it as a multi-stage process:

1. You send SQL a prepared statement with placeholders (perfectly formatted statement, with no data set for the placeholders yet).
2. You send SQL the associated parameters for the placeholder(s) (including their associated data types: int, varchar etc).
3. SQL then takes these parameters and integrates them into your prepared statement, automatically checking data types against the parameters passed (ie. if integer column, parameter must be an integer etc) and automatically taking into account any potential malicious characters (such as single/double quotes and so forth). SQL then executes the statement.

So Why Use Prepared Statements?

Security

Prepared statements offer the best protection against SQL injection attacks due to the separation layer between the SQL query and parameters.

Less Code

There is no need to escape the data you are passing to your SQL queries, leading to less complicated code, and, I have found, less development time worrying about protecting against SQL injection.

Potential Performance Increase

With a prepared statement, SQL will only need to parse your prepared statement once, checking for syntax, allowing you to run that same prepared statement multiple times, not having the overhead of parsing the same SQL query. This can lead to performance increases when you’re doing, say, many INSERT statements.

That said, there is also the potential for decreased performance, because with prepared statements there are two trips to the server: one for the prepared statement and one for the parameters. So you need to look at your application and decide on what is most important for you, security or performance. I’d always prefer security, and if performance turns out to be a problem, then just setup a good load-balanced server environment to provide better SQL performance for your application(s).

Some Example PHP/MySQL Code

I thought I’d give you some example code of prepared statements. This particular example is with PHP/MySQL, with PHP setup with the mysqli extension. All languages will have their own ways of using prepared statements, but the general idea remains the same.

This is an example of an UPDATE statement to a ‘users’ database table (see in code for comments).

public function updateSqlRecord() {
    try {
        $mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DATABASE); /* establish database connection */ 

        $sql = "UPDATE users ";
        $sql .= "SET ";
        $sql .= "id = ? ";
        $sql .= ",name = ? ";
        $sql .= "WHERE id = ? ";

        $stmt = $mysqli->prepare($sql); /* prepare statement */
        $stmt->bind_param('isi', $this->id, $this->name, $this->id); /* bind parameters to statement */
        $stmt->execute(); /* execute prepared statement */
        $stmt->close(); /* close statement and connection */
        $mysqli->close(); /* closes connection */ 

        return true; /* return success */
    } catch (Exception $e) {
        return false; /* return exception */
    }
} 

You would have noticed the bind_param function above that binds the parameters to the prepared statement has an initial parameter that you may not understand (’isi’). When binding the parameters to the prepared statement (in the above example: bind_param(…)), you need to specify the bind types for the parameters you are passing to the prepared statement. For example, is the parameter an integer (i), or a varchar (s), or other?

These are the bind type rules you need to know for mysqli:

i = All INT types
d = DOUBLE and FLOAT
b = BLOBs
s = All other types

That should be all you need to know for running prepared statements, but before I finish I would like to show you an example of a SELECT statement using this method (as it varies slightly, because you’re getting information rather than setting with the example UPDATE above)

    public function selectSqlRecord() {
        try {
            $mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DATABASE); /* establish database connection */ 

            $sql = "SELECT ";
            $sql .= "id ";
            $sql .= ",name ";
            $sql .= "FROM users ";
            $sql .= "WHERE id = ? ";

            $stmt = $mysqli->prepare($sql); /* prepare statement */
            $stmt->bind_param('i', $this->id); /* bind parameters to statement */
            $stmt->execute(); /* execute prepared statement */
            $stmt->store_result(); /* transfer result set from the prepared statement */
            $stmt->bind_result($this->id, $this->name); /* bind results to variables */
            $return_array = array();
            $row_array = array();
            while ($stmt->fetch()) {
                $return_array['id'] = $this->id;
                $return_array['name'] = $this->name;
            }
            $stmt->free_result(); /* free result set from buffer */
            $stmt->close(); /* close statement and connection */
            $mysqli->close(); /* closes connection */ 

            return $return_array;
        } catch (Exception $e) {
            return false; /* return exception */
        }
    } 

Enjoy your more secure programming in the future