4

This is a security question meant for PDO prepared statements. Using PDO I know that the risk of sql injection is virtually impossible and that the class handles all of that for you. Coming from using them mysql_* stack I always feel as if I'm not handling security issues well enough. I'm usually used to writing hundreds of lines of code just to deal with security while now I'm literally just writing queries.

Is there really any security threats with PDO statements that I have to worry about other than length of the string in the db?

Amirshk
  • 8,170
  • 2
  • 35
  • 64
  • This is kind of like asking "Are there any security issues I need to be concerned about in programming?" Probably too broad. – FoolishSeth Apr 13 '13 at 02:44
  • Take a look at this answer http://stackoverflow.com/a/8265319/689579 – Sean Apr 13 '13 at 02:50
  • 1
    Actually I think this is a fairly well defined question, it's basically, "What security threats does POD not automatically protect against" I like it. – Hailwood Apr 13 '13 at 03:10
  • 1
    Nothings 100% safe in IT. PDO is just another line of defense. You still should validate your inputs before passing them on to your queries... – Jay Bhatt Apr 13 '13 at 03:43
  • PDO just shields you from *SQL*-related stuff. There are many other security risks on the web, from simple Javascript injections to more "exotic" problems like XSS attacks having to do with character encodings. Most of these things go right through your prepared statements. – Sverri M. Olsen Apr 13 '13 at 03:56

2 Answers2

2

Definitely yes.

As a matter of fact, native prepared statements are good for simple schoolbook cases only.
Means you still have to write some "hundreds of lines" for whatever complex cases. I made a small digest of such cases in the PDO tag wiki. Main disadvantages are

  • no placeholders for identifiers. You have to format and inject them manually like in old good mysql_* code.
  • no placeholders for arrays. Means you still have to write some code manually and later inject it in the query - so, there is still a possibility to slip into some trouble.

Therefore you need a higher level of abstraction even upon PDO. A common solution is to use some sort of Query Builder which offered by every modern framework.

But personally I hate query builders as they seems too bloated to me, pretending to replace whole SQL but obviously failing with it. So, I don't understand why use SQL written in PHP when I can use pure SQL. For this purpose I created an abstraction library of mine, to correct all the disadvantages of native prepared statements, safeMysql. It has placeholders for everything you need and thus makes queries much safer than PDO, yet it makes application code dramatically shorter.

Means with safeMysql you indeed can "just literally write queries":

$sql  = "SELECT * FROM articles WHERE id IN(?a) ORDER BY ?n LIMIT ?i"
$data = $db->getAll($sql, $ids,$_GET['order'], $limit);

$sql  = "INSERT INTO stats SET pid=?i,ip=inet_aton(?s),?u ON DUPLICATE KEY UPDATE ?u";
$db->query($sql, $pid, $ip, $data, $data);

Just compare these 2-liners with amount of code you will need to write using raw PDO.

Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

I think the answer is pretty close to yes for "sql injection attacks". mysqli prepared statements and mysqli_real_escape_string

There are still many other types of attacks, but at least all your values are escaped.

Relying on PDO to "fix" your security is like relying on a compiler to find your bugs.

Community
  • 1
  • 1
BraveNewCurrency
  • 12,654
  • 2
  • 42
  • 50