Now that we have seen the basic use of the extension, let’s examine a few of the new features.
Prepared statements provide developers with the ability tocreate queries that are more secure, have better performance,and are more convenient towrite.
They come in two flavors: bound parameter prepared statements,and bound result prepared statements.
Bound Parameters
Bound parameter prepared statements allow query templates to be created andthen stored on the MySQL server. When a query needs to be made,datato fill in the template is sent to the MySQL server,and a complete query is formed andthen executed.
The basic process for creating andusing bound parameter prepared statements issimple.
A query template is created and sent to the MySQL server. The MySQL server receives the query template, validates it to ensure that it is well-formed, parses it to ensure that it is meaningful,and stores it in a special buffer. It thenreturns a special handle that can later be used to reference the prepared statement.
When a query needs to be made,datato fill in the template is sent to the MySQL server,andthen a complete query is formed andthen executed.
This process has some very important behaviors wrapped up in it.
The body of the query is only sent to the MySQL server once. On requests to execute the query, only the datato fill in the template needs to be delivered to the MySQL server.
Most of the work required to validate and parse the query only needs to be done a single time, instead of each time that the query is executed.
Additionally, for queries that contain a small amount of data, the overhead of sending the query is greatly reduced. For example,if you have a query like:
then each time that you execute the query, you will only need to send about 16 bytes of query data, instead of 60or more bytes. (These approximate numbers include overhead for the foo and bar query datalike the id of the prepared statement, the length of the query data for binary safety, etc, but donot include extra overhead for the query string.)
The data for the query does not need to be passed through a functionlike mysql_real_escape_string()to ensure that no SQL injection attacks[4] occur. Instead, the MySQL client and server work together to ensure that the sent datais handled safely when it is combined with the prepared statement.
Note that bind_param() has a short string as its first parameter. This is a format string that is used to specify how the datain the bound variables should be treated.
In the case of the above script, ‘sssd’ indicates that the values of the first three parameters $code, $language and $official will be sent as a strings, while the fourth parameter $percent will contain a doubleorfloatvalue.
For each bound variable in bind_param(), there should be another letter in the format string that specifies how the variable should be handled. e.g.
$stmt->bind_param('s', $foo);
$stmt->bind_param('si', $foo, $bar);
$stmt->bind_param('sid', $foo, $bar, $baz);
The bind types let the mysqli extension know how toencode the data that it sends for greater efficiency.