Prepared statements and Escaping

Question

I've read many times over - and just want to clarify (I think I'm confused)

I switched to mysqli today, and started using prepared statements.

Example of my prepared statement

function read($table, $var) {
    if($stmt = mysqli_prepare($link, "SELECT * FROM ? WHERE `uid`=?")) {
        mysqli_stmt_bind_param($stmt, "si", $table, $var);
        mysqli_stmt_execute($stmt);
        return mysqli_fetch_assoc($stmt);
    } else {
        echo '<script type="text/javascript>">alert("Something went wrong");</script>';
    }
}

$info = read("users", $_SESSION['uid']);
$char = read("characters", $_SESSION['uid']);

Do i still need to escape anything? I know, i know, i've read it everywhere that you dont need to escape when using prepared statements, but then there are questions like this and this that make me worried.

John Woo · Accepted Answer · 2012-12-27T04:53:22.450

4

the only problem with you query is that you cannot pass the tableName as a paramater. Only values can be parameterized. So the other way is to concatenate the tableName along with your query.

"SELECT * FROM `" . $tableNameHere . "` WHERE `uid`=?"

edited Dec 27 '12 at 04:53

answered Dec 27 '12 at 04:47

John Woo

258,903
69
498
492

1

is this subject to injection? – Wulf Dec 27 '12 at 04:47
2

if the **downvoter** could explain how `tableName` can be parameterized, i'll delete this answer. – John Woo Dec 27 '12 at 04:49
1

see [Can PHP PDO Statements accept the table name as parameter?](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) and [Which tokens can be parameterized in PDO prepared statements?](http://stackoverflow.com/questions/1622521/which-tokens-can-be-parameterized-in-pdo-prepared-statements) – John Woo Dec 27 '12 at 04:51
Thank you for the links. Does this mean it's safe to enter in table names into the query? – Wulf Dec 27 '12 at 04:56
1

@Wulf unfortunately, you still need to manually sanitized it. adding backticks around the table name may help prevent from the attack but I cannot fully guarantee it. – John Woo Dec 27 '12 at 04:57
1

@Wulf: It's as subject to SQL injection as any query that doesn't escape interpolated data. You might want to ensure that the table name doesn't contain any wacky chars, and/or is actually the name of a table. (`mysqli_real_escape_string` works for values, but identifiers follow slightly different rules. In particular, they are delimited by backticks, which `mysql_real_escape_string` doesn't escape.) – cHao Dec 27 '12 at 04:57
1

@cHao `mysqli_real_escape_string` still doesn't fully protect your from the attack. see the this article: [SQL injection that gets around mysql_real_escape_string()](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – John Woo Dec 27 '12 at 04:58
As long as I am the one who defined the variable $table = "blah"; (in the page.php, it will be alright because the user won't be able to edit the variable, correct? – Wulf Dec 27 '12 at 05:01
1

@JW.: People love to trot that old horse around. In order for properly sanitized data to get around it, though, you need to (1) be using an old version of mysql, *and* an old version of the mysql libs, *and* (2) not tell mysql which charset you're using, *and* (3) be using one of the vulnerable character sets (which no one outside of China does). – cHao Dec 27 '12 at 05:01
1

@Wulf: If you set `$table`, and it's not from user input, it's safe. – cHao Dec 27 '12 at 05:03

score -1 · Answer 2 · edited May 23 '17 at 11:56

Well, first of all this code just won't work.

So, there are 2 questions actually

Do I need to do any additional escaping on the bound parameters

No.

How to safely insert an identifier into query?

It depends. As long as you have your table name hardcoded in your code - it's ok to insert it as is.
But if it's coming from the untrusted source, you have to filter it out, using whitelisting. I've explained it in my other answer https://stackoverflow.com/a/8255054/285587

As for the questions you linked to, the second one is irrelevant and first one just makes very little sense. LIKE is supposed to return many rows, so, one have to either use no LIKE at all or worry not about it (in terms of safety). Though in terms of returning correct result you may want to escape characters that have special meaning in LIKE, but I wouldn't use LIKE for the search purposes at all.

I do not understand your last paragraph one bit... what's "LIKE"? Are you referring to the questions I posted? — Wulf, Dec 27 '12 at 05:08

Prepared statements and Escaping

2 Answers2