PHP PDO parameterised query for MySQL

Question

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 "[email protected]", which is an email
    $tableColName="userName"; // Or "userEmail"
    $sth=$dbh->prepare("SELECT * FROM users WHERE :tableColName = :userIdentifier");
            $sth->bindParam(":tableColName", $tableColName);
            $sth->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

Community · Accepted Answer · 2023-11-17 20:42:18Z

3

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

edited Nov 17, 2023 at 20:42

CommunityBot

11 silver badge

answered Jun 16, 2011 at 3:22

Stephane Gosselin

9,1485 gold badges45 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stephane Gosselin Over a year ago

Sorry for my bad formatting I have major keyboard problems at the moment. ;)

JDelage Over a year ago

Yep, that seems to work, I wonder why they allow the use of a variable but not of parameters. In any case, I can deal with it. Thank you.

Collectives™ on Stack Overflow

PHP PDO parameterised query for MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related