What exactly makes PDO secure?

Question

Possible Duplicate:
How prepared statements can protect from SQL injection attacks?

For those of us that are new to PDO, we get that it is more secure and that it is better to use, but what I can't wrap my brain around is, how is this secured?

<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
try {
    //connect as appropriate as above
    $db->query('hi'); //invalid query!
} catch(PDOException $ex) {
    echo "An Error occured!"; //user friendly message
    some_logging_function($ex->getMessage());
}
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'].' '.$row['field2']; //etc...
}
?>

Mind you, I do understand what it does, but what exactly does it do to sanitize input? I know mysql_* use mysql_real_escape_string which just put the literal \. Does PDO use this same system? If not, what are we relying on as far as sanitation?

[How prepared statements can protect from SQL injection attacks?](http://stackoverflow.com/q/8263371/285587) — Your Common Sense, Jan 29 '13 at 15:22
It's not PDO itself that protects you from SQL injection. It is the use of prepared statements. If you call `$db->query("select * from users where userid=$something_from_the_user")`, you're doing it wrong. The key is to not use any data in the building of your SQL statements. — Andy Lester, Jan 29 '13 at 16:10
@AndyLester the bad news that in whatever more or less comlex application you cannot avoid using dynamical data in the building of your SQL statements. Or it makes your code enormously complex. — Your Common Sense, Jan 29 '13 at 16:24
It's all about how much complexity you're willing to pay for the benefit of not using unsafe data when building your executable code. That we sometimes have to pay for safety with complexity is not news. — Andy Lester, Jan 29 '13 at 16:38

score 4 · Accepted Answer · edited May 23 '17 at 12:21

4

While there doesn't seem to be anything to sanitize as input in your query. Furthermore, if you just put in your query it will not do anything to it.

But it does have the magic called prepared statements, which does help you. You can check @yourcommonsense 's link for more information on that:

How can prepared statements protect from SQL injection attacks?

edited May 23 '17 at 12:21

Community

1
1

answered Jan 29 '13 at 15:25

Nanne

64,065
16
119
163

So, what im gathering, PDO makes two connections to the database, the first one with the query, the second with the user input? – Nick Jan 29 '13 at 15:28
I would call the first one "preparing", but basically, yes. It does make 'n+1' connections to be correct, if you need to do it again you can re-use the allready prepared query. – Nanne Jan 29 '13 at 15:30
so, this become its own variable and conenction: `$sql->prepare("SELECT * FROM users where id=?");` and then this also becomes its own execution? `$db->execute($data);` if this is the case, does the database put the two together upon the second query being ran? or does PDO? And essentially we can use `$sql` again? – Nick Jan 29 '13 at 15:35
1

Prepared statements are supported by most DBMS directly, if it does not, PDO will emulate it: ["Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database."](http://www.php.net/manual/en/pdo.prepared-statements.php) – dualed Jan 29 '13 at 15:42
Thank you both, i now have a pretty good understanding of how it works, still a little confused on the syntax of prepared execute etc.. but its probably better if i figure that out on my own. – Nick Jan 29 '13 at 15:49
@JohnDoe, there are examples of using PDO's prepare() followed by execute() here: http://www.php.net/manual/en/pdo.prepare.php – Bill Karwin Jan 29 '13 at 17:34

score -1 · Answer 2 · answered Jan 29 '13 at 16:13

PDO has the PDO::Quote() method which does the same job as mysql_real_escape_string(). This provides basically the same functionality and the sme level of security.

A better option is the "Prepared Statements" functionality, where you put markers into the query rather than variables using PDO::prepare(), and then use PDO::execute() to pass the variables to the query.

This is more secure because it uses the database engine's internal mechanisms to push the variables into the query. There is zero possibility of a SQL injection attack via a prepared statement (unless the database engine itself has a bug, but that's something you would mitigate by keeping you MySQL version patched up to date; nothing to do with your own code).

Prepared statements also have the bonus that they can be cached by the database engine. This means that if you make multiple calls using the same query but with different variable values, then you will get a performance boost compared with making the same queries using the old string-based mechanism.

Note: Some DBs may not support prepared statements natively, in which case PHP will emulate them. In this case, it's basically the same as using PDO::Quote, and you don't get the benefits listed above. Most DBs do support them tho, but even then it is possible for it to be disabled, so make sure you switch off ATTR_EMULATE_PREPARES.

Finally, using PDO is more secure because the old mysql extension had some fundamental security issues at its core. For example, the old API does not encrypt the communications between PHP and the MySQL database. This means that there is a theoretical risk of an eavesdropper attack, particularly if the DB is not on the same server as PHP. PDO uses a more recent version of the MySQL comms protocol, and is thus able to encrypt this traffic and as a result is more secure (and also why PDO doesn't support really ancient versions of MySQL). There are a handful of other similar hidden risks with the old API that were so deeply ingrained in the code that they could not be removed. This is why the mysqli extension was written as a direct replacement for it.

1. PDO::Quote() method does NOT the same job as mysql_real_escape_string() and there is essential difference. 2. prepared statements aren't "more" secure than proper formatting. They're equal. 3. Usual web-application doesn't make multiple calls using the same query - so, no boosts. 4. What are these "handful of other similar hidden risks"? — Your Common Sense, Jan 29 '13 at 16:20
@YourCommonSense - 1. Yes, I know they're different, but *roughly* the same role. 2. Yes they are: a quoted string query retains a theoretical risk of a character-set-based injection attack; prepared statements do not. 3. I've written a number of web apps that call a prepared query in a loop. I've got a boost out of it. 4. I'd have to go and dig out the references, and I really don't have time. But the encryption was the main one. — SDC, Jan 29 '13 at 16:34

What exactly makes PDO secure?

2 Answers2