how safe are PDO prepared statements

Question

Started using PDO prepared statements not too long ago, and, as i understand, it does all the escaping/security for you.

for example, assuming $_POST['title'] is a form field.

$title = $_POST['title'];
$query = "insert into blog(userID, title) values (?, ?)"
$st = $sql->prepare($query);
$st->bindParam(1, $_SESSION['user']['userID'], PDO::PARAM_INT);
$st->bindParam(2, $title);
$st->execute();

Is this really safe? Do i have to do anything else? what else do i have to take into consideration?

Thanks.

Solution 1

Strictly speaking, there's actually no escaping needed, because the parameter value is never interpolated into the query string.

The way query parameters work is that the query is sent to the database server when you called prepare(), and parameter values are sent later, when you called execute(). So they are kept separate from the textual form of the query. There's never an opportunity for SQL injection (provided PDO::ATTR_EMULATE_PREPARES is false).

So yes, query parameters help you to avoid that form of security vulnerability.

Are they 100% proof against any security vulnerability? No, of course not. As you may know, a query parameter only takes the place of a single literal value in an SQL expression. You can't make a single parameter substitute for a list of values, for example:

SELECT * FROM blog WHERE userid IN ( ? );

You can't use a parameter to make table names or column names dynamic:

SELECT * FROM blog ORDER BY ?;

You can't use a parameter for any other type of SQL syntax:

SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;

So there are quite a few cases where you have to manipulate the query as a string, prior to the prepare() call. In these cases, you still need to write code carefully to avoid SQL injection.

Solution 2

It's safe from SQL injection.

A couple things it's NOT safe from:

• Denial of service (causing excessive amounts of rows to be created)
• Cross-site scripting attacks (if title is ever echoed back to another user)

Security is more than just preventing SQL injection.

Solution 3

Regarding SQL Injections, I believe that's the safest you can get, specially if you use constants like PDO::PARAM_INT.

Solution 4

Seeing as XSS was mentioned, I think it's also good to take a look at using things such as this input cleaning class http://www.phpclasses.org/browse/package/2189.html to prevent XSS attacks.

View more solutions

Comments

[koen]

This 'specially' needs some clarification IMO. You probably don't want to say that it's 95% safe but if you use the constants it's 100% safe. If without constants it's not 100% safe it is not safe. If it is 100% then it is not 'especially' safe with constants. What's the safety difference between using the constants and not using them?

[sqram]

please contribute. what do you mean if title is echoed back to another user?

[Yuliy]

Let's assume that you have title of blog posts being stored in a database, and other users can view these posts. Then there is a potential cross-site scripting attack in that a malicious user can craft a title that includes HTML to embed a malicious script into the page as it is shown to other users of your site.

[Salman von Abbas]

Also LIKE ? is valid but you should escape characters used for matching.

[datasn.io]

Regarding "There's never an opportunity for SQL injection (provided PDO::ATTR_EMULATE_PREPARES is false).", does this mean PDO emulated prepares are NOT as safe as native prepares of the db driver? If so, why?

[thelastshadow]

A good example of injection with PDO prepares (because of the limitations mentioned in this answer) is the recent drupal vulnerability sektioneins.de/advisories/…

[Bill Karwin]

@thelastshadow, thanks for the real-world example! Upgrade your Drupal, everyone!