SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Mar 2006
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    pdo and sql injection

    I want to ask something simple.At a later project,I used pdo and prepared statements against sql injections and someone told me that it is not proper to use pdo,he did not say why and I did not ask why.Is there a reason not to use pdo lately,because I do not know something like that,I have some time to write php code.

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,148
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    As far as I know, PDO is the best option PHP provides us.

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,807
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Just wanted to provide this information too, found it via a google search (which lead right back to Sitepoint 3 years ago.
    http://www.sitepoint.com/forums/show...-SQL-injection

  4. #4
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    if you bind your parameter in your PDO statements then you will gaurd against SQL Injection attacks.

    wrong:
    Code:
    $sql = "
    SELECT
      name
    FROM
      names
    WHERE
      name 
        LIKE '%$name%';";
    $stmt = $Db->prepare($sql);
    $stmt->execute();
    Correct
    Code:
    $sql = "
    SELECT
      name
    FROM
      names
    WHERE
      name 
        LIKE '%" .? . "%';";
    $stmt = $Db->prepare($sql);
    $stmt->bindParam(1, $name);
    $stmt->execute();
    To protect the SELECT, UPDATE, or INSERT you need to ensure that you not only prepare the statement you also have to bind any parameters that a user or external data-source such as a $_POST, $_GET, JSON, SOAP... value is being used in an SQL statement.

    Regards,
    Steve
    ictus==""

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    The only remotely valid reason I ever heard was that the target server did not support PDO.

    But that was years ago, and I did not buy that reason even then.

    There were those that preferred the mysqli extension when PHP5 first came out and for some time the jury was out, but my last understanding was that PDO was seen as the best way to proceed.

    EDIT: PDO with prepared statements, to be more precise, as has been said above.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    What #ServerStorm said. Too many people have this misunderstanding that "PDO" is the solution – it is not. Any variable binding using PDO, MySQLi, etc will work. Sending the dynamic data separate from the query is what prevents SQL injection – not PDO!!!
    The only code I hate more than my own is everyone else's.

  7. #7
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    Actually, I have a related question. First, I use a home rolled CQRS/EventSourcing system, rather than a traditional mapping style ORM, or data access abstraction layer. So here's the question, which I ask based on assumptions I've made, not really having used PDO...

    Doesn't PDO's (or mysqli_bind for that matter) just call sprintf($sql, $params) underneath? If so, why not just do that? If not, what does PDO's bindParam method actually do?

  8. #8
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Serenarules View Post
    Doesn't PDO's (or mysqli_bind for that matter) just call sprintf($sql, $params) underneath? If so, why not just do that? If not, what does PDO's bindParam method actually do?
    No, its not sprintf. Prepared Statements is a function of the database itself. PDO sends the SQL statement with no data to the server. The DB parses the SQL statements, sends back an identifier. PDO then sends data to the server using the indentifier to link that data to the SQL statement. The SQL and the data never mix.

    Only for database servers that do not support Prepared Statements does PDO emulate them.

    See:
    https://en.wikipedia.org/wiki/Prepared_statements
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  9. #9
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    Ok gotcha. I always wondered about that. Any particular reason not to keep using sprintf though?

  10. #10
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,148
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

  11. #11
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    Of course, I know what a storedproc is. I've been using them since the mid 90's with MsSQL server. But those are designed and compiled server side. Without having used PDO, I wasn't sure if there was any communication with the server prior to setting param values, or if it was all done client side. I'd still like to know if there are any real drawbacks to using sprintf. Nothing user-supplied is ever used in a query, and my data access needs are very light (storing and fecthing serialized events, and a simple read model) so I really don't want to mess with more abstraction than I really need.

  12. #12
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,148
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    You wanted to know the advantages of prepared vs sprintf... that link gives you the benefits as bullet points.

  13. #13
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    Oh snap! That'll teach me to skim something at 1:40 in the morning. Thanks, that answerd my query perfectly.


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
  •