php+prepared,Prepared Statements

Prepared Statements

The MySQL database supports prepared statements. A prepared statement

or a parameterized statement is used to execute the same statement

repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages:

prepare and execute. At 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 MySQL server supports using anonymous, positional placeholder

with ?.

Example #1 First stage: prepare

$mysqli = new mysqli ( "example.com" , "user" , "password" , "database" );

if ( $mysqli -> connect_errno ) {

echo "Failed to connect to MySQL: (" . $mysqli -> connect_errno . ") " . $mysqli -> connect_error ;

} if (! $mysqli -> query ( "DROP TABLE IF EXISTS test" ) || ! $mysqli -> query ( "CREATE TABLE test(id INT)" )) {

echo "Table creation failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

} if (!( $stmt = $mysqli -> prepare ( "INSERT INTO test(id) VALUES (?)" ))) {

echo "Prepare failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

} ?>

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.

Example #2 Second stage: bind and execute

if (! $stmt -> bind_param ( "i" , $id )) {

echo "Binding parameters failed: (" . $stmt -> errno . ") " . $stmt -> error ;

}

if (! $stmt -> execute ()) {

echo "Execute failed: (" . $stmt -> errno . ") " . $stmt -> error ;

} ?>

Repeated execution

A prepared statement can be executed repeatedly. Upon every execution

the current value of the bound variable is evaluated and sent to the server.

The statement is not parsed again. The statement template is not

transferred to the server again.

Example #3 INSERT prepared once, executed multiple times

$mysqli = new mysqli ( "example.com" , "user" , "password" , "database" );

if ( $mysqli -> connect_errno ) {

echo "Failed to connect to MySQL: (" . $mysqli -> connect_errno . ") " . $mysqli -> connect_error ;

} if (! $mysqli -> query ( "DROP TABLE IF EXISTS test" ) || ! $mysqli -> query ( "CREATE TABLE test(id INT)" )) {

echo "Table creation failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

} if (!( $stmt = $mysqli -> prepare ( "INSERT INTO test(id) VALUES (?)" ))) {

echo "Prepare failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

} $id = 1 ;

if (! $stmt -> bind_param ( "i" , $id )) {

echo "Binding parameters failed: (" . $stmt -> errno . ") " . $stmt -> error ;

}

if (! $stmt -> execute ()) {

echo "Execute failed: (" . $stmt -> errno . ") " . $stmt -> error ;

} for ( $id = 2 ; $id < 5 ; $id ++) {

if (! $stmt -> execute ()) {

echo "Execute failed: (" . $stmt -> errno . ") " . $stmt -> error ;

}

} $stmt -> close (); $res = $mysqli -> query ( "SELECT id FROM test" ); var_dump ( $res -> fetch_all ()); ?>

以上例程会输出：

array(4) {

[0]=>

array(1) {

[0]=>

string(1) "1"

}

[1]=>

array(1) {

[0]=>

string(1) "2"

}

[2]=>

array(1) {

[0]=>

string(1) "3"

}

[3]=>

array(1) {

[0]=>

string(1) "4"

}

}

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.

Using a prepared statement is not always the most efficient

way of executing a statement. A prepared statement executed only

once causes more client-server round-trips than a non-prepared statement.

This is why the SELECT is not run as a

prepared statement above.

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

Example #4 Less round trips using multi-INSERT SQL

<?php if (! $mysqli -> query ( "INSERT INTO test(id) VALUES (1), (2), (3), (4)" )) {

echo "Multi-INSERT failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

} ?>

Result set values data types

The MySQL Client Server Protocol defines a different data transfer protocol

for prepared statements and non-prepared statements. Prepared statements

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.

Example #5 Native datatypes

$mysqli = new mysqli ( "example.com" , "user" , "password" , "database" );

if ( $mysqli -> connect_errno ) {

echo "Failed to connect to MySQL: (" . $mysqli -> connect_errno . ") " . $mysqli -> connect_error ;

}

