Preventing SQL Injection

Need a little advice

[BlueScreen]

Hi,

I've never really considered this until recently, but after reading more about it, I'd like some advice on how to do it.

Most of the articles on it I read weren't particularly helpful or detailed, so I thought I would ask here in hopes of getting a simple answer.

I've been able to protect numeric values by using is_numeric and failing if false, but I'm still lost as to how to prevent SQL injection on strings.

IE in the following code (I know it's bad, just trying to give a simple example from my head)

$username = addslashes($_POST['username']);
$id = '4';

mysql_query("UPDATE table_test SET
username=$username
WHERE ID=$id");

--

I been told it's possible to inject SQL into that statement and similar statements, but I've no idea how to protect against it.

I'd apreciate any advice.

[dcrombie]

You can avoid most exploits by using addslashes [php.net] and adding single quotes around all variables in the SQL statement:

$username = addslashes($username); 
 $id = intval($id); 
 mysql_query("UPDATE table_test SET username='$username' WHERE ID='$id'");

;)

[coopster]

The PHP Forum Library [webmasterworld.com] has a few threads on this topic. A quick search over the forum will turn up plenty of reading material as well.

[BlueScreen]

Thanks, some very useful information there.

After reading a lot of that, following links, and reading more, and double checking the PHP documentation itself and it's comments, I'm still unsure on when to use addslashes, and when to use mysql_real_escape_string?

When do you use which, and why?

Thanks a lot.

[coopster]

The manual pages clearly recommend we try to use any database-specific string escape function when available.

Quote each non numeric user supplied value that is passed to the database with the database-specific string escape function (e.g. mysql_escape_string(), sql_escape_string(), etc.). If a database-specific string escape mechanism is not available, the addslashes() and str_replace() functions may be useful (depending on database type).

Kind of limits you if you are designing an application that you may want to port to other databases though. If you intend to port in this way, you should use your own abstraction layer or the PEAR::DB class.

[BlueScreen]

Thanks a lot, I apreciate it.

Although I had read mysql_real_escape_string was preferred, addslashes is normally what tends to be recommended, so I wondered if there was anything it did that mysql_real_escape_string did not, and if it was preferable in any particular circumstances (other than working with different database systems)

Thanks.

[Aleister]

mysql_real_escape_string and add_slashes are completely different functions. MySQL has characters that need to be escaped, which are not always the same as what you need to escape for browser output.

It is safer to use the right one for the job.

[jatar_k]

another interesting thing I/we ran into the other day that I didn't know. You have to have a connection before calling mysql_real_escape_string otherwise it tries to connect with blank values and it dies.

[henry0]

Jatar K Interesting comment - thanks-

about using:

<?php
function escapestring ($string)
{
if (get_magic_quotes_gpc() )
{
return $string;
}
else
{
return mysql_real_escape_string($string);
}
}
?>

[Aleister]

I have been using this function to make input safe for mysql queries (after performing standard validation - so you make sure you have the right kind of data you need from the user)

function fix_for_mysql($value){
if (get_magic_quotes_gpc()) $value = stripslashes($value);
$value = "'" . mysql_real_escape_string($value) . "'";
return $value;
}

Then, since it already has the single quotes around it, I use it like this:

 
$username = fix_for_mysql($username);
$q = sprintf("select password from users where username = %s", $username);
$result = mysql_query($q, $dbc);