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.
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.
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?
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:
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: