Is this a safe way to protect against SQL injection?

Question

This code is 100% safe from SQL injection:

$id = $_GET['id'] mysql_query('SELECT * FROM mytable WHERE id < ' . (int)$id); 

or do i need to do this?

 $id = $_GET['id'] mysql_query('SELECT * FROM mytable WHERE id < ' . mysql_real_escape_string($id)); 

Answer 1

The request may still explode if $_GET['id'] empty, or (int)$_GET['id'] evaluates to empty. As a result, you get a syntax error in the request. This is not enough to blindly run away or jot down a value and insert it into the query. You must verify that the ultimate “safe” value is really safe, not just a wolf in grandma’s clothes.

Answer 2

This article seems good in explaining how mysql_real_escape_string can protect you from SQL Injection, but also explains its “holes”.

http://www.webappsec.org/projects/articles/091007.shtml

Answer 3

it

 mysql_query('SELECT * FROM mytable WHERE id < ' . mysql_real_escape_string($id)); 

it will be bad practice. If you want this to be a string, at least quote the string:

 mysql_query('SELECT * FROM `mytable` WHERE `id`<"'.mysql_real_escape_string($id)).'"'; 

(and while you are specifying all the field and table names on it, since things like id can or will become reserved keywords at some point)

I would prefer a cast if it is a whole. One argument for the string version will be that on some day the identifier can be alphanumeric (as is increasingly seen on many websites).

Answer 4

I am using sprintf , mysql_query(sprintf('SELECT * FROM mytable WHERE id < %d', $id));

Answer 5

You must use parameterized queries. Then you do not need to worry about everything that escapes. It also makes reading SQL easier. Oh, and don't use select *, just select what you want.

Answer 6

No
you have to do this:

 $id = mysql_real_escape_string($_GET['id']); //put the escaped string in a $var, so your select statement stays readable //this will help in debugging, and make **not** forgetting those vital quotes //easier. $query = "SELECT * FROM mytable WHERE id < '$id'"; // ^ ^always single quote your $vars // ^ ^ and double quote the query $result = mysql_query($query); //and test to see if your query ran successful. if (!$result) { //your query gave an error, handle it gracefully. 

Then you are safe.

Answer 7

It's safe, but you probably want to do

 if(is_int($id)){ mysql_query('SELECT * FROM mytable WHERE id < ' . mysql_real_escape_string($id)); // just to be safe always to escape }