sql injection

[ayushchd]

Is it enough to use mysql_escape_string to safeguard from sql injection attacks?

[whoisgregg]

Mostly yes. You should also be aware of other "best practices" detailed in the PHP manual [us.php.net].

[henry0]

This is deprecated
you should use
mysql_real_escape_string

to use such function you need to verify that
get_magic_quotes_gpc is set to off
other wise
you will use stripslashes

(Gregg did not catch that one!) :)

[eelixduppy]

This is deprecated
you should use
mysql_real_escape_string

It's not that it is depreciated. mysql_real_escape_string should be used, though, because it takes into account the current character set of the mysql table.

[whoisgregg]

Good catch henry0. :) Yeah, I completely missed that one. Definitely use mysql_real_escape_string [php.net].

mysql_escape_string [php.net] became deprecated after PHP v4.3.0 according to the manual page.

[eelixduppy]

hehe - oops. Looks like it does say it is depreciated in the manual. :)

[ayushchd]

Thanks to all.

Am I right If I say that for numerical values I must use mysql_real_escape_string and for string, I must use addslashes?

When shud I use stripslashes?

What is the difference between addslashes and mysql_real_escape_string?

It would be really kind if someone could draw out a small example for me combining the use of addslashes, stripslashed and mysql_real_escape_string

Thanks In Advance.

[eelixduppy]

Here's a nice example:

