1

A sibling question has been asked here. I am seeking perspective specific to the MySQLi extension.

In the linked sibling question, the paraphrased conclusion is no, PDO prepared statements are not 100% sufficient to prevent SQL injection. There are certain edge cases and PDO settings that are vulnerable to SQL injection.

My question is, are MySQLi prepared statements 100% sufficient to prevent SQL injection, or does that also have certain settings that we need to update to be totally safe?

Thanks for any help!

Community
  • 1
  • 1
The Gilbert Arenas Dagger
  • 12,071
  • 13
  • 66
  • 80
  • 1
    It appears that the sibling question's answers cover all points. Do you have a more specific question that isn't covered in the sibling? To review: you need to disable emulated prepared statements and avoid mixing server and client library charsets. I believe the only known exploit to parameterized queries employs specifically gbk charset mismatch. – chugadie Nov 17 '15 at 23:18
  • Are you using PDO or mysqli_* functions? – JoniJnm Nov 17 '15 at 23:34
  • 1
    @chugadie I thought those details were specific to PDO's, mysqli uses emulated prepared statements? – The Gilbert Arenas Dagger Nov 18 '15 at 03:17
  • @JoniJnm I'm talking specifically about mysqli functions – The Gilbert Arenas Dagger Nov 18 '15 at 03:20
  • @TheGilbertArenasDagger no, mysqli does not have emulated prepared statements. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php " The API does not include emulation for client-side prepared statement emulation. " – chugadie Nov 18 '15 at 13:29

2 Answers2

2

Prepared statements are a server feature, not a client library feature. Each mysql client library for PHP - pdo, mysql, and mysqli - use the libmysqlclient library and rely on that for features. PDO is the only library that adds the concept of emulated prepared statements.

All discussions about the security of using prepared statements apply to all client libraries equally. (except for PDO when it uses emulated prepared statements and you are saving data in gbk charset and you mismatch the charset setting at the server and the client library (incidentally this is the only known weakness against addslashes as well))

chugadie
  • 2,786
  • 1
  • 24
  • 33
  • Thanks for the reply, I upvoted your answer, but, respectfully, if we're saying that the discussion about security using prepared statements applies equally except such and such edge case then I don't really consider it equal. Prepared statements really are a database feature, so your core point is taken; you can interface with the database in different ways, but ultimately you are using the same feature. It's less clear to me any default settings that I need to be aware of when using mysqli. – The Gilbert Arenas Dagger Nov 18 '15 at 14:17
  • I guess I don't understand the original question enough. I'm guessing that you want answers like the sibling question but geared exactly at mysqli instead of pdo_mysql. That's why I say everything is exactly the same except for the gbk edge case. (which is extremely hypothetical because nobody saves their data in gbk, not even the Chinese) – chugadie Nov 18 '15 at 14:32
  • I revised my question if it wasn't clear. The impression I'm getting from you is the answer is, yes - prepared statements using MySQLi are safe because emulated prepared statements are not used. Is that a fair takeaway? – The Gilbert Arenas Dagger Nov 18 '15 at 15:14
  • I don't think that's quite the answer I was trying to provide (but it's not wrong). Prepared statements are safe because prepared statements are safe. – chugadie Nov 19 '15 at 12:57
1

Yes, prepared statements are sufficient to prevent SQL injection, but only if you always use them.

No, prepared statements are not sufficient to prevent SQL injection, as not all statements are parameterizable, for example some set commands, however this can be mitigated with white lists and/or regexp.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152