SQL injection even when the variable is escaped

Question

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 * 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

You want us to teach you how to hack, or how to protect? You are already using `mysql_real_escape_string($my_id_va)` which will escape all special characters to protect you against most of the attacks. The harm will be almost zero. If you want to be sure 100%, then use PDO or MySQLi. However, this kind of question smells bad to me. — Shef, Oct 01 '11 at 07:04
@Shef: sorry thing is, i am facing cont prob even i used these mysql_real_escape_string everything, your question makes me angry, i dont, i should proceed my issue furtheir, else shall i stop posting question, your comment is really hurting, i got good sug from GolezTrol, domanokz, Mark Byers, Zach R, but you only thinging like bad, sorry for comment — bharanikumar Bs, Oct 01 '11 at 07:15
I see you commenting everywhere _"why the `SHOW TABLES` is not working?"_ Are you interested in it NOT working, or working? What do you want? — Shef, Oct 01 '11 at 07:18
i should want to know, what are the neg ways available to execute this show table, so that i can understand, this manner also others will try for hacking. somthing like, ok cheers, — bharanikumar Bs, Oct 01 '11 at 10:05

score 2 · Answer 1 · answered Oct 01 '11 at 06:55

2

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

answered Oct 01 '11 at 06:55

dpp

25,478
28
95
150

score 2 · Answer 2 · answered Oct 01 '11 at 06:56

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.

score 1 · Accepted Answer · answered Oct 01 '11 at 06:55

1

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.

answered Oct 01 '11 at 06:55

Mark Byers

719,658
164
1,497
1,412

check my update, show table not worked, but regular query worked as usual – bharanikumar Bs Oct 01 '11 at 07:01
@bharanikumar Bs: You can't use `SHOW TABLE` there. Just use `1 or 1=1 --`. That will fetch all rows from the table. – Mark Byers Oct 01 '11 at 07:06
so if use the mysql_real_escape_string, than 1 or 1=1 -- this malicious will not work know – bharanikumar Bs Oct 01 '11 at 07:17
@bharanikumar Bs: Yes it will, because you didn't quote it. Use intval, like I said in my answer. – Mark Byers Oct 01 '11 at 07:23

score 1 · Answer 4 · answered Oct 01 '11 at 06:55

1

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.

answered Oct 01 '11 at 06:55

Zach Riggle

2,535
15
26

Shef · Answer 5 · 2011-10-01T07:57:15.530

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.

SQL injection even when the variable is escaped

5 Answers5

1. First query somehow secured

2. Second query somehow secured