PHP - Does PDO quote safe from SQL Injection?

Question

$id = trim((int)$_GET['id']);
$sql = 'SELECT * FROM users WHERE id = ' . $db->quote($id) . ' LIMIT 1';
$run = $db->query($sql)->fetch();

Does PDO's quote method is safe as prepared statements? Or i have to use prepared statements all the way in my script?

Answer 1

Basically quote() is safe as prepared statements but it depends on the proper implementation of quote() and of course also on it's consequent usage. Additionally the implementation of the used database system/PDO driver has to be taken into account in order to answer the question.

While a prepared statement can be a feature of the underlying database protocol (like MySQL) and will then being "prepared" on the database server (a server site prepare), it does not necessarily have to be and can be parsed on client site as well (a client site prepare).

In PDO this depends on:

• Does the driver/database system support server side prepared statements?
• PDO::ATTR_EMULATE_PREPARES must be set to false (default if the driver supports it)

If one of the conditions is not met, PDO falls back to client site prepares, using something like quote() under the hood again.

---

Conclusion:

Using prepared statements doesn't hurt, I would encourage you to use them. Even if you explicitly use PDO::ATTR_EMULATE_PREPARES or your driver does not support server site prepares at all, prepared statements will enforce a workflow where it is safe that quoting can't be forgotten. Please check also @YourCommonSense's answer. He elaborates on that.

Answer 2

Technically - yes.

However, it means that you are formatting your values manually. And manual formatting is always worse than prepared statements, as it makes code bloated and prone to silly mistakes and confusions.

The main problem with manual formatting - it is detachable. Means it can be performed somewhere far away from the actual query execution. Where it can be forgotten, omitted, confused and such.

Answer 3

What is the point of using trim on int. And then quoting that value? Since you have integer value then use it as such

$sql = 'SELECT * FROM users where id = ' . $id . ' LIMIT 1';

Instead of blindly quote everything just mind the types of your variables and make sure you are not doing stupid things like $id = trim((int)$_GET['id']); where $id = (int)$_GET['id']; would be more than enough

If you are not sure you can make it, use prepared statements. But please mind what you are coding