Best way to prevent sql injection?

[duuudie]

So you're saying I should put all incoming vars through this:

function strip_magic_quotes($arr) 
{ 
    foreach ($arr as $k => $v) 
    { 
        if (is_array($v)) 
            { $arr[$k] = strip_magic_quotes($v); } 
        else 
            { $arr[$k] = stripslashes($v); } 
    } 

    return $arr; 
} 

if (get_magic_quotes_gpc()) 
{ 
    if (!empty($_GET))    { $_GET    = strip_magic_quotes($_GET);    } 
    if (!empty($_POST))   { $_POST   = strip_magic_quotes($_POST);   } 
    if (!empty($_COOKIE)) { $_COOKIE = strip_magic_quotes($_COOKIE); } 
}

and then addslash any that are being used to build a query?

Hmm..makes sense...extra work..lol..but makes sense.

not that much extra work

just include this recursive function at the top of all your pages dealing with gpc and you're done.

Also, do not just use mysql_real_escape_string() but also check the expected lengths data type of your variables. See the functions I posted a few posts above as they might save you some time.

[CapitalWebHost]

CapitalWebHost: almost, but DON'T use addslashes. Use mysql_real_escape_string() if you're using MySQL.

Got it...thanks. Now to go back and rework all my CMS backend code...

LOL.

[dbevfat]

Better now then later

[jispa]

someone care to explain what that function do?

[arborint]

What are your methods for preventing sql injection? For example, say I am adding $value into my database. What is the best way to make sure that if $value contains " ' " it is inserted itno the database, but does not break the query?

I didn't notice anyone mention filtering or the database specific escaping functions. I would recommend:

// or for string values
$value = preg_replace('/[^a-zA-Z0-9\_\-]/', '', $value);
// or for integer values
$value = intval($value);

// and for mysql for example
$sql = "UPDATE mytable SET myfield='" . mysql_real_escape_string($value) . "' WHERE ...";
// or postgres
$sql = "UPDATE mytable SET myfield='" . pg_escape_string($value) . "' WHERE ..."; 

I believe it is a best practice to filter all values that your script uses from the request and to escape all data that goes into a database.

[coo_t2]

I didn't notice anyone mention filtering or the database specific escaping functions. I would recommend:[PHP]// or for string values
$value = preg_replace('/[^a-zA-Z0-9\_\-]/', '', $value);

[snip]

What if you want to keep whitespace characters?

--ed

[arborint]

What if you want to keep whitespace characters?

Then add any of the them to the regex you want, or use some of the special regex character groups.

 $value = preg_replace('/[^a-zA-Z0-9\ \_\-\$\\\'\"]/', '', $value); 

The regex is saying: replace all characters not in my set of characters to null.

[coo_t2]

Then add any of the them to the regex you want, or use some of the special regex character groups.

 $value = preg_replace('/[^a-zA-Z0-9\ \_\-\$\\\'\"]/', '', $value); 

The regex is saying: replace all characters not in my set of characters to null.

OK, I just thought there might be a reason why you were filtering out whitespace characters.

--ed

[jjshell]

why quoted integers would be a good protection against injections attacks?

[ceefour]

"I didn't notice anyone mention filtering or the database specific escaping functions. I would recommend:"

There it goes again... this thread is now been soooooooo long and filled up with redundant and many disinformation. I would never recommend making up your own escaping function. It's very easy to make mistakes and that means you may be vulnerable to attacks. And there's no such thing as database-independent escaping, so even if your function works on MySQL there's no guarantee it'll work on other DBMSs.

Why is this "not notice anyone mentioning database-specific escaping function"? Hasn't you seen mysql_escape_string() or mysql_real_escape_string() being mentioned NUMEROUSLY in this thread? It's clearly database specific to me, except if you didn't read the 'mysql' part.

My recommendation goes as this: Use a good database abstraction layer, like MDB (my way), Creole (if you have PHP 5), ADOdb, or PEAR DB (last and least of all, but most supported). Then use it's database-independent escaping function, i.e. quote() for MDB. Rest assured you'll be safe (unless there's a bug with the library you're using).
And please always note that you'll need to perform additional escaping if you use some data in a LIKE / REGEXP pattern or something else.

I have to warn you that a lot of recommendations in all other posts in this thread won't prevent SQL injection attacks. And some aren't portable. A correction to my recommendation is gladly acceptable.

[ceefour]

why quoted integers would be a good protection against injections attacks?

Actually, you have to escape (which also means quote) "integers" to prevent SQL injection attacks. Quoting by itself is not enough.

However, the fastest and easiest way if you're dealing with integers is just typecast it (or use intval, it does exactly the same thing), and forego escaping/quoting altogether. And that works with any DBMS. If anybody is trying to SQL inject that integer, well the typecasted integer would probably end up as some arbitrary value (usually 0), but that's much better than an executable SQL expression.

