Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    2

    Importance of preventing SQL injections


    Hello friends.

    I have been hearing a lot about the importance of using mysqli_real_escape_string() to prevent SQL injections.

    When should I use that command?

    How likely are SQL injections to occur on a new website?

    Thanks in advance.
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,296
    Rep Power
    9400
    Every time you don't use prepared statements and have to put unknown user-submitted data into a query, and very likely.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    actually, you should always use prepared statements. Manual escaping is risky, because there are a lot of mistakes you can make: You might forget the quotes, use the wrong character encoding, use a dangerous context. Prepared statements are much more foolproof.



    Originally Posted by phpnewbie34
    How likely are SQL injections to occur on a new website?
    That obviously depends on you the programmer. In a properly written application, there's mustn't be any SQL injections. If there are, it means you haven't done your job.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,296
    Rep Power
    9400
    Originally Posted by Jacques1
    actually, you should always use prepared statements. Manual escaping is risky, because there are a lot of mistakes you can make: You might forget the quotes, use the wrong character encoding, use a dangerous context. Prepared statements are much more foolproof.
    I, for one, avoid blanket terms like "always" because one should not, in fact, always use prepared statements. It's quite possible to do some regular statements correctly and without any complicated efforts.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    And I like to keep things simple and not overcomplicate them by enumerating all possible exceptions and cases that are debateable. A superfluous prepared statement doesn't hurt. A botched raw query does hurt.

    The great thing about those simplifications is that they're self-correcting: As soon as somebody understands prepared statements, they know themselves when to make an exceptions from the rule -- without any need for our permission.

    Are you also against simplified rules like "Don't kill"? Would you rather want parents to hand their children 50 pages of legal texts including the latest academic discussion about whether or not the death penalty is legitimate?

    Of course you don't need prepared statements for constant queries, because those have no input you could pass to the statement. But for any dynamic query with external input, I strongly suggest using a prepared statement -- unless you really, truly know what you're doing and have a very good reason for falling back to manual escaping.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,296
    Rep Power
    9400
    There's a difference between saying "don't kill" and "never kill anyone". There's a difference between asking what you should do during a home invasion and whether killing the intruder is allowed. There's a difference between telling a child that murder is bad and telling them that sometimes there are exceptions.

    If someone comes here specifically asking when they should use mysql(i)_real_escape_string() they're not looking for the blanket "use prepared statements" answers. They're looking for when they should use that function. You're free to tell them to never, ever use it and that they should be using prepared statements instead, but I'm free to tell them that sometimes you don't need prepared statements because some situations are simple enough to do manually.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    2

    Re:


    Is it better to use prepared statements using Mysqli or with PDO?

    Which one better protects against SQL injections?
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    There's no difference if you turn off "emulated" prepared statements in PDO:

    PHP Code:
     $db_options = array( 
        
    PDO::ATTR_EMULATE_PREPARES => false                     // important! use actual prepared statements (default: emulate prepared statements) 
        
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION           // throw exceptions on errors (default: stay silent) 
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC      // fetch associative arrays (default: mixed arrays) 
    ); 
    $database = new PDO('mysql:host=localhost;dbname=YOURDB;charset=utf8''YOURUSER''YOURPW'$db_options);    // important! specify the character encoding in the DSN string, don't use SET NAMES 
    However, PDO is more user-friendly, and it has one gigantic advantage: You're not bound to MySQL. Should you ever decide to switch to a real database system like PostgreSQL, Oracle or whatever, you don't have to rewrite all your code.

    So I generally recommend PDO.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,296
    Rep Power
    9400
    Originally Posted by Jacques1
    However, PDO is more user-friendly, and it has one gigantic advantage: You're not bound to MySQL. Should you ever decide to switch to a real database system like PostgreSQL, Oracle or whatever, you don't have to rewrite all your code.
    ...with one major gotcha: MySQL has some differences from standard SQL. Probably one of the largest and most commonly discovered differences is that it allows you to include fields in the SELECT that aren't listed in a GROUP BY.
    Code:
    SELECT id, name, value, COUNT(1) FROM table GROUP BY id
    MySQL allows it while others like MS SQL do not. (Honestly, it really should be allowed when you're grouping by a unique field and the extra fields are from the same table.) There's also LIMIT x,y while MS SQL has just TOP y (and only if x=0).
    So if you switch you may have to rework some SQL (and possibly some code) but that's still much less work than it would be to switch from mysqli to sqlsrv.
  18. #10
  19. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by Jacques1
    Hi,

    actually, you should always use prepared statements. Manual escaping is risky, because there are a lot of mistakes you can make: You might forget the quotes, use the wrong character encoding, use a dangerous context. Prepared statements are much more foolproof.
    Are you categorizing using mysqli_real_escape_string() as "manual escaping"? Are there instances where the prepared statement would escape differently than that?

    10 years with PHP and I've never used a single prepared statement, but that's made me disciplined in using mysqli_real_escape_string() (or a derivative) where needed.
  20. #11
  21. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,296
    Rep Power
    9400
    Prepared statements work completely differently than queries with escaped data.

    With what you're used to you send the one query to the server and that's it. It's all one big string, most of it has some syntax like "SELECT" and "WHERE" and whatever, and some of it is the actual data you're using. The point of escaping is so that the line between the two doesn't blue - that the data is never accidentally treated as syntax.

    Prepared statements work by sending two things. The first is the statement syntax itself. No data whatsoever. Then MySQL (or whatever database engine) can just look at the structure and you don't have to worry about that being messed up because of SQL injection. The second thing sent is the data itself. No syntax whatsoever. They're separate so there's no risk of mixing the two together, and thus no need to try to escape anything. In fact if you try to escape data all you're doing is adding in slashes, and when you try to get the data back out the slashes will still be there.

    The main purpose of prepared statements is reusability. If you want to execute a statement normally, by sending syntax and data at once, MySQL has to figure out how to process the statement every single time you send it. Over and over, even if it's the same statement with different data.
    Since prepared statements send the syntax part by itself, MySQL can decide ahead of time what to do (what tables need updating, how best to get data, etc) and is ready to execute when you send the data. If you then send more sets of data, all the hard work of finding out what to do has already happened and it can just repeat the same steps with the new data.
  22. #12
  23. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by requinix
    The main purpose of prepared statements is reusability. If you want to execute a statement normally, by sending syntax and data at once, MySQL has to figure out how to process the statement every single time you send it. Over and over, even if it's the same statement with different data
    Ah. Hmm, well... there are very few times I repeat a query multiple times--usually only if I'm inserting thousands of records and am capping the per-query value sets at 200 each or something. Otherwise if I'm repeating a query in quick succession I'm probably doing something wrong.

    Plus my queries tend to be highly dynamic with each section of the string being built a piece at a time. While that doesn't preclude putting the resulting syntax and values into a prepared statement separately, it's more to track than just a growing string.

    And actually my query building framework would need to be completely refactored. So I'll probably stick with normal statements for now.

    Good info, though. I didn't realize the communication with the server was actually handled differently behind the scenes. I figured it was just an interface to PHP sticking the values in and building the final query string, itself.
  24. #13
  25. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,296
    Rep Power
    9400
    Give it a second thought. It may take time extra time now but imagine how much more time you'll have to spend fixing any SQL injection problems with your code. And then fixing whatever damage was done because of the exploit.
    Even if it will take you some time you should still switch to prepared statements. They will protect your site for a minimal amount of effort.

    Originally Posted by dmittner
    I figured it was just an interface to PHP sticking the values in and building the final query string, itself.
    It can, if you enable the "emulated prepared statements" thing Jacques said to disable. But the emulated version isn't as good as the real thing.

    Comments on this post

    • Jacques1 agrees
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    I'm a bit disappointed that we still have to argue about prepared statements in the "PHP community"...

    Yes, in theory, you can escape all your data with mysqli_real_escape_string() etc. But it doesn't work in reality. I mean, just look at any vulnerability database or the version history of any major web application, and you'll find dozens of SQL injection vulnerabilities. And this is not some crap code written by a PHP newbie. It's professional software by professional developers who know about escaping. Yet still all those developers fail sooner or later.

    I think we should finally draw our conclusions from that: Manual escaping just doesn't work. It requires all developers to write perfect code and never ever make any mistake, but humans do make mistakes. Even the smartest and most experienced programmers screw up sometimes. And that's why people have invented better, more foolproof approaches like prepared statements.

    Of course you may not always be able to use them. If you have 1,000,000 lines of legacy code, nobody expects you to rewrite it all right now. But for new projects, do use prepared statements.

    It's the year 2013. PHP has prepared statements since almost a decade. I think it's time to finally use them and put an end to SQL injections.

    Using a query builder can be a fallback. But many home-made frameworks suffer from serious design flaws, which can void all security measures. For example, if you specifiy the character encoding in the wrong way (via SET NAMES) or not at all, you've already lost.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    40
    Rep Power
    2

    Re:


    Nice discussion here.

    Now an simpler question?

    What does this mean and how can I use it?
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo