Whether you’re reading from, or writing to a database, using prepared statements are easy, convenient, and secure. So what are they?
About these examples
If you look at an HTML file, you’ll see that it’s carrying both the content and instructions on how to show it. Usually, when you write an SQL query, it’s the same. The problem with that is that if you’re not careful, you (or more annoyingly, someone else) can write content that gets mistaken for commands.
In HTML, that ends up with broken layouts or cross-site scripting attacks, but with databases, it can hand over all your data to attackers.
With HTML the only answer is to always escape your content so it can never be interpreted as HTML tags when you don’t want it to. You can do that with your SQL too, by using your database’s escaping functions (like
mysql_real_escape_string()), but there’s a better way, which is to use a prepared statement. With this method, your command and the content are sent
along separate channels to the database, which means you never need to worry about things like SQL injection attacks.
Prepared statements are also faster if you’re running the same query often since your database won’t need to interpret the command every time it’s sent.
Look at the SQL statement in the example below. You can see that instead of putting
$title we want directly into the query, we put a placeholder instead, called
:title, and then we “bind” the value we want to the placeholder. PDO then passes the SQL and the data to the database separately.
Values vs Params
Notice that in the examples, I’ve used
bindValue(). If you’ve looked at the PDO manual, you may have wondered what the difference is between
bindParam(). The answer is that
bindParam() connects the variable by
reference instead of just taking the value from the variable. This means if you change the variable, you change the SQL statement. It’s pretty useful in a
foreach loop if you’re adding a lot of rows to your database, but it can also cause some pretty confusing errors if you don’t realise what it’s doing. My personal recommendation is to avoid surprises and always use
WHERE … IN
Sadly MySQL doesn’t support arrays in prepared statements, for example:
SELECT * FROM my_table WHERE id IN (1, 5, 7);
In this instance, you’ll have to escape the values manually using PDO’s
In the example above, we use
array_map() to run the same method on every item in the input array, and return the result as a new array. The method is the
quote() method of the PDO database object, which does the appropriate form of
backslashing for the current database connection. It’s really important that you use
quote() instead of
addslashes() because not all databases use quotes and backslashes the same way (or at all).
That’s pretty much all you need to know, but if you want to see more examples or find out more about prepared statements, have a look at the PHP manual page on the subject.