CODEWITHSUNDEEP
phpmysqlpdo
Roy Grubb
Roy Grubb

Reputation: 93

PDO binding with bindValue not working

I'm trying to use prepared statements and PDO for the first time, with a MySQL database. I've been referring to the PDO Tutorial for MySQL Developers, the PHP manual pages on PHP Data Objects and the numerous answers in SO covering this but I haven't been able to find what I'm doing wrong.

To be specific, I can't get named placeholders to work. Can someone put me right, please?

The database table I'm using, t_list is 

  `user_id` mediumint(8) unsigned NOT NULL,
  `list_name` char(30) NOT NULL,
  `list_items` longtext NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`list_name`)

The unsafe approach, using variables directly in the SQL, works ...

if ($tableName=="t_list") {
    try {
        $stmt = $db->prepare("SELECT * FROM t_list");
        $stmt->bindValue(":user_id", $user_id, PDO::PARAM_INT);
        $stmt->bindValue(":list_name", $list_name, PDO::PARAM_STR);
        $stmt->execute();
        foreach($db->query("SELECT `list_items` FROM t_list WHERE user_id=$user_id AND list_name='$list_name'") as  $row) {
            $found = 1;
            echo $row['list_items'];
        }    
        $stmt->debugDumpParams();
    } catch(PDOException $e) {
        echo "An error occured reading 't_list' table!"; 
        echo $e->getMessage();                   
    }
    if ($found==0) { 
        echo "read failed : " . $user_id . " / " . $list_name . " not found:"; 
    } 
} else {
    echo 'tableName not recognized (' . $tableName . ')';
}

... but what I thought I should be able to do to make the code safe is: 

foreach($db->query("SELECT `list_items` FROM t_list WHERE user_id=:user_id AND list_name=:list_name") as  $row) {

As suggested in another SO answer, I do this first: 

    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

There are no MySQL or PHP error messages.

What happens:

Say the value in $user_id is 2 and $list_name is "AAAA" when reaching the code above. There is a record in the table corresponding to user 2 / list AAAA, but instead of retrieving that one record, it retrieves all records for user 0. If I replace :user_id with $user_id, it recovers the correct user, but all data for that user, not just the one list_name record.

I tried

$stmt->bindValue(":user_id", (int) trim ($user_id), PDO::PARAM_INT);

but for user 2 that still gave me user 0's records. I tried with bindParam as well, but I think bindValue is what I should be using in this case.

I added $stmt->debugDumpParams() after the query and it showed:

SQL: [23] SELECT * FROM todo_list
Params:  0

I don't know whether Params 0 is a helpful indication.

My use of binding is failing, but I can't see why. I'm running this on an up-to-date installation of Ubuntu.

Upvotes: 0

Views: 6428

Answers (1)

mirza
mirza

Reputation: 5793

I think somehow you've got it backwards. The proper way to use binding params is should be like this:

if ($tableName=="t_list") {
    try {
        $stmt = $db->prepare("SELECT * FROM t_list WHERE user_id=:user_id and list_name=:list_name");
        $stmt->bindValue(":user_id", $user_id, PDO::PARAM_INT);
        $stmt->bindValue(":list_name", $list_name, PDO::PARAM_STR);
        $stmt->execute();
        $rows = $stmt->fetchAll();
        foreach($rows as  $row) {
            $found = 1;
            echo $row['list_items'];
        }    
        $stmt->debugDumpParams();
    } catch(PDOException $e) {
        echo "An error occured reading 't_list' table!"; 
        echo $e->getMessage();                   
    }
    if ($found==0) { 
        echo "read failed : " . $user_id . " / " . $list_name . " not found:"; 
    } 
} else {
    echo 'tableName not recognized (' . $tableName . ')';
}

Upvotes: 2

Related Questions