Php PDO query security

Question

I am new to the PDO class, I have been using MYSQLI since just now and I am kind of confused. This question is rather simple but I cannot find the answer in straight text anywhere in the manual. So calling $pdo->query(some query) will automatically escape the query and will not leave any room for potential injections of any kind. Is this true?

Well, how do I escape the query then? If I run prepare() and then query() it throws an error. — php_nub_qq, Jun 01 '13 at 14:21

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

4

NO, this is NOT true.

To avoid any risk of mysql injections you will need either prepared statments or to escape properly your variables (which would involve you to manually escape each variable before submit). I would suggest to use prepared statements because they are way easier to use. Please read this post How can I prevent SQL injection in PHP?. You can either have those with mysqli OR PDO, a nice example of PDO prepared statments, token from stackoverflow

$id  = 1;
$stm = $pdo->prepare("SELECT name FROM table WHERE id=?");
$stm->execute(array($id));
$name = $stm->fetchColumn();

You can learn more here about PDO prepared statements. I would also like you to have a look here How can prepared statements protect from SQL injection attacks?

edited May 23 '17 at 12:18

Community

1
1

answered Jun 01 '13 at 14:22

Fabio

21,516
12
49
63

Well, you don't *need* prepared statements to avoid SQL injection; escaping properly is perfectly fine too. – deceze Jun 01 '13 at 14:26
@deceze you do. prepared statement makes formatting proper, complete and obligatory. while manual formatting doesn't – Your Common Sense Jun 01 '13 at 14:28
@Your You can avoid SQL injections *without* prepared statements. Nobody argues that prepared statements make it *easier*, but they're not the only solution! Don't spread unnecessary FUD. – deceze Jun 01 '13 at 14:29
@deceze you cannot. using prepared statement for the dynamic part of the every query in your application is the only way. Everything else is error prone. – Your Common Sense Jun 01 '13 at 14:37
@Your Yes, manually escaping is *error prone*, but **if done right** it prevents SQL injection just fine! – deceze Jun 01 '13 at 14:38

vdegenne · Answer 2 · 2013-06-01T14:28:52.273

the query function is not safe.

you better use prepare of the PDO object.

e.g.

$sth = $dbh->prepare("select * from mytable where myattr = :attr");

the $sth handler can be used to set the placeholder in your query (e.g. :attr in this example)

you have two choice :

either you use an array directly in the execute function of the handler :

$sth->execute (array ('attr', $myattr));

or the bindParam function of the handler then execute

$sth->bindParam ('attr', $myattr);
$sth->execute();

The method provide a good way of escaping the single quotes in your arguments.

note : also take a loot at Why you Should be using PHP’s PDO for Database Access (net.tutsplus.com)

deceze · Answer 3 · 2013-06-01T14:30:23.193

No, PDO::query is just as vulnerable as mysql_query or any other raw query method.

If you do

$sql = "SELECT foo FROM bar WHERE baz = '$var'";

and $var is

Robert'; DROP TABLE users; --

so the result is

SELECT foo FROM bar WHERE baz = 'Robert'; DROP TABLE users; --'

then no API can help you, because no API can tell the difference between what the query part and what the user value is. This difference is only clear to the API when you use prepared statements or escape special characters in the value properly.

Read The Great Escapism (Or: What You Need To Know To Work With Text Within Text).

Php PDO query security

3 Answers3