0

I'm trying to select a variable column name in my table, but this doesn't seem to work:

$reponse = $bdd->prepare('SELECT :day AS day FROM TABLE WHERE id= :id');               
$reponse->execute(array('day' => 'monday', 'id' => '5')); 
$day = $reponse->fetch();

Even by setting 'day', to a sure known element in my table (monday), it doesn't work. Same for id.

Does someone know how to fix that?

I have no php error output, only a mysql query error (that doesn't show). By replacing ':day' by monday, I have an output.

user2864740
  • 60,010
  • 15
  • 145
  • 220
gr3g
  • 2,866
  • 5
  • 28
  • 52
  • 2
    You simply can't bind parameters like this. Build the string with string concatenation, and be sure to validate the input to make sure it's a valid column or expression. – Mike Christensen Sep 29 '14 at 22:15
  • Concatenation is the only way? If it is, post as answer please – gr3g Sep 29 '14 at 22:19
  • 1
    Where "valid input" means "use a [whitelist](http://en.wikipedia.org/wiki/Whitelist)" in context. – user2864740 Sep 29 '14 at 22:28
  • What means Whitelist? Comparing variable to array of possibilities? – gr3g Sep 29 '14 at 22:30
  • @user1824508 In this case, yes. But if the value *never* comes from the user (directly or indirectly) then such is largely a moot point. – user2864740 Sep 29 '14 at 22:30
  • It doesn't come from user. But I'd like to know more about this "Whitelist" – gr3g Sep 29 '14 at 22:32

2 Answers2

1

Table and Column names cannot be replaced by parameters in PDO. You will need to filter and sanitize the data manually.

Nameless11
  • 199
  • 1
  • 3
1

Due to the order in which the SQL is parsed, there's simply no way to use a bound parameter as part of the SQL statement (for example, a column or table name).

Instead, you'll need to build the string with string concatenation. If the value of :day comes from an external source (database, POST parameter, etc), to avoid possible SQL injection attacks you'll want to validate the input to make sure it's a valid column or expression.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326