if (! $mysqli -> query ( "DROP TABLE IF EXISTS test" ) ||

! $mysqli -> query ( "CREATE TABLE test(id INT, label CHAR(1))" ) ||

! $mysqli -> query ( "INSERT INTO test(id, label) VALUES (1, 'a')" )) {

echo "Table creation failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

} $stmt = $mysqli -> prepare ( "SELECT id, label FROM test WHERE id = 1" ); $stmt -> execute (); $res = $stmt -> get_result (); $row = $res -> fetch_assoc (); printf ( "id = %s (%s)\n" , $row [ 'id' ], gettype ( $row [ 'id' ])); printf ( "label = %s (%s)\n" , $row [ 'label' ], gettype ( $row [ 'label' ])); ?>

以上例程会输出：

id = 1 (integer)

label = a (string)

This behavior differs from non-prepared statements. By default,

non-prepared statements return all results as strings.

This default can be changed using a connection option.

If the connection option is used, there are no differences.

Fetching results using bound variables

Results from prepared statements can either be retrieved by

binding output variables, or by requesting a mysqli_result object.

Output variables must be bound after statement execution.

One variable must be bound for every column of the statements result set.

Example #6 Output variable binding

$mysqli = new mysqli ( "example.com" , "user" , "password" , "database" );

if ( $mysqli -> connect_errno ) {

echo "Failed to connect to MySQL: (" . $mysqli -> connect_errno . ") " . $mysqli -> connect_error ;

}

if (! $mysqli -> query ( "DROP TABLE IF EXISTS test" ) ||

! $mysqli -> query ( "CREATE TABLE test(id INT, label CHAR(1))" ) ||

! $mysqli -> query ( "INSERT INTO test(id, label) VALUES (1, 'a')" )) {

echo "Table creation failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

}

if (!( $stmt = $mysqli -> prepare ( "SELECT id, label FROM test" ))) {

echo "Prepare failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

}

if (! $stmt -> execute ()) {

echo "Execute failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

} $out_id = NULL ; $out_label = NULL ;

if (! $stmt -> bind_result ( $out_id , $out_label )) {

echo "Binding output parameters failed: (" . $stmt -> errno . ") " . $stmt -> error ;

}

while ( $stmt -> fetch ()) { printf ( "id = %s (%s), label = %s (%s)\n" , $out_id , gettype ( $out_id ), $out_label , gettype ( $out_label ));

} ?>

以上例程会输出：

id = 1 (integer), label = a (string)

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.

It is also possible to buffer the results of a prepared statement

using mysqli_stmt_store_result().

Fetching results using mysqli_result interface

Instead of using bound results, results can also be retrieved through the

mysqli_result interface. mysqli_stmt_get_result()

returns a buffered result set.

Example #7 Using mysqli_result to fetch results

$mysqli = new mysqli ( "example.com" , "user" , "password" , "database" );

if ( $mysqli -> connect_errno ) {

echo "Failed to connect to MySQL: (" . $mysqli -> connect_errno . ") " . $mysqli -> connect_error ;

}

if (! $mysqli -> query ( "DROP TABLE IF EXISTS test" ) ||

! $mysqli -> query ( "CREATE TABLE test(id INT, label CHAR(1))" ) ||

! $mysqli -> query ( "INSERT INTO test(id, label) VALUES (1, 'a')" )) {

echo "Table creation failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

}

if (!( $stmt = $mysqli -> prepare ( "SELECT id, label FROM test ORDER BY id ASC" ))) {

echo "Prepare failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

}

if (! $stmt -> execute ()) {

echo "Execute failed: (" . $stmt -> errno . ") " . $stmt -> error ;

}

if (!( $res = $stmt -> get_result ())) {

echo "Getting result set failed: (" . $stmt -> errno . ") " . $stmt -> error ;

} var_dump ( $res -> fetch_all ()); ?>

以上例程会输出：

