绑定到PDO查询的参数数量不正确

Question

I have a method verifyCredentials in a class that's used to verify user credentials. I'm rewriting it to make use of PHP's PDO instead of DBMS dependant mysqli-statements. I'm having trouble with getting parameters bound to my prepared query.

PDO always throws the warning

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in [file] on line [line]

I'm clearly missing something here, but I can't figure out what for the life of me.

Code snippet, everything in caps except DBH and STH are defined by an external constants.php file:

class FancyClass{
function __construct(){
    try{
        $this->DBH=
            new PDO(PDO_DRIVER.':host='.DB_HOST.';dbname='.DB_DB,
                    DB_USER, DB_PWD);
    }
    catch(PDOException $e){
        return $e->getMessage();
    }
    $this->queryGetPwdForUser="select :userIdCol , :pwdCol from :usersTable where :aliasCol = ':alias' limit 1"
}

function __destruct(){
    $this->DBH=null;
}

function verifyCredentials($alias,$pwd){
    $STH=$this->DBH->prepare($this->queryGetPwdForUser);
    $STH->bindParam(':userIdCol',$userIdCol);
    $STH->bindParam(':pwdCol',$pwdCol);
    $STH->bindParam(':usersTable',$usersTable);
    $STH->bindParam(':aliasCol',$aliasCol);
    $STH->bindParam(':alias',$alias);
    $userIdCol=DB_COLUMN_USERID;
    $pwdCol=DB_COLUMN_USERPWD;
    $usersTable=DB_TABLE_USERS;
    $aliasCol=DB_COLUMN_USERALIAS;
    $STH->execute();
    $result=$STH->fetch();

    if($result==false) return false;
    $hasher = new PasswordHash(50,false);
    if($hasher->CheckPassword($pwd,$result[DB_COLUMN_USERPWD]))
        return $result[DB_COLUMN_USERID];
    else
        return false;
}
}

Answer 1

To clarify what needs to be done, even though fixing the ':alias' quotes suggested before, MySQL keywords (like SELECT, INSERT), table names and column names can't be bound through placeholders using prepared statements. In order to dynamically create a MySQL query, you have to replace these values in some other way.

I noticed you're predefining the SQL queries already, so using something like str_replace or maybe define a general method which may replace these placeholder with values like so:

$sql = str_replace(array(
  ':userIdCol',
  ':pwdCol',
  ':usersTable',
  ':aliasCol'
), array(
  $userIdCol,
  $pwdCol,
  $usersTable,
  $aliasCol
), $this->queryGetPwdForUser);

Obviously the approach for the prepared statement in this case

$STH=$this->DBH->prepare($sql);
$STH->bindParam(':alias',$alias);