PDO and Microsoft SQL: Must declare the table variable "@P1"

Question

I'm trying to use binding in PDO to select some entries from a Microsoft SQL database. The code I'm using looks like it's similar to what I've found in the documentation. However, when I run it, I get the warning below:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1087 [Microsoft][SQL Native Client][SQL Server]Must declare the table variable "@P1". (SQLExecute[1087] at ext\pdo_odbc\odbc_stmt.c:254) in (long file path) on line 40

Here's the relevant code:

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM ? WHERE user='?'"; 
$sth = $db->prepare($sql);
$sth->bindValue(1,  $table, PDO::PARAM_STR);
$sth->bindValue(2, $user, PDO::PARAM_STR);
$sth->execute(); //                         <-- line 40
$data = $sth->fetch(PDO::FETCH_ASSOC);

This may be related. When I try to use named parameter markers (:table, :user) instead of question marks, I get this:

Warning: PDOStatement::bindValue() [pdostatement.bindvalue]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in (long file path) on line 39

Why doesn't it like my prepared statement?

Answer 1

You can't bind parameters to table names in SQL. This is true in any language, any database.

You'll have to interpolate the table name into the query string before prepare().

Also you shouldn't put the parameter placeholder inside quotes, even if it's a string or date value. Parameter placeholders inside quotes are interpreted as literal strings. Otherwise how would you ever enter a literal question mark?

Here's how I would write this:

$table = "[User Site]";
$user = "demo";
$sql = "SELECT * FROM $table WHERE user=?"; 
$sth = $db->prepare($sql);
$sth->execute(array($user));
$data = $sth->fetch(PDO::FETCH_ASSOC);

I don't bother with using bindParam() or bindValue(). It's usually easier to just pass the parameter values in an array argument to execute().