www.webdeveloper.com
Results 1 to 9 of 9

Thread: mysql_real_escape_string - code

  1. #1
    Join Date
    Sep 2010
    Posts
    36

    mysql_real_escape_string - code

    Hi there,

    I wonder if someone could check my code for inserting contact details into MySQL database on my website. It's the security/validation that I need checking as I know the insert code works:

    PHP Code:
    <?php
    $con 
    mysql_connect("XXX.mysql","XXX","XXX");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }

    mysql_select_db("XXX"$con);

    $number mysql_real_escape_string(preg_replace('/[^0-9]/'''$_POST['number']));

    $realname mysql_real_escape_string($_POST['realname']);

    $sql="INSERT INTO Enquiries (Name, Number, Date)
    VALUES
    ('
    $realname','$number', CURDATE())";

    if (!
    mysql_query($sql,$con))
      {
      die(
    'Error: ' mysql_error());
      }
    echo 
    "";

    mysql_close($con)
    ?>
    Thanks very much in advance for any input, it is much appreciated!

    Kind regards, Mike

  2. #2
    Join Date
    Mar 2010
    Posts
    672
    PHP Code:
    $number mysql_real_escape_string(preg_replace('/[^0-9]/'''$_POST['number'])); 
    Interestingly enough this would be a case of over-sanitation. I personally wouldn't bother with the mysql_real_escape here as you're already defining a whitelist regular expression that will definitely remove any characters but 0-9. It doesn't hurt at all, but it also is a bit moot.

    PHP Code:
    $realname mysql_real_escape_string($_POST['realname']); 
    Mysql_real_escape_string() doesn't always protect as well as it should (a few cases have popped up in the past, aka the multibyte vulnerability), so if you can, i'd recommend putting together a list of all the characters you know won't be included (or that you know you don't want to be included) and filter those out of the string before you escape it. Extra points if you do a whitelist method like you did above. You should also be aware that escaping is not the same as sanitation and if you ever plan on printing this name on screen you should then run it through htmlentities() so that it doesn't get treated as data. Thats more so to prevent xss rather than sql injection though.

  3. #3
    Join Date
    Sep 2010
    Posts
    36
    Hey Jarrod,

    Thank you very much for your post, I appreciate it a lot!

    I'll look into sanitising the $realname field as you pointed out. Is there a way to safely allow apostrophes as we do business with a number of Irish clients where names with apostrophes are common. Apostrophes are one of the main threats regarding sql injection aren't they?

    Thanks again for your help!

    Kind regards, Mike

  4. #4
    Join Date
    Mar 2010
    Posts
    672
    Yes, those are commonly called single quotes. Whether or not they're bad depends entirely on how you're quoting your data within your query. If you do:
    mysql_query("select * from blah where blah2='somedata' ");

    Then somedata containing an ' would indeed break out of that string enclosing and may be interpreted as data. However that should be a non-issue here as that is one, of the many things, mysql_real_escape_string() escapes.
    The sanitizing i was mentioning was many in reference to cross-site scripting (aka, xss). What if someone made their name:
    <script>alert('xss');</script>

    Technically, any bad characters would be escaped by mysql_real_escape_string... but all that does is let you safely store this malicious script, if you echo this value to the browser, maybe as part of a list of users for example, the script will then be interpreted as html code within your page and executed. For this you can protect yourself by:
    PHP Code:
    echo htmlentities($realname); 

  5. #5
    Join Date
    Sep 2010
    Posts
    36
    Ah right, I see!

    So for my confirmation page which displays their contact details after submitting, I would need to use htmlentities so as to make it safe, i.e not executable if someone writes some script!

    That's great, thank you once again for your help!

    Kind regards, Mike

  6. #6
    Join Date
    Mar 2010
    Posts
    672
    Yes, that is correct.

  7. #7
    Join Date
    Mar 2010
    Posts
    2,803
    With htmlentities, I think you'll need the second paramater to handle single and double quotes

    PHP Code:
    echo htmlentities($strENT_QUOTES); 

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    Quote Originally Posted by tirna View Post
    With htmlentities, I think you'll need the second paramater to handle single and double quotes

    PHP Code:
    echo htmlentities($strENT_QUOTES); 
    You'll also want to use the third parameter if outputting your page as UTF-8.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Mar 2010
    Posts
    2,803
    Quote Originally Posted by NogDog View Post
    You'll also want to use the third parameter if outputting your page as UTF-8.
    not only utf-8, but I would imagine any charset that is not the default used by htmlentities() since the 3rd paramater is optional.
    Last edited by tirna; 01-18-2011 at 07:10 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles