SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How PDO avoids SQL injection?

    I have read in a number of places that PDO prepared statements provide the best form of defense against SQL injection? What does PDO provide that mysql_real_escape_string() doesn't, of course, assuming that MySQL is the underlying database.

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    afaik it doesn't do much more than mysql_real_escape_string,
    the difference is that *you* don't have to do it.

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2008
    Posts
    1,149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Parametrized statements don't merely just escape the input. The parameters are transferred to the server in a less risky way.

    However, as far as you are concerned, using prepared statements is a plus because you cannot accidentally forget to escape a piece of input. If you manually construct SQL statements, forgetting to surround a variable in a function call is pretty easy to do.
    Last edited by sk89q; Mar 26, 2009 at 13:13.

  4. #4
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/tech-resources/...tatements.html
    Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack...
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sk89q View Post
    Parametrized statements don't merely just escape the input. They parameters are transferred to the server in a less risky way.

    However, as far as you are concerned, using prepared statements is a plus because you cannot accidentally forget to escape a piece of input. If you manually construct SQL statements, forgetting to surround a variable in a function call is pretty easy to do.
    does that mean that PDO prepared statements are superior not because of any technical merit but because of the fact that it eliminates the odds of the developer forgetting to escape the string ?

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2008
    Posts
    1,149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No. If you plan to execute a query repeatedly but with different parameters, using parametrized queries will give you a performance boost because the DBMS does not have to re-compile the query.

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sk89q View Post
    No. If you plan to execute a query repeatedly but with different parameters, using parametrized queries will give you a performance boost because the DBMS does not have to re-compile the query.
    that is besides the point. We aren't talking about how parameterized queries provide better performance. We are talking about how parameterized queries provide better security over the usual mysql_real_escape_string().

  8. #8
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if used properly, mysql_real_escape_string() provides the same security level as prepared statements...but it's my opinion that it's easy to improperly (by forgetting to use it, or using it in the wrong spot) use it...so i feel safer using prepared statements because as you said, you don't have to worry about escaping your strings at all.

  9. #9
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    It's not there incase you forget to escape the data; It's there so you DON'T need to.

    This about it this way.

    PHP Code:
    $Query MySQL_Query("SELECT cols FROM table WHERE data1 = '$Data1'"); 
    Ok, so as you probably know, if $Data1 contained any unescaped single quotes then the query can be changed, because it's converted into a string BEFORE being sent to MySQL.

    But for PDO:
    PHP Code:
    $Query $Database->Prepare('SELECT cols FROM table WHERE data1 = :data1'); 
    That SQL is sent to the database engine, so that the database engine knows what it's doing without needing the values yet.
    PHP Code:
    $Query->BindValue(':data1'$Data1);
    $Query->Execute(); 
    That value is sent separately from the SQL string - it's sent on its own - the value you give it is taken literally, bit for bit.

    So, whereas with MySQL_Query where your values are sent WITH the SQL, allowing changes in the SQL if unescaped, in PDO the values are sent separately, so it's impossible for it to affect the SQL.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  10. #10
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by LAMP View Post
    .. how parameterized queries provide better security over the usual mysql_real_escape_string().
    With parameterised queries, the query and the data are sent separately to the database server. When the server receives the query, it parses it. Parsing a formal language is rather complex and has many edge-cases. If the query contains data embedded within, there is always the risk that there might be a loophole where this data is inadvertently interpreted as code. With parameterised queries this can never happen, because the data never ends up in the query-parser and thus is never evaluated.

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_real_escape_string() may also not work properly if you change the character set of the connection after establishing the connection. If you use mysql_set_charset(), it will be ok. But a SET NAMES or SET CHARACTER SET query issued through mysql_query() won't change the charset mysql_real_escape_string() uses. There's some charsets which would make sql injection a possibility in this scenario.

    Another drawback is you store this escaped value temporarily. In the meantime, the character set might be changed before you send the query(whether you use SET NAMES or mysql_set_charset() doesn't matter, you already escaped it and you have a string result). Now the values are no longer escaped using the proper charset for the connection, and again, the vulnerability exists.

    Those definately aren't common scenarios. But they are real. I'm not sure how persistant connections play into this. I'm not sure if prepared statements would get the data to mysql correctly if you changed character sets at weird times, but at least there's no chance of sql injection.

  12. #12
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Prepared statements are 2 round-trips to the database for a single query.

    As soon as you prepare a query, it's sent to the database with the placeholders you set. So the database engine takes that prepared statement and maps out the query and optimizes it for execution. Then when you call execute() only the values you give are sent to the database, with a reference to that query you just prepared. The database engine drops in the values and runs the query. This is totally immune to SQL injection, because the database engine already knows exactly where the values begin and end (the placeholder marker(s) you set), and therefore never need escaping.

    The reason SQL injection exists in the first place is because the entire query is interpreted upon execution, values and all. So if anything interferes with the quotes surrounding your values, the engine thinks that value has ended earlier than it really should, and thus a security hole is introduced. That problem is avoided entirely with prepared statements by letting the database engine know ahead of time exactly where to put each value you pass to it later on. There is no need for escaping and there is no need to worry.

  13. #13
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Imagine a container full of cargo.

    Now imagine that something in that container might be radioactive.

    Prepared statements would be like taking that potentially radioactive item out of the container and transporting it in a hazmat container using full precautions, instead of just leaving everything in the normal container and letting the receiving party take their own precautions for the potential radiation.

  14. #14
    We're from teh basements.
    Join Date
    Apr 2007
    Posts
    1,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by LAMP View Post
    that is besides the point. We aren't talking about how parameterized queries provide better performance. We are talking about how parameterized queries provide better security over the usual mysql_real_escape_string().
    It was a direct response to your question about whether prepared statements are superior due to technical merit, actually.

    Be aware that, although the third argument to PDOStatement::bindParam is optional, it is necessary in some cases. For example, I have had PDO try to bind an IP address as a numeric type rather than a VARCHAR(15) when the datatype specification was omitted. One workaround is to quote the variable yourself, but that negates the main benefit of using PDO (automatic escaping) in the first place.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •