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;
}
}
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);