mysql_real_escape_string - code

[sss_1234]

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
$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

[Jarrod1937]

$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.

$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.

[sss_1234]

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

[Jarrod1937]

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:

echo htmlentities($realname); 

[sss_1234]

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

[Jarrod1937]

Yes, that is correct.

[tirna]

With htmlentities, I think you'll need the second paramater to handle single and double quotes

echo htmlentities($str, ENT_QUOTES); 

[NogDog]

With htmlentities, I think you'll need the second paramater to handle single and double quotes

echo htmlentities($str, ENT_QUOTES); 

You'll also want to use the third parameter if outputting your page as UTF-8.

[tirna]

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.