Using prepared statements

Using prepared statements

Online by JSC0d3
June 16, 2014 | | 1210 recognitions

If you look at an HTML file, you’ll see that it’s carrying both the content and instructions on how to show it. Commonly SQL queries are written the same way. 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.

Using prepared statements php

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 the $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.

$query = 'SELECT * FROM my_table WHERE title = :title';
$stmt = $db->prepare($query);
$stmt->bindValue(':title', $title);

Here’s a full example:

$db = new PDO('mysql:host=hostname;dbname=defaultDbName', 
              'username', 'password', 
              array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

$query = 'SELECT * FROM my_table WHERE title = :title';

$stmt = $db->prepare($query);
$stmt->bindValue(':title', $myTitle);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  // ...
}

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 bindValue() and 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 bindValue().

WHERE … IN

Sadly PDO doesn’t support arrays in prepared statements, for example:

SELECT * FROM my_table WHERE id IN (1, 5, 7);

To do this in PDO, you’ll have to escape the values manually using the quote() method. Here’s an example:

$db = new PDO('mysql:host=hostname;dbname=defaultDbName', 
              'username', 'password', 
              array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

$names  = array('Alice', 'Bob', 'Charlie');
$values = array_map(array($db,'quote'),$names);
$query  = 'SELECT * FROM my_table WHERE name IN ('.join(',',$values).')';

$result = $db->query($query);

while($row = $result->fetch(PDO::FETCH_ASSOC)) {
  // ...
}

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).

JSC0d3's Logo
About JSC0d3

JSC0d3 is an entrepreneur, online marketer, and an employee of an IT company. When not building websites, creating content, or helping customers improve their online business, spend time with their wife and two beautiful children. Although he still feels new in WordPress, he enjoys sharing what he has learned with all of you! If you want to get in touch with him, you can do this through this website.

On the same idea

Posted by | October 1, 2017

The WordPress platform is a magnet for those who want to take matters into their own hands, who want complete control over their websites and want to...

Posted by | September 22, 2017

Are you finding issues in managing the image quality for your WordPress powered website/blog If your answer is a “Yes” then this is a post just...

Posted by | August 14, 2017

Do you want to capture more data when creating a post or a page Do you want to add some custom fields to your custom post type With WordPress’s...

Previous PostBackNext Post

Leave here an impression