11

According to the PHP Documentation PDO::prepare() adds quotes to all your parameters so that you don't have to worry about doing it:

"The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."

The problem with this for me is the way I am building my queries and my database structure. Usually the FROM part of an SQL Statement wouldn't need to be parametrized because the Table probably would be defined by direct user input. However with my code that is the case in some places and thus I feel more comfortable with the parametrized version.

SELECT * FROM ? WHERE ?=?

as opposed to SELECT * FROM tablename WHERE ?=?

So my question is this, is it possible to prevent my PDO Object from adding the quotes around the FROM parameter so that I don't get SQL errors thrown in my face? Or do I have to do this in a different manner.

asdf
  • 357
  • 2
  • 11
  • 2
    What is the question exactly? – jeroen Jan 16 '12 at 18:01
  • It does not necessarily add quotes. Drivers that support native prepared statements will retain the literal `?` and the database server does the substitution. -- However you do need to explain what specifically your code is doing and why that's a problem here. Adding `'?'` or `':placeholder'` in your FROM clauses are literal strings, not bound parameters. – mario Jan 16 '12 at 18:03
  • Sorry about that, I accidentally hit enter before I was finished typing the question – asdf Jan 16 '12 at 18:04
  • 2
    possible duplicate of [Table name as parameter using PDO/MySQL prepared statement](http://stackoverflow.com/questions/7541761/table-name-as-parameter-using-pdo-mysql-prepared-statement) and [Escaping field names in PDO statements](http://stackoverflow.com/questions/1542627/escaping-field-names-in-pdo-statements) - In essence: no sorry. That's not possible. You need a whitelist or filtering and handle table/field identifiers yourself. They can't be parameterized, as there would be no statement preparation/optimization possible. – mario Jan 16 '12 at 18:06
  • You can use a table as a param. it needs to be hard coded or in place via php text manipulation before you pas it to prepare. – prodigitalson Jan 16 '12 at 18:08

2 Answers2

6

The placeholders in prepared statements are for values only. The only way to insert dynamic table names is to do it yourself

"SELECT FROM `".$table."` WHERE `".$column."` = ?"
KingCrunch
  • 128,817
  • 21
  • 151
  • 173
  • 3
    And of course just wrapping the value with back ticks isn't secure if the variable contents came from a tainted source. – Matthew Jan 16 '12 at 18:14
  • @YourCommonSense Can you give me a use-case, where the _table_ and _column_ names come from a source out of your control? When they _somehow_ base on user input it is much easier to just give them your DB-password. This said you can either assume they come from a reliable source (a configuration for example, or hardcoded somewhere else), or you did something very wrong. And it still doesn't prevent you from using your brain :) – KingCrunch Mar 03 '14 at 03:01
  • @YourCommonSense Yeah, probably good reason to downvote. But I'm still awaiting a use-case, where you expect a table name from an unreliable source? – KingCrunch Mar 04 '14 at 14:15
  • It is not about "downvote". This answer should be deleted, not downvoted. – Your Common Sense Mar 04 '14 at 17:37
3

@KingCrunch is mostly correct in his answer. You should really escape the string on your own. Something like this should protect against most injections:

//make sure $table and $column only contain alphanumeric chars  
$table = preg_replace("/[^A-Za-z0-9]/", '', $table);
$column = preg_replace("/[^A-Za-z0-9]/", '', $column); 

$query = "SELECT FROM `{$table}` WHERE `{$column}` = ?"
David Kryzaniak
  • 163
  • 7