SQL injection even when the variable is escaped [duplicate]

This question already has answers here: How can I prevent SQL injection in PHP? (27 answers) Closed 2 years ago.

The sql injection will work only when my query looks like below sample

SELECT * FROM login WHERE id = $my_id_va;

Assume if my query is

SELECT * FROM login WHERE id = $my_id_va ORDER BY id DESC

Than I will get following error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by id desc' at line 1

So, this 1 or 1=1; SHOW TABLES will not work, correct?

My site was hacked successively many times.

I want one quick answer: When my query looks like the following one, what ways or which types of query can they use to hack my site?

SELECT * FROM login WHERE id = $my_id_va ORDER BY id DESC

What are the ways to execute the show table in the following query

SELECT *开发者_StackOverflow中文版 FROM login WHERE id = $my_id_va ORDER BY id DESC

I am also using escaping function to handle the query string values, like mysql_real_escape_string($my_id_va). Yes, obviously this for single related hack, but not sure.

Added some more

SELECT EventActuallyCharged, EventDate FROM tblevent WHERE EventDate between '2011-07-21 or 1=1; SHOW TABLES --' and '2011-07-31' ORDER BY EventDate DESC

but show table not worked

---

If you are using PHP5, use parametarized query, use PDO.

---

Int cast

If id is a number, you can int-cast your variable as well. Integers are safe to use:

$x = (int)$yourInputVar;
$s = "select * from Table where id = $x";

mysql_real_escape_string

If you want to pass a string, you can, and should, use mysql_real_escape_string, but this function escapes only those characters that are inside the string. You will still need to add quotes around the string, so:

 $x = mysql_real_escape_string('hello');
 $s = "select * from Table where id = $x";

.. will result in the query: select * from Table where id = hello. This is obiously not a valid query, since hello should be in quotes.

Change the query to:

 $x = mysql_real_escape_string('hello');
 $s = "select * from Table where id = '$x'";

.. and everything works fine. You add the quotes around, and mysql_real_escape_string takes care of special characters inside the string, if any.

Parameters

Another solution is to use parameterized queries. This can by done using MySQLi or PDO. The advantage is that you only tell your database where a variable should be inserted, and the database takes care of the escaping yourself.

It also may add a performance benefit, because these queries could be cached without their parameters, make a more efficient use of the query cache. This doesn't really work yet in current versions of MySQL, though.

---

You are right that 1 or 1=1; SHOW TABLES will give a syntax error but this will work:

1 or 1=1 --

The -- comments out the rest of the query.

In your case the value is an integer so instead of using mysql_real_escape_string you can use intval.

---

If you set $my_id_va to:

1 or 1=1; SHOW TABLES --

The -- will comment out the rest of the command, effectively terminating it.

I'm not sure what effect mysql_real_escape_string will have on the query. What you should be doing is parameterized queries.

---

1. First query somehow secured

$sql = sprintf('SELECT * FROM login WHERE id = %d ORDER BY id DESC', mysql_real_escape_string($my_id_va));

2. Second query somehow secured

$sql = sprintf("SELECT EventActuallyCharged, EventDate FROM tblevent WHERE EventDate BETWEEN '%s' AND '%s' ORDER BY EventDate DESC", 
             mysql_real_escape_string($start_date),
             mysql_real_escape_string($end_date));

---

Read the docs about sprintf if you don't understand it.

However, as others have said, it would be very very secure if you would use parameterized queries with a class such as PDO or MySQLi.