How to protect from mysql "union select" injection

Question

Simple question: how can I protect so that when the user inputs "union select" in a field and then sends it as a parameter to a query mysql won't give an error.

Should I simply check if "union select" is inputed or there are also other keywords that could result in a mysql error?

Here's the SQL protection function I use now:

function sql_protect(&$n){ 
  $n=stripslashes($n);
  $n=mysql_real_escape_string($n);
  $dntwant = array("\'","\\", "/","\"",")","(","<",">","\\r\\n",";","*","%");
  $n = str_replace($dntwant,"", $n);
}

The query has something similar in it:

where column1 like '%$user_input%'

Why not just `mysql_real_escape_string()`? It seems to me your `str_replace()` may risk undoing something desirable that was done by `mysql_real_escape_string()`. — Wiseguy, Jun 23 '11 at 15:47
Don't invent your own `SQL protect` function. The `mysql_real_escape_string` is fine. How do you do your SELECT? The user's string will always be in '' so a union select could do no harm... `SELECT * FROM table WHERE field = 'a union select x'` — Konerak, Jun 23 '11 at 15:48
I'm not very clear with your question but I think enclosing your parameters in single-quotes should prevent the errors. You may as well like to use escaping functions, like mysql_real_escape_string() if you are using PHP. You may check if "union select" is inputted but then I think you should check for all keywords that is indeed a long list - http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html — Abhay, Jun 23 '11 at 15:50
@Konerak: do you mean don't check for the keywords? Surely I agree that's not a good thing to do; I only mentioned it for Cristy to know that it's a tedious job and not a good thing to do — Abhay, Jun 23 '11 at 16:30

score 4 · Answer 1 · edited May 23 '17 at 10:08

4

You need two things

Either use PDO, no checking needed PDO does everything.

Or use :

$var = mysql_real_escape_string($_POST['var']);
$query = "SELECT * FROM test WHERE var = '$var' ";
//                                       ^    ^  These single quotes are vital!!

This is all you need to do, forget about stripslashes, and str_replace.

If you want to use dynamic database/table/column names, or you want to inject numbers for the limit clause, be warned mysql_real_escape_string will not work. Neither will PDO.
Nothing will work except for checking the input you are about to inject against a pre-approved whitelist.

See this question on how to do that: How to prevent SQL injection with dynamic tablenames?

edited May 23 '17 at 10:08

Community

1
1

answered Jun 23 '11 at 15:47

Johan

74,508
24
191
319

So how can I use the single quotes in a LIKE statement? – XCS Jun 23 '11 at 15:48
`$query = "SELECT * FROM test WHERE var LIKE '$var' ";` – Konerak Jun 23 '11 at 15:49
@Konerak, It's a bit of a word joke, he needs to do either PDO (one of two things) or mysql_real.... and the single quotes (two things). – Johan Jun 23 '11 at 15:52
So how can I use the LIKE with "%" in it so that it's secure? – XCS Jun 23 '11 at 16:01

score 0 · Answer 2 · answered Jun 23 '11 at 17:00

0

Try to prepare and execute the query:

$stmt = $db->prepare("SELECT * FROM test WHERE column1 LIKE ?");
$stmt->execute(array("%{$user_input}%"));

answered Jun 23 '11 at 17:00

corretge

1,751
11
24

How to protect from mysql "union select" injection

2 Answers2