I have already written a post titled “Php Prepared Statements” but it was very brief and did not explain the workings of Prepared Statements, neither did it mention the PROS of using Prepared Statements. In this post, I want to provide more info about Prepared Statements in PHP. What are their advantages, why you should use them, how they work… Then I will provide you with a simple example script and explain what it does.
Main Advantages of Prepared Statements
1- Prepared statements reduces parsing time as the preparation on the query is done only once (although the statement is executed multiple times)
The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. 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.
2- Prepared statements are very useful against SQL injections
Parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.
Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query.
How Do Prepared Statements Work
SQL statement template is created and sent to the database. Parameters that are to be bound later are left as question marks in the query. See “3” above.
Database parses, compiles and optimizes the SQL statement template, and stores the result. See “1” above.
Later, application binds parameters to the query and database executes the query. Statement can be executed as many times as needed with different parameters.
Here is a very simple script that prepares and executes a prepared statement:
//$conn is our MySQLi connection object
//This code uses MySQLi object-oriented notation
$query = "select myCol from myTable where myVariable=?";
$myVar = "some string value";
$stmt = $conn->prepare($query);
//Then place the results in a variable and iterate over the results using loops
As you can see here, we have 1 place in the query where a variable will be bound later. We are using “?” in place of the variable in our query template.
We prepare our statement using the “prepare” method then bind our variable using “bind_param” method. Here you will see the first parameters says “s”. That is the type string which indicates the type of the variable that will be bound to the query later. After binding is complete, we finally execute the statement.
Let’s talk about the type string parameter which is the first parameter in “bind_param” method. As I said, type string tells the types of the parameters to be bound. There are 4 types in PHP Prepared Statements:
b: Corresponding variable is a blob and will be sent in packets
You may ask, what about other data types like DateTime…? How can I bind it? What is the type string for it? The answer for this question is string. You can use “s” as the type string for a DateTime value. For the other data types you should do your research and find which type strings work for them.
The number of variables and length of type string must match the parameters in the statement. If there are multiple parameters in your queries, this type string will be a string of types that are concatenated with each other. If you have 2 string parameters, type string will be “ss” and your “bind_param” would be like:
$stmt->bind_param("ss", $myVar1, $myVar2);
If you are to bind an integer and a string value respectively, then your bind_param would be like:
$stmt->bind_param("is", $myVar3, $myVar4);
Hope I was able to provide you with an introductory info on PHP Prepared Statements. For a little more advanced topic of binding variable number of parameters to prepared statements check this post out.
Protect the forests.