1

Is it posible to bindParam in the order by portion of a sql statement. For example, is the following possible?

select whatever from table where age > :age order by :user_specified_order_by_field_name_here

and if not, what's the recommended way to make sure that the user_specified_order_by_field_name_here does not contain SQL injection code?

Josh
  • 8,082
  • 5
  • 43
  • 41
Average Joe
  • 4,521
  • 9
  • 53
  • 81

1 Answers1

3

No, PDO doesn't support dynamic table or column names as prepared values. Any column names you insert into the query will not be escaped, and will lead to a SQL injection vulnerability.

PDO::Quote() won't help either - it can escape strings only, but in mySQL, column names aren't strings.

The only 100% safe way to prevent problems is to compare user_specified_order_by_field_name_here against a list of valid columns in the table. You could even use numbers (that you resolve into column names internally) to add an additional layer of obscurity.

Community
  • 1
  • 1
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • that's the super safe method no doubt... whitelist that is... but could not pdp:quote method or some other generic technique takes the worries out here? – Average Joe Feb 12 '12 at 17:24
  • @John no, because table names aren't escaped by quotes. Doing a PDO:quote on it won't help. – Pekka Feb 12 '12 at 17:26
  • @everybody I don't know how the focus got on the table name itself, the focus should have been on the order by. Also, if in my question, the query were to be :table, escaping it would still be necessary ( whether in SQL, the table names may contain a string or not ) cause a malicious user string could still inject a bad intended SQL piece right there where the table name is. Whether table names can have ' is irrelevant. But that wasn't the focus. In my situation, my table name is not passed with a variable, it's hard wired into the SQL, – Average Joe Feb 12 '12 at 18:07
  • In the order by area, a malicious string can stop the sql statement and start a destructive one. The question is what do we do about it - other than the whitelist approach. Can we still not use the quote method? Do you recommend mysql_real_escape_string instead? – Average Joe Feb 12 '12 at 18:09
  • @John as said, PDO::Quote and mysql_real_escape_string won't work. They are for escaping strings only. Whitelisting is the only really good way to go I'm afraid. You could wrap the table name in backticks and then remove all the backticks from the field name but I can't guarantee it's perfectly safe. – Pekka Feb 12 '12 at 18:10
  • @John ugh, I meant *column* name of course, sorry :) Fixing. But what was said is valid for column names as well, no change there. Do some whitelisting, it really is the best way to go – Pekka Feb 12 '12 at 18:11
  • so you are saying, they can come up with a damaging sql piece for the `user_specified_order_by_field_name_here` variable without using any ' ( single quote ) therefore escaping it does no good? I don't see how though? How could they come up with a SQL injection piece - without using a single quote? How would you do it? – Average Joe Feb 12 '12 at 18:16
  • 1
    Easily -- `CHAR()` will let you assemble a string without single quotes. `UNION SELECT user, password FROM accounts` doesn't contain single quotes either. –  Feb 12 '12 at 18:21
  • @John All they would have to do is add some SQL instead of a column name - it would be inserted into your query. Because PDO doesn't allow multiple queries at once, they can't do a `; DROP TABLE table;` but they can still modify your SQL query which is bad – Pekka Feb 12 '12 at 18:21