PHP PDO parameterised query for MySQL

All,

I'm trying to write a function that would check in my users table whether a user name or a user email exist already. This is to be used as a check during user registration.

I want to be able to use the same function to check whether an email or a name exist. I would use the function by sending it 2 string variables, a $tableColName which represents the column in the db table (so either "userName" or "userEmail", and a $userIdentifier, which represents either a user name or a user email I want to query on.

I wrote the following (modified to be free standing):

<?php
    $dbHost="localhost";
    $dbName="project";
    $dbUser="admin";
    $dbPassword="abcd";
    $dbh=new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUser, $dbPassword);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $userIdentifier="apple";  // Or "apple@gmail.com", which is an email
    $tableColName="userName"; // Or "userEmail"
    $sth=$dbh->prepare("SELECT * FROM users WHERE :tableColName = :userIdentifier");
            $sth->bindParam(":tableColName", $tableColName);
            $sth-&开发者_运维知识库gt;bindParam(":userIdentifier", $userIdentifier);
            $sth->execute();
    print("PDO::FETCH_ASSOC: ");
    print("Return next row as an array indexed by column name");
    echo "</br>";
    $result = $sth->fetch(PDO::FETCH_ASSOC);
    print_r($result);
    echo "</br>";
    if ($result==null){
        print("FALSE - result is null");
    }else{
        print("TRUE - result isn't null");
    }
?>

This doesn't work. What works is a query where I specify the column name directly in the query instead of using a parameter, but I loose the flexibility I sought:

<?php
    $dbHost="localhost";
    $dbName="project";
    $dbUser="admin";
    $dbPassword="abcd";
    $dbh=new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUser, $dbPassword);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $userName="apple";
    $sth=$dbh->prepare("SELECT * FROM users WHERE userName = :userIdentifier"); // Cannot be used for userEmail search.
            $sth->bindParam(":userIdentifier", $userName);
            $sth->execute();
    print("PDO::FETCH_ASSOC: ");
    print("Return next row as an array indexed by column name");
    echo "</br>";
    $result = $sth->fetch(PDO::FETCH_ASSOC);
    print_r($result);
    echo "</br>";
    if ($result==null){
        print("FALSE - result is null");
    }else{
        print("TRUE - result isn't null");
    }
?>

What am I doing wrong?

Thanks,

JDelage

+1'ed for my love of PDO!

As for your issue friend, table names and column names cannot be passed as parameters in PDO. Refer to this post for more info.

I believe using good old variables (filtered of course!) would work out nice for you.

 $tableName = "email";
 $sth=$dbh->prepare("SELECT * FROM users WHERE $tableName = :userIdentifier");

        $sth->bindParam(":userIdentifier", $userIdentifier);
        $sth->execute();

Not tested , but it should give you a start. Also, do remember to filter $tableName, one (of many) simple way this can be done with a simple array that holds a whitelist of allowed tablenames, here is a simple example:

$validTables = array('email', 'username');

if(!in_array($tableName, $validTables)){ 
    throw new Exception("Invalid Table Name");
}