Dynamic arguments in prepared statements

Question

people!

I have this query which gets its WHERE arguments from $_POST. The thing is I want to get both the comparison values dynamically, without creating a query for each value. What I have now is:

$what = $mysqli->real_escape_string($_POST['what']);
........
$query = "SELECT * FROM list WHERE ";
$query .= $what . " = ? LIMIT 0,10";
........
$stmt->bind_param('s', $what);

My first question: How safe is this in practice? Is there another, better way I can manage the same thing, since i cannot bind both parameters?

Second problem I have is: I want to change the = ? to LIKE %?%, but i can't get it working. I tried using CONCAT('%', ?, '%') (bad, I know) , but it's still not working. Basically what i want to achieve in the end is: WHERE ? LIKE %?%. Is it possible? Can you help me a bit with this?

Cheers, Alex

Even if you could avoid SQL injection, you can't really avoid someone breaking a query against your database (because they put in a column that doesn't exist), without constantly checking your table for the columns that exist - definitely go with a whitelist as suggested. — Colin M, Feb 11 '13 at 15:05
with like try: `WHERE .. LIKE ?` and then `bind_param('s', '%test%')` — bitWorking, Feb 11 '13 at 15:10
I got the `LIKE` to work with Mogria's suggestion: `$where = '%' . $_POST['where'] . '%';` — vulkoingim, Feb 11 '13 at 15:33
@vulkoingim the problem with this is the user can also enter % signs. If you don't want this, see the link in my answer, to escape that properly too. PDO won't do that. — MarcDefiant, Feb 11 '13 at 18:36
Got it , but I'm using mysqli and when i try to append `%` in the `bind_param()` as shown in your link i get: `Fatal error: Cannot pass parameter 2 by reference` Maybe i should switch to PDO altogether... — vulkoingim, Feb 12 '13 at 07:41

score 0 · Answer 1 · answered Feb 11 '13 at 15:07

Just check $where on strict set of parameters:

if (!in_array($where, array("table1", "table2"))) {
   //do smth with wrong parameter
}

Note that in_array is running in O(N) complexity so better use associative array with table names as keys if your set of parameters is big.

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

mysql_real_escape_string or similar functions do not escape backticks (`), therefore your Application is vulnerable to SQL Injection.

Simply define a white-list with all your columns and check if the column is in the Array.

For searching after LIKE %?% simply append a % in the front and in the back of the search term. For proper escaping this thing see: Escaping MySQL wild cards

edited May 23 '17 at 10:32

Community

1
1

answered Feb 11 '13 at 15:09

MarcDefiant

6,649
6
29
49

score -1 · Answer 3 · answered Feb 11 '13 at 15:07

-1

You could do a query dynamic like that but your website will be vulnerable to SQL Injection.

answered Feb 11 '13 at 15:07

Vuk Vasić

1,398
10
27

score -1 · Answer 4 · answered Feb 11 '13 at 15:53

mysql_real_escape_string escape backticks, but you can escape them manually.
It is also good idea to move all that escaping stuff into some sort of helper library. So, you will get your data in 2 lines:

$sql  = "SELECT * FROM list WHERE ?n = ?s LIMIT 0,10";
$data = $db->getAll($sql, $_POST['what'], $_POST['where']);

Dynamic arguments in prepared statements

4 Answers4