PHP Prepared Statements

A prepared statement (also known as parameterized statement) is simply a SQL query template containing placeholder instead of the actual parameter values. These placeholders will be replaced by the actual values at the time of execution of the statement.

  • Prepare — At the prepare stage a SQL statement template is created and sent to the database server. The server parses the statement template, performs a syntax check and query optimization, and stores it for later use.
  • Execute — During execute the parameter values are sent to the server. The server creates a statement from the statement template and these values to execute it. Prepared statements is very useful, particularly in situations when you execute a particular statement multiple times with different values, for example, a series of INSERT statements. The following section describes some of the major benefits of using it.

    • Advantages of Using Prepared Statements

    A prepared statement can execute the same statement repeatedly with high efficiency, because the statement is parsed only once again, while it can be executed multiple times. It also minimize bandwidth usage, since upon every execution only the placeholder values need to be transmitted to the database server instead of the complete SQL statement.

    Prepared statements also provide strong protection against SQL injection, because parameter values are not embedded directly inside the SQL query string. The parameter values are sent to the database server separately from the query using a different protocol and thus cannot interfere with it. The server uses these values directly at the point of execution, after the statement template is parsed. That's why the prepared statements are less error-prone, and thus considered as one of the most critical element in database security.

    Example -

    Explanation of Code (Procedural style)

    Inside the SQL INSERT statement (line no-12) of the example above, the question marks is used as the placeholders for the first_name, last_name, email fields values.

    The mysqli_stmt_bind_param() function (line no-16) bind variables to the placeholders (?) in the SQL statement template. The placeholders (?) will be replaced by the actual values held in the variables at the time of execution. The type definition string provided as second argument i.e. the "sss" string specifies that the data type of each bind variable is string.

    The type definition string specify the data types of the corresponding bind variables and contains one or more of the following four characters:

  • b — binary (such as image, PDF file, etc.)
  • d — double (floating point number)
  • i — integer (whole number)
  • s — string (text)

    • Using Inputs Received through a Web Form

    If you remember from the previous chapter, we've created an HTML form to insert data into database. Here, we're going to extend that example by implementing the prepared statement. You can use the same HTML form to test the following insert script example, but just make sure you're using the correct file name in the action attribute of the from.

    Here's the updated PHP code for inserting the data. If you see the example carefully you'll find we didn't use the mysqli_real_escape_string() to escape the user inputs, like we've done in the previous chapter example. Since in prepared statements user inputs are never substituted into the query string directly, so they do not need to be escaped correctly.

    Example -