Page 1 of 2
Mysql_real_escape_string question!
Posted: Sat Aug 06, 2005 11:56 am
by influx
I have posted a few threads on this forum relating to a huge loss of data at my website. It was a code flaw, and it was my fault, but a good wakeup call nonetheless.
I have been looking through/posting my code to get tips and opinions on it. One thing that everybody has mentioned I need to look at is preventing
SQL Injections.
When somebody fills out a form on my site, for example, depending on what they want to do (i.e:
remove post, add post, remove all posts) I assign a code (i.e:
code 1, code 2, code 3) and I propagate this in the URL as c=1, 2, 3. Then I handle it using the $_GET command when coding the function
I have been made aware that doing this would make my site EXTREMELY vulnerable to SQL injections. Now my question is this:
Code: Select all
$code=mysql_real_escape_string($_GET['code']);
$_SESSION['username']=mysql_real_escape_string($_SESSION['username']);
$post_id=mysql_real_escape_string($post_id);
//etc.
//etc.
//////////////////////////////check if post_id/code is in range for preventing sql injection
if(!($post_id==1 || $post_id==2 || $post_id==3 || $post_id==4 || $post_id==5 || $code==1 || $code==2))
{
die('Invalid file ID/code. Please contact us if this problem persists.');
}
//////////////////////////////
//etc.
//etc.
//for example:
$query = "UPDATE posts SET post".$post_id."_name = '$post_name' WHERE username = '".$_SESSION['username']."'";
Would this thwart any sql injection possibilities?
I have gone through all my code and ANYTHING that passes through an SQL command/query I have run through the
mysql_real_escape_string() command. Could this hurt me at all? Is this a good or bad idea?
Thanks.
-influx
Posted: Sat Aug 06, 2005 12:00 pm
by s.dot
It can't hurt you when you use it where you don't need it.
As a tip, when you're calling the data back out of the database use stripslashes($data);
If you don\'t you\'ll have slashes before all quote\'s -- single or double.. like this sentence
You could keep the slashes if you want, it just looks ugly
Your code looks pretty SQL injection safe. If you want to be even more secure about your code, you could call htmlentities() on the data too... I'm not sure if that would make it
more secure.. but it wouldn't hurt.
Posted: Sat Aug 06, 2005 12:17 pm
by andre_c
err... you don't need to use stripslashes
doing mysql_escape_string() or addslashes() only escapes the string so it doesn't cause problems with executing the query, the slashes don't get stored on the database...
... unless you have magic_quotes_gpc on (which i would recommend to have it off), in that case mysql_escape_string() would add extra slashes that would get entered to the database
Posted: Sat Aug 06, 2005 12:44 pm
by influx
Hrm, in phpinfo it shows that magic_quotes_gpc is On. If I turn it off it could change a lot on my website, couldn't it? What exactly does it do...I read the php manual about it but got a rough idea of what it does. Could you explain it in lamens terms for me?
So if magic_quotes_gpc are enabled, is mysql_real_escape_string() useless on my website? What if I propagate an e-mail address through the URL, I won't have to addslashes to it, will I? It's only for text with ' and "
Posted: Sat Aug 06, 2005 12:51 pm
by andre_c
here's a good article about magic_quotes
http://www.webmasterstop.com/63.html
they are evil
i would recommend turning them off, and always use mysql_real_escape_string() and never use stripslashes (you shouldn't need to)
if you use mysql_real_escape_string() with magic_quotes on, it will only add slashes to the slashes already there, sending extra slashes that get entered into the database, forcing you to use stripslashes after you get the string back from the database
you only need to addslashes when inside of quotes, and you only need to escape the quotes that are the same as the containing quotes
Posted: Sat Aug 06, 2005 1:01 pm
by josh
influx wrote: What if I propagate an e-mail address through the URL, I won't have to addslashes to it, will I? It's only for text with ' and "
Are you passing the email through regexp? How good is you regexp?
If your regex looks something like:
You will need to escape it with mysql_real_escape_string, yes.
If your regex looks something like:
Code: Select all
^[A-Za-z0-9](([_\.\-]?[a-zA-Z0-9]+)*)@([A-Za-z0-9]+)(([\.\-]?[a-zA-Z0-9]+)*)\.([A-Za-z]{2,})$
you know your string does not have anything that needs escaping, given that it matched the regex.
Still it's always a good idea to use mysql_real_escape_string() on text inputted from the user, the only time I won't use it is if I'm dealing with an integer or something, with an email address its easy to typo a regex and let a quote by.
Posted: Sat Aug 06, 2005 1:04 pm
by s.dot
if you're grabbing an email address through the URL, you should use mysql_real_escape_string()
Use it on anything input by the client user.
Posted: Sat Aug 06, 2005 1:15 pm
by andre_c
i second that.
use it on ANYTHING that's coming from the user and going on a query
Posted: Sat Aug 06, 2005 3:17 pm
by josh
Heh, I had the same debate in another thread, show me a string that passes that second regex and can still exploit a query and I'll admit I'm wrong. It's all a matter of opinion, if you want the extra security and peace of mind go ahead and escape ALL data, I never said it was wrong to escape data, just not entirely necessary if you're careful. Besides if you took the time to read my whole post you'd see the only time I personally don't escape data is on an integer, I pointed out how easy it is to typo a regex, all I was trying to do was state that 'technically' escaping something that has proven to have nothing to be escaped, does nothing in result.
Posted: Sat Aug 06, 2005 5:43 pm
by influx
andre_c wrote:i second that.
use it on ANYTHING that's coming from the user and going on a query
IF AND ONLY IF magic_quotes_gpc is set to
off in php.ini...right?
If magic_quotes_gpc is set to on and I mysql_real_escape_string everything coming through, will it add slashes twice? Or will it know that it has already added slashes
Posted: Sat Aug 06, 2005 5:54 pm
by s.dot
I believe it will only add slashes once. Either way, if you have slashes, stripslashes() will usually remove them.
I believe i have it ON and I use mysql_real_escape_string() on all data. Then when I need it back out of the db stripslashes() takes care of it.
Posted: Sat Aug 06, 2005 6:28 pm
by andre_c
influx wrote:andre_c wrote:i second that.
use it on ANYTHING that's coming from the user and going on a query
IF AND ONLY IF magic_quotes_gpc is set to
off in php.ini...right?
If magic_quotes_gpc is set to on and I mysql_real_escape_string everything coming through, will it add slashes twice? Or will it know that it has already added slashes
if magic_quotes are on and you do mysql_real_escape_string(), it WILL add the slashes twice.
if magic_quotes_gpc is on, all strings coming from the user will already have slashes (as if someone had done addslashes() on them), and if you use mysql_real_escape_string() on them it will add a second set of slashes. The behavior of it is to escape slashes with a slash, so wherever it sees a slash it will add another one. The extra slashes will go into the database, forcing you to use striplashes() when you get the strings back out.
i consider magic_quotes a bad thing since it escapes strings even if you're not planning to use them in a query, forcing you to stripslashes() when you don't need to.
If you want to, you can keep it magic_quotes on and (as scrotaye said) stripslashes() everything you get out of the database.
Definitely not what i would recommend, but it will still work.
personally, i would turn off magic_quotes, always use mysql_real_escape_string(), and never use stripslashes()
Posted: Sat Aug 06, 2005 6:50 pm
by s.dot
final judgement: use mysql_real_escape_string() on all data put in by the user, regardless if your magic quotes are on or off.
That is the safest way to do it, and at the most, the only inconvenience you'll have is having to add stripslashes() when you bring it out of the database. But I'd say that's much better than an SQL injection. =)
Posted: Sat Aug 06, 2005 7:02 pm
by josh
scrotaye wrote:final judgement: use mysql_real_escape_string() on all data put in by the user, regardless if your magic quotes are on or off.
That is the safest way to do it, and at the most, the only inconvenience you'll have is having to add stripslashes() when you bring it out of the database. But I'd say that's much better than an SQL injection. =)
Or surround the mysql_real_escape_string with a conditional. Escape if magic quotes is off, this way your application is portable and secure. I usually just use ini_set to make sure my application is portable, (set the ini settings exactly how I want them for each time a script is run)
Posted: Sat Aug 06, 2005 7:11 pm
by andre_c
from
http://php.net/mysql_real_escape_string
Note: If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.
i trust mysql_real_escape_string more than i trust addslashes, so i would just turn magic_quotes off
mysql_real_escape_string() is a function that calls an actual mysql library function, so it's pretty much guaranteed to stop sql injection. I'm not as confident about addslashes() or magic_quotes