Lampwrights Forum > Linux, Apache, MySQL, PHP > MySQL

Reply
 
Thread Tools
11-19-2010, 10:35 AM   #1
Jeff
Administrator
 
Jeff's Avatar
 
Join Date: Jul 2010
Posts: 402
Rep Power: 10
Jeff is getting browny points
Protecting Against MySQL Injections

I just had the not so fun task of auditing some PHP code that had a lot of vulnerabilities. These were all MySQL injection type attacks. I had found them mostly because of the MySQL errors they generated in our logs. If you are writing PHP scripts and you are using MySQL you must sanitize your data! Never assume nobody will know. It is easy to find.

Here is an example, this script will look up widgets of certain category ID:

http://www.somesite.com/somePHPscript.php?viewCategory=19

Well, a BAD way to do this:

Code:
"SELECT * FROM widgets WHERE category_id = " . $_REQUEST['viewCategory'];
What's wrong with that? Well, what if I call the URL like so:

Code:
http://www.somesite.com/somePHPscript.php?viewCategory=19%20union%20truncate%20widgets
Now that query would be:

Code:
SELECT * FROM widgets WHERE category_id = 19 union truncate widgets;
Your widgets tables my dear friend, is history.

How would you combat that? Here you go:

Code:
"SELECT * FROM widgets WHERE category_id = " . intval($_REQUEST['viewCategory']);
intval() converts whatever is passed to it to an integer. So regardless of what is "injected" into $_REQUEST['viewCategory'] it will be converted to an integer.

What if you are using text? Example:

Code:
"UPDATE users set name='$username'"
The mysql_real_escape_string() function will make that variable safe:

Code:
"UPDATE users set name='" . mysql_real_escape_string($username) . "'"
These two simple techniques will take care of 99% of injection attempts!
Jeff is offline   Reply With Quote

11-21-2010, 06:23 AM   #2
hadi
Junior Member
 
Join Date: Sep 2010
Posts: 3
Rep Power: 0
hadi is getting browny points
I'm using mysql_real_escape_string() to sanitize my user input. Jeff what is the difference if you using mysql_real_escape_string to sanitize your first example?
hadi is offline   Reply With Quote
11-21-2010, 10:14 AM   #3
Jeff
Administrator
 
Jeff's Avatar
 
Join Date: Jul 2010
Posts: 402
Rep Power: 10
Jeff is getting browny points
Quote:
Originally Posted by hadi View Post
I'm using mysql_real_escape_string() to sanitize my user input. Jeff what is the difference if you using mysql_real_escape_string to sanitize your first example?
They are both the same?
Jeff is offline   Reply With Quote
11-21-2010, 10:16 AM   #4
Jeff
Administrator
 
Jeff's Avatar
 
Join Date: Jul 2010
Posts: 402
Rep Power: 10
Jeff is getting browny points
Unless you mean what is the difference between mysql_real_escape_string and mysql_escape_string. The difference is, the mysql_escape_string is deprecated and also mysql_real_escape_string takes a connection handler and escapes the string according to the current character set.
Jeff is offline   Reply With Quote
11-21-2010, 11:50 AM   #5
hadi
Junior Member
 
Join Date: Sep 2010
Posts: 3
Rep Power: 0
hadi is getting browny points
Quote:
Originally Posted by Jeff View Post
They are both the same?
What I mean is the difference of function between intval() and mysql_real_escape_string(). Anyway quick search about intval() in google explain everything.

From what I learned mysql_real_escape_string() is good enough to sanitize ANY user input. I'm just worried that mysql_real_escape_string() function only works in certain condition.

Thanks
hadi is offline   Reply With Quote
11-22-2010, 07:39 AM   #6
Jeff
Administrator
 
Jeff's Avatar
 
Join Date: Jul 2010
Posts: 402
Rep Power: 10
Jeff is getting browny points
Quote:
Originally Posted by hadi View Post
What I mean is the difference of function between intval() and mysql_real_escape_string(). Anyway quick search about intval() in google explain everything.

From what I learned mysql_real_escape_string() is good enough to sanitize ANY user input. I'm just worried that mysql_real_escape_string() function only works in certain condition.

Thanks
intval() will convert any data to an integer. So for example:

Code:
$variable = "I am some injected text";
echo intval($variable);
The output of this would be:

0

So if you are expecting a variable to be an integer such as a timestamp, or an ID #, pass it through intval and no matter what, it will always come out as a number. It eliminates any chance that variable has been injected with any SQL code. If you just used mysql_real_escape_string() you could still cause an error if MySQL is expecting an integer. This does not help you however when you NEED text. Then you use mysql_real_escape_string().
Jeff is offline   Reply With Quote
06-22-2011, 06:59 AM   #7
JimmyJohn
Junior Member
 
Join Date: Jun 2011
Location: Midwest USA
Posts: 2
Rep Power: 0
JimmyJohn is getting browny points
I am dealing with this right now and am tightening up the holes in my scripting. I'm using PHP to drive it and am wondering what method to use for this project, MySQLi or PDO. It's a subscription site for online ads, and uses PayPal so no SSL is needed on our end, but still important to protect the data of course! Would PDO be better suited or shall I just work out 100% sanitation methods?
JimmyJohn is offline   Reply With Quote
06-22-2011, 07:54 AM   #8
Jeff
Administrator
 
Jeff's Avatar
 
Join Date: Jul 2010
Posts: 402
Rep Power: 10
Jeff is getting browny points
You can eliminate most mysql injections with MySQLi's prepare method.
Jeff is offline   Reply With Quote
01-18-2024, 04:48 PM   #9
SienalHep
Junior Member
 
Join Date: Jan 2024
Posts: 1
Rep Power: 0
SienalHep is getting browny points
For a subscription site dealing with online ads and PayPal, would you recommend using MySQLi's prepare method or PDO for protecting against SQL injection, and why?
SienalHep is offline   Reply With Quote
Reply

Tags

mysql best practices

,

mysql security


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT -4. The time now is 04:13 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2024, vBulletin Solutions, Inc.