[tezza]

I haven't read every single comment thus far but I thought it worth mentioning that this is new http://phpsec.org/
Their security user guide contains a brief segement on SQL injection but I don't think it adds much to the discussion here. The rest of the article is still worth a read though.

I think a good place to start is by creating a user in the RDBMS with heavy restrictions (particularly against accessing system tables).

I like the bin2hex idea. It's the simplest one so far but I haven't seen how you get it back from hex. I think the main drawback is you can't easily administer/report on data without being inside a script which can will reverse bin2hex(). Can anyone come up with a scenario where this technique could still be exploited by a cracker?
There's also base64encode/decode. Could base64decode([insert SQL here]) ever produce a result which could exploit base64encode() ?
There is also pack()/unpack() which seems to give you more options for "insulating" your input data (not that you really need more options).

[Dean C]

I use a function which I pass an array, it's key is the incoming post/request vars value, and the value is it's datatype.

e.g.

$data->cleanse(
        array(
                $_POST['somevar'] => INT,
                $_POST['anothervar'] => STR,
                $_POST['lala'] => STR_NO_HTML
        )
); 

Inside of this method it does all the work. INT intval()'s the value, STR addslashes the value, STR_NO_HTML strips all html and then addslashes

[ceefour]

I use a function which I pass an array, it's key is the incoming post/request vars value, and the value is it's datatype.

e.g.

$data->cleanse(
        array(
                $_POST['somevar'] => INT,
                $_POST['anothervar'] => STR,
                $_POST['lala'] => STR_NO_HTML
        )
); 

Inside of this method it does all the work. INT intval()'s the value, STR addslashes the value, STR_NO_HTML strips all html and then addslashes

Seems like there are still lots of people using the [evil] addslashes().
Oh well... what do I care. It's not like I'm a proper-escaping evangelist.

[Dean C]

addslashes isn't evil, it's never failed me before now

Edit:

I have read your earlier posts, it's interesting as I haven't ever had that problem before but we'll see

[stereofrog]

Well, all this ado about addslahes vs mysql_real_escape_string is pretty much about nothing. Mysql docs say black on white:

Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. This function (mysql_real_escape_string) quotes the other characters to make them easier to read in log files.

addslashes escapes quotes and backslashes quite perfect, so why bother.

[RhythmAddict]

Easy. Use stored procedures with bind variables...

[Super Phil]

Easy. Use stored procedures with bind variables...

My prefered solution, although a lot of people here use mysql.

I like the bin2hex idea. It's the simplest one so far but I haven't seen how you get it back from hex. I think the main drawback is you can't easily administer/report on data without being inside a script which can will reverse bin2hex(). Can anyone come up with a scenario where this technique could still be exploited by a cracker?
There's also base64encode/decode. Could base64decode([insert SQL here]) ever produce a result which could exploit base64encode() ?
There is also pack()/unpack() which seems to give you more options for "insulating" your input data (not that you really need more options).

ugh - and lose the ability to search / order your data? And increase table size? The easiest solution is not the best.

[Young Twig]

I will add (if it hasn't already been said) that a nice (int) typecast is a quick way to avoid SQL injections in integer fields:

$userid=(int) $_POST['userid'];
$password=some_sql_injection_preventing_function($_POST['password']);

$query='SELECT * FROM users WHERE password\''.$password.'\' AND id='.$userid; 

Now try the classic OR 1=1.

Userid: 5 OR 1=1
Password: ****

SELECT * FROM users WHERE password='asdf' AND id=5

[dbevfat]

As stated in posts above, this might have unwanted results, because:
1) values beginning with int, such as '12 or 25', are casted to 12
2) values not beginning with an int, such as 'blah blah', are casted to 0

Both will result in valid queries, no error detected, but will at least return wrong data.

[coo_t2]

As stated in posts above, this might have unwanted results, because:
1) values beginning with int, such as '12 or 25', are casted to 12

But that's a programming decision. The only values that should be casted to an int type are values that are ONLY allowed to be of an int type. "12 or 25" is a string value and should be escaped and quoted.

--ed

[Young Twig]

But that's a programming decision. The only values that should be casted to an int type are values that are ONLY allowed to be of an int type. "12 or 25" is a string value and should be escaped and quoted.

--ed

Exactly. Like you wouldn't typecast (int) on a search term.

[ceefour]

Exactly. Like you wouldn't typecast (int) on a search term.

Do we _really_ have to state something this obvious?

[Young Twig]

Do we _really_ have to state something this obvious?

[Dylan B]

I like the bin2hex idea. It's the simplest one so far but I haven't seen how you get it back from hex.

<?php 
function hex2bin($data) 
{ 
   $len = strlen($data); 
   return pack("H" . $len, $data); 
} 
?>