sql injections

ashley98860615

i took this code from php.net, is this any good for preventing sql injections or do i have to use more than this, i would pass all vars through it:

<?php
// Quote variable to make safe
function quote_smart($value) {
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value) || $value[0] == '0') {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}
?>

laserlight

If it is feasible, use prepared statements with say, the PDO extension.

Still, you might want to write it as:

function quote_smart($value) {
	if (get_magic_quotes_gpc()) {
		$value = stripslashes($value);
	}
	return mysql_real_escape_string($value);
}

ashley98860615

do i need to run this function on both inserts/updates/deletes and selects or just inserts or updates?

currently for inserts/updates i am using:

mysql_query("INSERT INTO something (title, content) VALUES ('".mysql_real_escape_string($title)."', '".mysql_real_escape_string($content)."'") or die ("Cannot insert: ".mysql_error());

laserlight

Only run prior to use in an SQL statement.

Sanitising what is retrieved from the database is another matter, and usually things like [man]htmlspecialchars/man is used instead.

ashley98860615

ok thanks.

when using the quote smart function it runs the function correctly but then when inserted into the database it doesn't add the backslashes to quotes ". Is this correct? (Although i thought this was the whole point of the function).

Jack_McSlay

yep. the idea of the backslash is to make mysql (or whatever db you're using) ignore any special meaning, os insert special chars (such as \n or \t)

so a "\"" will be understood by mysql as ", which will prevent injections.

for example, an SQL such as
(...) WHERE user="$user"
could be vulnerable to an injection like
(...) WHERE user="1" or "true"
and with such code you would be able to prevent this:
(...) WHERE user="1\" or \"true"

btw, yes, you should do with ANYTHING which will be input from the user, regardless of what kind of query you're running

ashley98860615

so why doesn't my function work??

dammit why isnt this stuff automatically run by php?

laserlight

yep. the idea of the backslash is to make mysql (or whatever db you're using) ignore any special meaning, os insert special chars (such as \n or \t)

Sorry, but the backslash for escaping is a non-standard feature of MySQL. Normally, one would double the single quote to escape it.

so a "\"" will be understood by mysql as ", which will prevent injections.

Double quotes should not be used as string delimters in SQL to begin with.

btw, yes, you should do with ANYTHING which will be input from the user, regardless of what kind of query you're running

No, one should not treat integer input as strings, but rather validate with say, [man]ctype_digit/man.

so why doesn't my function work??

How does it not work?

dammit why isnt this stuff automatically run by php?

Because PHP wouldnt know what exact you want to escape.

ashley98860615

:eek:

it doesn't appear to add backslashes to qoutes -> " it simply add them as a single ". i thought the whole idea was to add backslashes?

laserlight

it doesn't appear to add backslashes to qoutes -> " it simply add them as a single ".

You are mistaken. My guess is that you retrieved the data from the database, saw that it was in unescaped form, and jumped to the conclusion that it wasnt escaped to begin with. That is not so, just like:

<?php echo 'ashley\'s world'; ?>

prints "ashley's world", not "ashley\'s world".

i thought the whole idea was to add backslashes?

The whole idea is to escape certain characters that may allow SQL injection.

ashley98860615

mm but if i click the 'edit' button in phpmyadmin the slashes don't show, so what should be:

quoted \"text\"

s simply: quoted "text"

im testing on a localmachine, would this be the reason? sorry this is dragging on...... and on...

laserlight

mm but if i click the 'edit' button in phpmyadmin the slashes don't show, so what should be:

Stop testing from the database. Test with a PHP script.

<?php
function quote_smart($value) {
    if (get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }
    return mysql_real_escape_string($value);
}

$value = "ashley's world";
echo "Original value: " . $value . "<br />\n";
echo "Escaped value: " . quote_smart($value);
?>

im testing on a localmachine, would this be the reason? sorry this is dragging on...... and on...

No, that is not the reason. The reason is that you are testing from phpmyadmin, which itself has been designed to show you the SQL and data as it is in the database, not as it is being sent to the database.

EDIT:
sorry, my test script wont work out of the box. You have to connect to the database first.

ashley98860615

ok i think i have it now, thanks for the help