|Sanitizing input in PHP/MySQL|
Preventing malicious injections
I've been using mysql_real_escape_string() to prevent injections, but I learned today that it didn't work in all case. This is the query I'm using in PHP:
$p_query = sprintf("SELECT * FROM tbl WHERE colA = '%s' AND colB = '%s' AND colC >= %s AND colC <= %s ORDER BY updated DESC",
$sth = mysql_query($p_query) or die(mysql_error());
But someone was still able to successfully run a query like this:
&c=999999.9+union+all+select+%28select+concat%280x27%2C0x7e%2Cusers.%2C0x27%2C0x7e %29+from+%db_name%60.users+Order+by+username+limit+2005%2C1%29+ %2C0x31303235343830303536%2C0x31303235343830303536%2C0x31303235343830303536 %2C0x31303235343830303536%2C0x31303235343830303536%2C0x31303235343830303536 %2C0x31303235343830303536%2C0x31303235343830303536%2C0x31303235343830303536--
Luckily, the data is all encrypted on my end, so all they saw was gibberish. But I thought that mysql_real_escape_string() was supposed to prevent that?
I've modified this script to ensure that a and b are approved strings and that c and d are integers, but I have other scripts that accept raw user-inputted data. What else should I be doing to prevent SQL injections like this?
[edited by: incrediBILL at 1:22 am (utc) on Jun 9, 2014]
[edit reason] fixed scrolling problem [/edit]
I think it is a good practice to always check your external data. This can be a form, CSV import, API results etc. Not only if it is a valid string or integer but even more strictly. For example if i expect a date i check if it is a valid date string, if i expect an url i check if it is a valid url.
Furthermore the old mysql is deprecated as of php 5.5.0. It is better to move to mysqli or pdo. Here you can use prepared statements, this is better protection for mysql injection.
Sorry it stretched out like that; I didn't realize it until bhukkel replied, and now it's too late to edit. Hopefully a mod can fix that for us.
Yeah, I realize that the style is deprecated. Which really blows, because it means I basically have to rebuild my entire site, which is working fine. It's on the long list of things-to-do for the next site upgrade.
The question here, though, is how to check the data when I'm expecting a long user-submitted string, like a message board post?
You can also check with your hosting company if they can put some filter on requests. For example on my websites anything that looks like a query, such as "select from", doesn't reach the server, it gets redirected. I didn't do anything, so I assume it's on their side. I can't even search for the phrase "select from" on those websites using the custom search. Good luck.