Are PDO prepared statements sufficient to prevent SQL injection

Question

Let s say I have code like this    dbh   new PDO  blahblah      stmt    dbh- gt prepare  SELECT   FROM users where username    username     stmt- gt execute  array   username    gt    REQUEST  username         The PDO documentation says      The parameters to prepared statements don t need to be quoted  the driver handles it for you    Is that truly all I need to do to avoid SQL injections   Is it really that easy   You can assume MySQL if it makes a difference   Also  I m really only curious about the use of prepared statements against SQL injection   In this context  I don t care about XSS or other possible vulnerabilities

User · Accepted Answer

The short answer is NO  PDO prepares will not defend you from all possible SQL-Injection attacks  For certain obscure edge-cases   I m adapting this answer to talk about PDO     The long answer isn t so easy  It s based off an attack demonstrated here   The Attack  So  let s start off by showing the attack      pdo- gt query  SET NAMES gbk     var     xbf x27 OR 1 1       query    SELECT   FROM test WHERE name     LIMIT 1    stmt    pdo- gt prepare  query    stmt- gt execute array  var      In certain circumstances  that will return more than 1 row  Let s dissect what s going on here    Selecting a Character Set   pdo- gt query  SET NAMES gbk      For this attack to work  we need the encoding that the server s expecting on the connection both to encode   as in ASCII i e  0x27 and to have some character whose final byte is an ASCII   i e  0x5c   As it turns out  there are 5 such encodings supported in MySQL 5 6 by default  big5  cp932  gb2312  gbk and sjis   We ll select gbk here   Now  it s very important to note the use of SET NAMES here  This sets the character set ON THE SERVER  There is another way of doing it  but we ll get there soon enough  The Payload  The payload we re going to use for this injection starts with the byte sequence 0xbf27   In gbk  that s an invalid multibyte character  in latin1  it s the string       Note that in latin1 and gbk  0x27 on its own is a literal   character    We have chosen this payload because  if we called addslashes   on it  we d insert an ASCII   i e  0x5c  before the   character  So we d wind up with 0xbf5c27  which in gbk is a two character sequence  0xbf5c followed by 0x27  Or in other words  a valid character followed by an unescaped    But we re not using addslashes    So on to the next step     stmt- execute    The important thing to realize here is that PDO by default does NOT do true prepared statements  It emulates them  for MySQL   Therefore  PDO internally builds the query string  calling mysql real escape string    the MySQL C API function  on each bound string value   The C API call to mysql real escape string   differs from addslashes   in that it knows the connection character set  So it can perform the escaping properly for the character set that the server is expecting  However  up to this point  the client thinks that we re still using latin1 for the connection  because we never told it otherwise  We did tell the server we re using gbk  but the client still thinks it s latin1   Therefore the call to mysql real escape string   inserts the backslash  and we have a free hanging   character in our  escaped  content  In fact  if we were to look at  var in the gbk character set  we d see      OR 1 1     Which is exactly what the attack requires  The Query  This part is just a formality  but here s the rendered query   SELECT   FROM test WHERE name       OR 1 1     LIMIT 1    Congratulations  you just successfully attacked a program using PDO Prepared Statements     The Simple Fix  Now  it s worth noting that you can prevent this by disabling emulated prepared statements    pdo- gt setAttribute PDO  ATTR EMULATE PREPARES  false     This will usually result in a true prepared statement  i e  the data being sent over in a separate packet from the query   However  be aware that PDO will silently fallback to emulating statements that MySQL can t prepare natively  those that it can are listed in the manual  but beware to select the appropriate server version    The Correct Fix  The problem here is that we didn t call the C API s mysql set charset   instead of SET NAMES  If we did  we d be fine provided we are using a MySQL release since 2006   If you re using an earlier MySQL release  then a bug in mysql real escape string   meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed   The bug was fixed in MySQL 4 1 20  5 0 22 and 5 1 11   But the worst part is that PDO didn t expose the C API for mysql set charset   until 5 3 6  so in prior versions it cannot prevent this attack for every possible command   It s now exposed as a DSN parameter  which should be used instead of SET NAMES     The Saving Grace  As we said at the outset  for this attack to work the database connection must be encoded using a vulnerable character set   utf8mb4 is not vulnerable and yet can support every Unicode character  so you could elect to use that instead mdash but it has only been available since MySQL 5 5 3   An alternative is utf8  which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane   Alternatively  you can enable the NO BACKSLASH ESCAPES SQL mode  which  amongst other things  alters the operation of mysql real escape string     With this mode enabled  0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously  i e  0xbf27 is still 0xbf27 etc   mdash so the server will still reject the string as invalid   However  see  eggyal s answer for a different vulnerability that can arise from using this SQL mode  albeit not with PDO    Safe Examples  The following examples are safe   mysql query  SET NAMES utf8     var   mysql real escape string   xbf x27 OR 1 1       mysql query  SELECT   FROM test WHERE name     var  LIMIT 1      Because the server s expecting utf8     mysql set charset  gbk     var   mysql real escape string   xbf x27 OR 1 1       mysql query  SELECT   FROM test WHERE name     var  LIMIT 1      Because we ve properly set the character set so the client and the server match    pdo- gt setAttribute PDO  ATTR EMULATE PREPARES  false    pdo- gt query  SET NAMES gbk     stmt    pdo- gt prepare  SELECT   FROM test WHERE name     LIMIT 1     stmt- gt execute array   xbf x27 OR 1 1          Because we ve turned off emulated prepared statements    pdo   new PDO  mysql host localhost dbname testdb charset gbk    user   password    stmt    pdo- gt prepare  SELECT   FROM test WHERE name     LIMIT 1     stmt- gt execute array   xbf x27 OR 1 1          Because we ve set the character set properly    mysqli- gt query  SET NAMES gbk     stmt    mysqli- gt prepare  SELECT   FROM test WHERE name     LIMIT 1     param     xbf x27 OR 1 1       stmt- gt bind param  s    param    stmt- gt execute      Because MySQLi does true prepared statements all the time   Wrapping Up  If you    Use Modern Versions of MySQL  late 5 1  all 5 5  5 6  etc  AND PDO s DSN charset parameter  in PHP   5 3 6    OR   Don t use a vulnerable character set for connection encoding  you only use utf8   latin1   ascii   etc    OR   Enable NO BACKSLASH ESCAPES SQL mode   You re 100  safe   Otherwise  you re vulnerable even though you re using PDO Prepared Statements     Addendum  I ve been slowly working on a patch to change the default to not emulate prepares for a future version of PHP  The problem that I m running into is that a LOT of tests break when I do that  One problem is that emulated prepares will only throw syntax errors on execute  but true prepares will throw errors on prepare  So that can cause issues  and is part of the reason tests are borking

