How do I protect my script from SQL injection attacks?

[zappsan]

Well, I've been working on a script which is almost finished.
The only thing bothering me now is security.

For example, I've got lines like this in my script:
$query = mysql_query('SELECT * FROM news WHERE ID="'.$_GET['newsid'].'"')

I am aware that this is an insecure method and people could perform MySQL injection attacks easily.

How can I prevent this from happening?

I've searched Google of course and found a good amount of articles on this, but what I need is a simple method which doesn't reuire me to change much.

Wikipedia suggests the use of mysql_real_escape_string (http://en.wikipedia.org/wiki/SQL_Injection#Securing_applications_against_SQL_in jection).
Would using this be enough protection?

Also, does anybody know any free software which scans your script for risks like this?
I already found an XXS scanner and a scanner for both XXS and SQL injection possibilieties (but I didn't get that to work).

I'd be glad if someone could help me :)

[nico_swd]

mysql_real_escape_string() is pretty safe. If you want it even safer, then filter your input for each thing you want to do. For example, use intval() (http://us2.php.net/manual/en/function.intval.php) for numeric values such as your newsid. Don't ever put the $_GET or $_POST variables directly in your mysql string.


$newsid = intval($_GET['newsid']);


If you only want to allow for example 25 chars, then filter your input before insering it into the database.


$input = substr($input, 0, 25);


If you only want to allow alphabetical chars, then filter out the others.

$input = preg_replace('/[^a-z]/i', '', $input);


Don't want to allow HTML?

$input = htmlentities($input);


Want to strip out all tags?

$input = strip_tags($input);

You can also use the quote_smart() function from php.net.


function quote_smart($value)
{
// Stripslashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// Quote if not a number or a numeric string
if (!is_numeric($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}


http://us2.php.net/manual/en/function.mysql-real-escape-string.php

If you keep in mind all that, then it should be pretty safe. Just only allow to insert the characters that you really need. Always use mysql_real_escape_string() along with the methods above though. Just incase.

[Chousho]

Oh, a couple things here I didn't know.

Thanks for the info, nico!