Results 1 to 7 of 7

Thread: Best Practices to Prevent SQL Injection

  1. #1
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    620
    Thanks
    274
    Thanked 14 Times in 14 Posts

    Default Best Practices to Prevent SQL Injection

    I would like to know the definitive way to guard against sql injection attacks so that I can incorporate these protections into my code, but I am a bit unsure as to which are the best commands to use. Do you have to use them all, or is one sufficient? I've never had a complete grasp of what "escaping" means, so could someone please illustrate the best way to safely add data to a database? I've seen things like below, which would imply that you could thereafter refer to the variable as $var, but I have found this not to be the case:

    Code:
    $var = mysql_real_escape_string($_POST['var']);
    $var = htmlspecialchars($_POST['var']);
    Do you add data like this?...

    Code:
    INSERT INTO location ( location, address, city)
    VALUES (	'" .$_POST['location']."', '".$_POST['address']."', '".$_POST['city']."' )";
    or like this?...

    Code:
    $location = mysql_real_escape_string($_POST['location']);
    $address = htmlspecialchars($_POST['address']);
    $city = mysql_real_escape_string($_POST['city']);
    
    VALUES (	'" .$location."', '".$address."', '".$city."' )";
    or what???

    Thanks.

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    mysql_real_escape_string() is a function that escapes the characters so they cannot affect the syntax of a mysql statement.

    That is all you need for security.

    However, you might want to use html_entities() also, if you want, for example, to later output it as just text on an html page and not have any of the html affected.

    Escaping is a general term for "escaping" (deactivating, removing affect of) characters that would work in a string to cause problems.

    The simplest example is:
    echo 'You can't do this';
    echo 'Now, you aren\'t going to have problems.';

    So, escaping works differently for every language. In mysql it is especially dangerous, because the syntax of mysql is not written directly, but rather AS A STRING. That's why you need to be careful with it. If you use eval() in php, which runs a string like php code, then you need to be just as careful.

    Anyway, for mysql, mysql_real_escape_string() will be all you need.

    For anything else, you'll need another method specific to that.



    Your second method, escaping first, is MUCH safer.

    The other method would allow someone to send, as post data, this:
    something; delete database 'info';

    And your data would all be gone. (The first semicolon ends the first statement, whatever you were doing in your sql statement, and the second deletes the database.)

    mysql_real_escape_string() will convert any problematic characters to just strings. In this case, you'd have the semicolons and apostrophes fixed.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. #3
    Join Date
    Jul 2008
    Posts
    199
    Thanks
    6
    Thanked 58 Times in 57 Posts

    Default

    Quote Originally Posted by djr33 View Post
    something; delete database 'info';
    mysql_query() only lets you do one query at a time, so that would be disabled by default.

    mysqli:: prepare()
    should do you good. Make sure you read up on it though.

  4. #4
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    http://www.php.net/manual/en/functio...ape-string.php

    There are some examples on the page, then.

    In the case of mysql_query, it seems like that limitation does provide some level of security, but it's certainly vulnerable, if a bit more work to attack.

    I'm not sure about your suggestion of mysqli::_prepare()-- it's for mysqli, not mysql, which may not be compatible with the script in question. I'm not sure if it requires mysqli or will escape any sql, though it looks like it requires a mysqli conn to work, anyway.

    However, assuming it works, what is the advantage over mysql_real_escape_string()?
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  5. The Following User Says Thank You to djr33 For This Useful Post:

    kuau (09-10-2008)

  6. #5
    Join Date
    Aug 2007
    Location
    Ohio
    Posts
    79
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Default

    If you need MySQL instead of mysqli, I use the PDO class with a MySQL driver, and then used prepared statements.

    As far as I'm aware, there's no possible way for an SQL injection attack when you use prepared statements. The idea is that you tell the MySQL engine ahead of time what the query you want to execute is, except without any values that you're receiving from the user. Then when you execute the query, you pass along the values and they're inserted in the query you already prepared. There's no way to meddle with the query because the MySQL engine already knows what the query is.

    PDO Extension
    PDO:: prepare

  7. The Following User Says Thank You to jackbenimble4 For This Useful Post:

    kuau (09-10-2008)

  8. #6
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    620
    Thanks
    274
    Thanked 14 Times in 14 Posts

    Default

    Thank you so much for the thorough explanation and links. I was able to follow the mysql_real_escape_string() part but I have to admit that the PDO:: prepare really lost me. I need to do a lot of background reading before I can tackle that method.

    Do you have to use mysql_real_escape_string() on date and integer values as well, or just strings?

    Thanks, e

  9. #7
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Depends where you get them from and what format they're in (in the case of the dates). If you're getting them from the user, then some validation has to be done — but checking isnumeric() will work just as well if the input is meant to be a number. The date is more complex: perhaps try converting it using strtotime() (which will return false if it fails due to an invalid date).
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  10. The Following User Says Thank You to Twey For This Useful Post:

    kuau (12-09-2008)

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
  •