Using MySQL Prepared Statements in PHP

By:    Updated: February 28,2017

Prepared Statements can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. This prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

How Prepared Statements work

The prepared statement execution consists of two stages: prepare and execute.

  • The prepare stage: A statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.
  • The bind and execute stage: Prepare is followed by execute. During execute the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources.

 

The MySQL Client Server Protocol defines a different data transfer protocol for prepared statements and non-prepared statements.

  • Prepared statements: They are using the so called binary protocol. The MySQL server sends result set data "as is" in binary format. Results are not serialized into strings before sending. The client libraries do not receive strings only. Instead, they will receive binary data and try to convert the values into appropriate PHP data types. For example, results from an SQL INT column will be provided as PHP integer variables. 
  • Non-prepared statements: Using the so called text protocol.The mysqli_query(), mysqli_real_query() and mysqli_multi_query() functions are used to execute non-prepared statements. By default, non-prepared statements return all results as strings(With the text protocol, the MySQL server converts all data of a result sets into strings before sending). This default can be changed using a connection option. If the connection option is used ($mysqli->options(MYSQLI_OPT_*******, $opt_value);), there are no differences.

 

Prepared statements return unbuffered result sets by default. The results of the statement are not implicitly fetched and transferred from the server to the client for client-side buffering. The result set takes server resources until all results have been fetched by the client. Thus it is recommended to consume results timely. If a client fails to fetch all results or the client closes the statement before having fetched all data, the data has to be fetched implicitly by mysqli.

Benefits

Reduce resources overhead and run faster: The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters(Upon every execution the current value of the bound variable is evaluated and sent to the server). When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster. 

 

Escaping and SQL injection: The parameters to prepared statements don't need to be quoted; the driver automatically handles this, after the statement template is parsed. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Drawbacks

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. Also, consider the use of the MySQL multi-INSERT SQL syntax for INSERTs. For the example, multi-INSERT requires less round-trips between the server and client than the prepared statement.

Simple practice

<?php

/*
 * Role:    Using MySQL Prepared Statements in PHP
 * Author:  Flasle <valinv.com@gmail.com>
 * Created: 2017.03.01
 *
 */

//$username, $passwd, ...

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

if ($mysqli->connect_errno) 
{
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}


$id = 0;
$nickname = "";
$avatar = "";

//mysqli supports positional ? placeholders, pdo_mysql supports positional ? placeholders and named placeholders
$sql = "select id, nickname, avatar from fs_outer_users where username = ? and passwd =? ";

if($stmt = $mysqli->prepare($sql))
{
	//binds parameters for markers 
	$stmt->bind_param("ss", $username, $passwd);
	//executes a prepared query
	$stmt->execute();

	/* 
	 * Common method
	//buffers the result set by the client
	//$stmt->store_result();

	//gets a result set from a prepared statement
	$result = $stmt->get_result();

	//or $row = $result->fetch_assoc();
	while ($row = $result->fetch_array(MYSQLI_ASSOC))
    {
        $id = $row["id"];
        $nickname = $row["nickname"];
        $avatar = $row["avatar"];
    }

    //frees stored result memory for the given statement handle
    $stmt->free_result();
     */

    /*
     * Using bind result method
     */
    //binds variables to prepared statement for result storage
    $stmt->bind_result($id, $nickname, $avatar);

    //fetches results from a prepared statement into the bound variables
    while ($stmt->fetch()) 
    {
        //...
    }

    //closes the prepared statement
    $stmt->close();
}

$mysqli->close();
?>

 

More in Development Center
New on Valinv
Sources
  • http://php.net/manual/en/pdo.prepared-statements.php
  • http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
Related Articles
Sponsored Links