if([url=http://us3.php.net/get_magic_quotes_gpc]get_magic_quotes_gpc[/url]()) {
$_POST = [url=http://www.php.net/array-map]array_map[/url]('stripslashes',$_POST);
}
$_POST = array_map('mysql_real_escape_string',$_POST);

addslashes should not be used for escaping variable within a query.

[ayushchd]

that means for escaping a variable within a query, we must use either stripslashes or mysql_real_escape_string..

But I dont see any change being made by stripslashes.

Can these be used with both string and numbers?

When do we use stripslashes and when do we use mysql_real_escape_string?

[eelixduppy]

no, you would only use mysql_real_escape_string. stripslashes removes the slashes added by addslashes, mysql_real_escape_string, etc..

The code that I wrote above checks to see if magic quotes is enabled, and if it is that would mean that it already added slashes to the POSTed string. Because of this, we remove those slashes using stripslashes, and then add slashes with mysql_real_escape_string instead.

So you only use mysql_real_escape_string for query variables - numbers or string, it doesn't matter.

[ayushchd]

That was nice and simple.

Thanks a lot.

Now, when we enter :
$a = "ABCD's";
$a = mysql_real_escape_string($a);
echo $a;

This would return ABCD\'s

If I want to insert this into the db, will it go as

ABCD\'s or ABCS's

And also I wanted to ask if I had to make further filtration to prevent from injection attacks?

[borntobeweb]

For numeric values, you need to validate all input to make sure they have no non-numeric characters in them (especially semi-colons). One lazy way is to run the value through intval(), so something like

"select * from table where id = ".intval($_POST['id'])

But it's best to validate all input first. You also want to check the length of strings to make sure they're not longer than the declared size of your fields in the database. That doesn't create a security problem but prevents ugly error messages.

[FriskUK]

Does anyone know of a functionized version of this... a safe function that cleans a string ready for mysql

I usually use adodb for my sql so my projects are compatable with different sql servers, and because i'm not using php's mysql commands directly, i can't use mysql_escape_string because it requires a connection.

[vincevincevince]

i can't use mysql_escape_string because it requires a connection

mysql_escape_string doesn't require a connection, but mysql_real_escape_string does (in order to determine the appropriate character sets)

[FriskUK]

ok since mysql_escape_string string is depreciated, so probably best not to use it since it might not be around soon, is there another alternative to cleaning a string without a connection?

[vincevincevince]

is there another alternative to cleaning a string without a connection?

It is impossible to clean a string without knowing the target character encoding. mysql_real_escape_string() works by finding the character encoding from the link. You could theoretically write you own mysql_real_escape_string() version which uses character encoding as an argument instead of deriving it from the link...

[FriskUK]

thanks, ill have to write one then :)

[henry0]

ayushchd
asked a very good question:
<<<
This would return ABCD\'s

If I want to insert this into the db, will it go as

ABCD\'s or ABCS's
>>>

Instead of a straight forward answer
I would like you to do the following:
use the function to escape and insert aaaa's and "aaaaa"
Then open phpMyyAdmin look at the result
next do a phpMyAdmin "view source" and search for those values
Last look at the browser output and again do a view source

last try doing the same insert without escaping

Keep us posted

[ayushchd]

Hi.

Thanks for the reply. Just read as I was in school.

I am really running short of time as I have my exams on head. I shall post a reply on this topic as soon as I get time.

And as it is, I am currently working on parsing.

Thanks.
Do wait.

[ayushchd]

Am really bad with functions.

Is this enough to safeguard from sql injection attacks?

function sqlclean($a) {
if (get_magic_quotes_gpc()) {
stripslashes($a);
}
$a = mysql_real_escape_string($a);
}

Do I have to add
return ($a) also?

Or this is good enough?

[henry0]

function sqlclean($a) {
if (get_magic_quotes_gpc()) {
stripslashes($a);
}
$a = mysql_real_escape_string($a);
}

Do I have to add
return ($a) also?

Or this is good enough?

as per accepted conventions when noming a function
you may use cap letters in a fashion like to:
SqlClean
A function does not work just because it's there
so you need to get something out of it

RETURN [de3.php.net]

function SqlClean($my_var)
{
if (get_magic_quotes_gpc())
{
stripslashes($my_var);
}
if(!is_numeric($my_var)
{
$my_var = mysql_real_escape_string($my_var);
}
return $my_var
}

using it

$my_test=SqlClean($my_test);
$foo=SqlClean($foo);
etc....

[ayushchd]

If I use this, am I safeguarded from sql injection attacks?

[henry0]

IF you FIRST FILTER any input and verify that that user's input is the expected one
AND use mysql_real_escape_string
then you are good to go.

among the many filters one that should not be disregarded is strlen() check if the data are within the acceptable spaces and characters corresponding to the table field
for ex: in DB, a name could be varchar 30
then do
if(strlen($name)>30)
{
echo"aaaaa";
exit();
}

[ayushchd]

Hi.

I am filtering almost every input.

But there are some I can't like the users' name, and information about the user, city name, etc.

What should I do in that case?

And also for restriction of character length,

I have used :

<input......maxlength='25'>

[henry0]

Your lenght check is fine at form level but the point is if for some reason the form values are tinted
or somehow bypassed then your PHP script will be the last bastion.

Question:
How do you get those username and other values? session? something else?

[ayushchd]

When registering, $_POST

After logging in, $_COOKIE

btw, how is it possible for the user to bypass the field if i am already restricting its length?

[henry0]

All input you are dealing with
coming from a user or coming from another page needs to be screened
this goes also for $_POST, S_SESSION $_etc....

The above thread is only the tip of the iceberg
Next read about SESSION fixation and hijacking

[ayushchd]

I have safeguarded myself against session hijacking, session fixation, and XSS attck using php class.

I am still unsure about sql injection attack.

How can a field be bypassed if i m restricting its length using maxlength?

What if I pass a string through mysql_real_escape_string without filtering it?

For eg, there is a field in which the user can type anything about himself. I cant filter it. So will passing it through mysql_real_escape_string, safeguard me?

[ayushchd]

What's wrong with this function now?

function SqlClean($my_var)
{
if (get_magic_quotes_gpc())
{
if (is_array($my_var)) {
foreach ($my_var as $key) {
stripslashes($key);
}
} else {
stripslashes($my_var);
}
}
if (is_array($my_var)) {
foreach ($my_var as $key) {
$key = mysql_real_escape_string($key);
return $key;
}
} else {
$my_var = mysql_real_escape_string($my_var);
}
return $my_var;
}

[henry0]

For eg, there is a field in which the user can type anything about himself. I cant filter it. So will passing it through mysql_real_escape_string, safeguard me?

I deal with those by using a class:
the very first thing it does is cleaning MS word mess
(think about it many will simple copy and paste from MS world)
then I run another regex to look for ¦ (WebmasterWorld breaks it)
and { } and [ ] plus... up to you
Next another one looks for email or web address (that I disallowed)

Last it checks for vulgar/bad words.

However even without going to my extreme you ought to keep using the mysql escape function