Join David Powers for an in-depth discussion in this video Using prepared statements, part of Accessing Databases with Object-Oriented PHP.
- View Offline
Among the advantages of choosing either PDO or MySQLi is their support for prepared statments which offer important security features. A prepared statement is a template for an SQL query that incorporates value from user input. The prepared statement contains a placeholder for each value that's stored in a variable. This not only makes it easier to embed the variables in your PHP code, it also prevents SQL injection attacks, because PDO and MySQLi automatically escape quotes and other characters before executing the query.
Other advantages of using prepared statements are that they're more efficient when the same query is used more than once. And you combine the results from each column of the select query to named variables, making it easier to display the output. Both PDO and MySQLi use question marks as anonymous placeholders. In this example, the question marks represent the values for username and password, gathered from user input. You'll see later in the course how to bind the values to the placeholders.
PDO also supports the use of named placeholders. A named placeholder begins with a colon followed by an identifier, which doesn't necessarily need to be the same as the column name. In this example, both user name and password are likely to be strings. But the placeholders are not wrapped in quotes. This applies to both named placeholders and anonymous placeholders. This makes it a lot easier to build an SQL query because there's no need to worry about getting the correct combination of single and double quotes.
Placeholders can be used only for column values. They can't be used for other parts of an SQL query, such as column names or operators. This is because values that contain non-numeric characters are automatically escaped and wrapped in quotes when the SQL is executed. Column names and operators can't be in quotes. Using a prepared statement involves the following steps. You begin by passing the SQL and placeholders to the prepare method, which checks if the syntax is valid.
Next, you bind values to the placeholders, then execute the prepared statement. Optionally, you can bind the results from each column of the select query to named variables. Finally, you fetch the results. Prepared statements involve slightly more code than submitting the query directly, but placeholders make the SQL easier to read and write, and the process is more secure. When the same query needs to be used more than once within the same script, a prepared statement increases efficiency, by analyzing and optimizing the SQL only once.
The values for each place holder are sent separately and interpolated into the optimized statement. A non-prepared statement on the other hand needs to be analyzed and optimized every single time. This can slow down an application noticeably. When a query is submitted only once, the question of efficiency isn't quite so clear cut. Using a prepared statement for a single query involves two round trips to the database server. The first time to validate and optimize the SQL, the second to send the values for the place holders.
On the other hand, a non prepared statement combines optimization and execution in a single operation. This means that if you have a simple query that's executed only once, it's arguably more efficient to check user input yourself and embed the value directly into the SQL, because it involves only a single round trip to the server. But user input still needs to be sanitized before incorporation into SQL. So the possible efficiency gain on the database server needs to be balanced against the effort involved in preventing SQL injection.
If in doubt, use a prepared statement to handle user input. Particularly, when multiple values need to be embedded in a query. Unless your site experience is very high traffic, the security offered by compared statements outweighs marginal differences in efficiency.
- Connecting to a database with PDO or MySQLi
- Fetching a result set
- Executing simple non-SELECT queries
- Sanitizing user input
- Binding input and output values
- Passing an array of values to the execute() method
- Working with advanced PDO fetch methods
- Executing a MySQLi transaction
- Freeing resources that are no longer needed
- Submitting multiple queries
- Creating an instance of a class from a result set