Is it safe to let the user specify the mysql field to search?

Question

I have a search program that has multiple input text boxes that correspond to fields in a mysql database. I would like to know if it is safe to have a custom search box where the user can enter the actual field to be searched and its value.

Like this:

<form method='post'>
 <input type='text' name='param1' />
 <input type='text' name='param2' />
 <input type='text' name='customField' />
 <input type='text' name='customValue' />
</form>

Then when it is submitted:

$param1 = mysql_real_escape_string($_POST['param1']);
$param2 = mysql_real_escape_string($_POST['param2']);
$customField = mysql_real_escape_string($_POST['customField']);
$customValue = mysql_real_escape_string($_POST['customValue']);

$query = "SELECT * FROM mytable WHERE field1 LIKE '" . $param1 . "' AND field2 LIKE '" . $param2 . "' AND " . $customField . " LIKE '" . $customValue . "'";

This is an internal webpage and only a few of us will actually see these new boxes but I would like to know if something like sql injection is possible here.

How do you think? **Of course NOT!** – Your Common Sense Feb 09 '11 at 13:53 — Your Common Sense, Feb 09 '11 at 13:53

score 5 · Accepted Answer · answered Feb 09 '11 at 13:47

5

You should check that the fields they provided are in a list/array of fields you allow searching within. Add backticks around the field names in the query just to be extra safe as well. Doing both those things will prevent any injection through those variables.

answered Feb 09 '11 at 13:47

Dan Grossman

51,866
10
112
101

$whitelist = array('list','of','approved','column','names'); if(in_array($param1,$whitelist)) { execute query here... } – Bryan Feb 09 '11 at 14:25

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

You have to hardcode all possible wariants

See this my answer for example code: In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

edited May 23 '17 at 10:33

Community

1
1

answered Feb 09 '11 at 14:00

Your Common Sense

156,878
40
214
345

score 0 · Answer 3 · answered Feb 09 '11 at 13:48

0

I'd use PDO, just to be safe - then make sure you handle all errors accordingly.

answered Feb 09 '11 at 13:48

Dave Kiss

10,289
11
53
75

1

PDO (or any wrapper around prepared statements) won't let you bind column names, only values. – Dan Grossman Feb 09 '11 at 13:49
2

PDO - providing false sense of security since PHP 5.1 – Mchl Feb 09 '11 at 13:52

score 0 · Answer 4 · answered Feb 09 '11 at 13:50

0

Mike first of all disable the multiquery mode of your mysql and rest is fine,

And be aware of use of single table, if you are not using the joins then i do not there there will be any effect.

answered Feb 09 '11 at 13:50

Ankur

111
8

Is it safe to let the user specify the mysql field to search?

4 Answers4

Linked