Reputation: 93
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
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