homepage Welcome to WebmasterWorld Guest from 54.227.12.4
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Sanitizing input in PHP/MySQL
Preventing malicious injections
GoNC




msg:4678116
 3:36 am on Jun 7, 2014 (gmt 0)

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",
mysql_real_escape_string($_GET['a']),
mysql_real_escape_string($_GET['b']),
mysql_real_escape_string($_GET['c']),
mysql_real_escape_string($_GET['d']));

$sth = mysql_query($p_query) or die(mysql_error());

But someone was still able to successfully run a query like this:

?a=whatever
&b=whatever
&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--
&d=1

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]

 

bhukkel




msg:4678133
 7:24 am on Jun 7, 2014 (gmt 0)

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.

GoNC




msg:4678139
 8:08 am on Jun 7, 2014 (gmt 0)

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.

Anyway.

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?

matrix_jan




msg:4678200
 3:26 pm on Jun 7, 2014 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved