9

I am using PDO and want to do something like this:

$query = $dbh->prepare("SELECT * FROM :table WHERE :column = :value");
$query->bindParam(':table', $tableName);
$query->bindParam(':column', $columnName);
$query->bindParam(':value', $value);

Will PDO allow me to bind the table name and the column name like this? It seems to allow it, but it puts quotes around my parameters even if I use PDO::PARAM_INT or PDO::PARAM_BOOL as the data type.

If this won't work, how can I safely escape my variables so that I can interpolate them in the query?

elynnaie
  • 861
  • 2
  • 13
  • 28
  • A prepared statement is a query who's path has been analyzed. You can't analyze a query with no table or even column information. – Xeoncross Nov 29 '11 at 16:30

1 Answers1

13

Unfortunately, you can't bind parameters by column names.

What you could try is to dynamically create your SQL command:

$sql = "SELECT * FROM $tableName WHERE $columnName = :value";
$query = $dbh->prepare($sql);
$query->bindParam(':value', $value);

Just make sure to sanitize your parameters/variables if they are coming from elsewhere, to prevent SQL Injection. In this case, $value is safe to a degree but $tableName and $columnName are not -- again, that is most especially if the values for these variables are not provided by you and instead by your users/vistors/etc...

One other thing; please avoid using * and name your columns instead... See some reasons why:

http://www.jasonvolpe.com/topics/sql/

Performance issue in using SELECT *?

See other similar posts here:

Why doesn't binding parameter in ORDER BY clause order the results?

How do I set ORDER BY params using prepared PDO statement?

Community
  • 1
  • 1
Nonym
  • 6,199
  • 1
  • 25
  • 21
  • So what is the proper way to escape things in PDO? It seems like PDO::quote() is not what I want. – elynnaie Nov 29 '11 at 16:33
  • You can take a look at the following: http://php.net/manual/en/function.mysql-escape-string.php or http://www.php.net/manual/en/function.htmlentities.php (among other ways to validate your inputs). People say it's okay `not` to escape parameters when binding using PDO's `bindParam` or `bindValue` since the value is being 'bound' as a value and not something to be interpreted as anything else, but I **`highly recommend`** that you sanitize/validate all input coming from your user. That includes querystrings. – Nonym Nov 29 '11 at 16:40
  • Ok, I wasn't sure if using something like mysql_real_escape_string will work cross-database or if there was a more generic function to use. – elynnaie Nov 29 '11 at 16:42
  • In PHP, you can still use: [htmlentities](http://www.php.net/manual/en/function.htmlentities.php) and [html_entity_decode](http://www.php.net/manual/en/function.html-entity-decode.php) or you can use regular expressions if you want to customize how you validate your data. :) – Nonym Nov 29 '11 at 16:52