User · Answer

Prepared statements   parameterized queries are generally sufficient to prevent 1st order injection on that statement    If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection   2nd order injection means data has been cycled through the database once before being included in a query  and is much harder to pull off   AFAIK  you almost never see real engineered 2nd order attacks  as it is usually easier for attackers to social-engineer their way in  but you sometimes have 2nd order bugs crop up because of extra benign   characters or similar   You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query  As an example  let s say you enter the following information as your new username when creating an account on a web site  assuming MySQL DB for this question         SELECT UserName         Password FROM Users LIMIT 1        If there are no other restrictions on the username  a prepared statement would still make sure that the above embedded query doesn t execute at the time of insert  and store the value correctly in the database  However  imagine that later the application retrieves your username from the database  and uses string concatenation to include that value a new query  You might get to see someone else s password  Since the first few names in users table tend to be admins  you may have also just given away the farm   Also note  this is one more reason not to store passwords in plain text    We see  then  that prepared statements are enough for a single query  but by themselves they are not sufficient to protect against sql injection attacks throughout an entire application  because they lack a mechanism to enforce all access to a database within an application uses safe code  However  used as part of good application design  mdash  which may include practices such as code review or static analysis  or use of an ORM  data layer  or service layer that limits dynamic sql  mdash  prepared statements are the primary tool for solving the Sql Injection problem  If you follow good application design principles  such that your data access is separated from the rest of your program  it becomes easy to enforce or audit that every query correctly uses parameterization  In this case  sql injection  both first and second order  is completely prevented       It turns out that MySql PHP are  okay  were  just dumb about handling parameters when wide characters are involved  and there is still a rare case outlined in the other highly-voted answer here that can allow injection to slip through a parameterized query