array(1) {

[0]=>

array(2) {

[0]=>

int(1)

[1]=>

string(1) "a"

}

}

Using the mysqli_result interface offers the additional benefit of

flexible client-side result set navigation.

Example #8 Buffered result set for flexible read out

$mysqli = new mysqli ( "example.com" , "user" , "password" , "database" );

if ( $mysqli -> connect_errno ) {

echo "Failed to connect to MySQL: (" . $mysqli -> connect_errno . ") " . $mysqli -> connect_error ;

}

if (! $mysqli -> query ( "DROP TABLE IF EXISTS test" ) ||

! $mysqli -> query ( "CREATE TABLE test(id INT, label CHAR(1))" ) ||

! $mysqli -> query ( "INSERT INTO test(id, label) VALUES (1, 'a'), (2, 'b'), (3, 'c')" )) {

echo "Table creation failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

}

if (!( $stmt = $mysqli -> prepare ( "SELECT id, label FROM test" ))) {

echo "Prepare failed: (" . $mysqli -> errno . ") " . $mysqli -> error ;

}

if (! $stmt -> execute ()) {

echo "Execute failed: (" . $stmt -> errno . ") " . $stmt -> error ;

}

if (!( $res = $stmt -> get_result ())) {

echo "Getting result set failed: (" . $stmt -> errno . ") " . $stmt -> error ;

}

for ( $row_no = ( $res -> num_rows - 1 ); $row_no >= 0 ; $row_no --) { $res -> data_seek ( $row_no ); var_dump ( $res -> fetch_assoc ());

} $res -> close (); ?>

以上例程会输出：

array(2) {

["id"]=>

int(3)

["label"]=>

string(1) "c"

}

array(2) {

["id"]=>

int(2)

["label"]=>

string(1) "b"

}

array(2) {

["id"]=>

int(1)

["label"]=>

string(1) "a"

}

Escaping and SQL injection

Bound variables are sent to the server separately from the query and thus

cannot interfere with it. The server uses these values directly at the point

of execution, after the statement template is parsed. Bound parameters do not

need to be escaped as they are never substituted into the query string

directly. A hint must be provided to the server for the type of bound

variable, to create an appropriate conversion.

See the mysqli_stmt_bind_param() function for more

information.

Such a separation sometimes considered as the only security feature to

prevent SQL injection, but the same degree of security can be achieved with

non-prepared statements, if all the values are formatted correctly. It should

be noted that correct formatting is not the same as escaping and involves

more logic than simple escaping. Thus, prepared statements are simply a more

convenient and less error-prone approach to this element of database security.

Client-side prepared statement emulation

The API does not include emulation for client-side prepared statement emulation.

Quick prepared - non-prepared statement comparison

The table below compares server-side prepared and non-prepared statements. Comparison of prepared and non-prepared statements Prepared Statement Non-prepared statement

Client-server round trips, SELECT, single execution 2 1

Statement string transferred from client to server 1 1

Client-server round trips, SELECT, repeated (n) execution 1 + n n

Statement string transferred from client to server 1 template, n times bound parameter, if any n times together with parameter, if any

Input parameter binding API Yes, automatic input escaping No, manual input escaping

Output variable binding API Yes No

Supports use of mysqli_result API Yes, use mysqli_stmt_get_result() Yes

Buffered result sets Yes, use mysqli_stmt_get_result() or

binding with mysqli_stmt_store_result() Yes, default of mysqli_query()

Unbuffered result sets Yes, use output binding API Yes, use mysqli_real_query() with

mysqli_use_result()

MySQL Client Server protocol data transfer flavor Binary protocol Text protocol

Result set values SQL data types Preserved when fetching Converted to string or preserved when fetching

Supports all SQL statements Recent MySQL versions support most but not all Yes

See also

mysqli::__construct()

mysqli::query()

mysqli::prepare()

mysqli_stmt::prepare()

mysqli_stmt::execute()

mysqli_stmt::bind_param()

mysqli_stmt::bind_result()