User · Answer

No this is not enough  in some specific cases   By default PDO uses emulated prepared statements when using MySQL as a database driver  You should always disable emulated prepared statements when using MySQL and PDO    dbh- gt setAttribute PDO  ATTR EMULATE PREPARES  false     Another thing that always should be done it set the correct encoding of the database    dbh   new PDO  mysql dbname dbtest host 127 0 0 1 charset utf8    user    pass      Also see this related question  How can I prevent SQL injection in PHP   Also note that that only is about the database side of the things you would still have to watch yourself when displaying the data  E g  by using htmlspecialchars   again with the correct encoding and quoting style

User · Answer

No  they are not always   It depends on whether you allow user input to be placed within the query itself  For example    dbh   new PDO  blahblah      tableToUse     GET  userTable      stmt    dbh- gt prepare  SELECT   FROM      tableToUse     where username    username     stmt- gt execute  array   username    gt    REQUEST  username         would be vulnerable to SQL injections and using prepared statements in this example won t work  because the user input is used as an identifier  not as data  The right answer here would be to use some sort of filtering validation like    dbh   new PDO  blahblah      tableToUse     GET  userTable     allowedTables   array  users   admins   moderators    if   in array  tableToUse  allowedTables         tableToUse    users     stmt    dbh- gt prepare  SELECT   FROM      tableToUse     where username    username     stmt- gt execute  array   username    gt    REQUEST  username         Note  you can t use PDO to bind data that goes outside of DDL  Data Definition Language   i e  this does not work    stmt    dbh- gt prepare  SELECT   FROM foo ORDER BY  userSuppliedData      The reason why the above does not work is because DESC and ASC are not data  PDO can only escape for data  Secondly  you can t even put   quotes around it  The only way to allow user chosen sorting is to manually filter and check that it s either DESC or ASC

User · Answer

Yes  it is sufficient  The way injection type attacks work  is by somehow getting an interpreter  The database  to evaluate something  that should have been data  as if it was code  This is only possible if you mix code and data in the same medium  Eg  when you construct a query as a string    Parameterised queries work by sending the code and the data separately  so it would never be possible to find a hole in that   You can still be vulnerable to other injection-type attacks though  For example  if you use the data in a HTML-page  you could be subject to XSS type attacks

User · Answer

Eaven if you are going to prevent sql injection front-end  using html or js checks  you d have to consider that front-end checks are  bypassable    You can disable js or edit a pattern with a front-end development tool  built in with firefox or chrome nowadays    So  in order to prevent SQL injection  would be right to sanitize input date backend inside your controller   I would like to suggest to you to use filter input   native PHP function in order to sanitize GET and INPUT values   If you want to go ahead with security  for sensible database queries  I d like to suggest to you to use regular expression to validate data format  preg match   will help you in this case  But take care  Regex engine is not so light  Use it only if necessary  otherwise your application performances will decrease   Security has a costs  but do not waste your performance   Easy example   if you want to double check if a value  received from GET is a number  less then 99     if  preg match    0-9  1 2           is heavyer of   if  isset  value   amp  amp  intval  value    lt 99          So  the final answer is   No  PDO Prepared Statements does not prevent all kind of sql injection   It does not prevent unexpected values  just unexpected concatenation

User · Answer

Personally I would always run some form of sanitation on the data first as you can never trust user input  however when using placeholders   parameter binding the inputted data is sent to the server separately to the sql statement and then binded together   The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement

[php] Are PDO prepared statements sufficient to prevent SQL injection?

The Attack

The Simple Fix

The Correct Fix

The Saving Grace

Safe Examples

Wrapping Up

Addendum

Examples related to php

Examples related to security

Examples related to pdo

Examples related to sql-injection