PDO语句在预准备语句中插入字符串值的整数

Question

My prepared statement is defined as a method of a generic mysql class. Inserts using this method which insert into a different table work fine. Inserts into a specific table replace all my interpolated values with integers. Prepared statement and query look fine. It looks like the integers inserted are interpolated from the "category_id" field.

The statement preparation:

$sql = "INSERT INTO post_data (`headline`, `body`,`online`,`category_id`,`post_date`)
        VALUES (:headline, :body, :online, :categoryId, NOW())";
$bindValues = array('headline' => (string) $headline
, 'body' => (string) $body
, 'online' => (int) $online
, 'categoryId' => (int) $categoryId);
$mysql->insert($sql, $bindValues);

The $mysql->insert method (which works for another table but not the above query:

public function insert($sql, array $bindValues) {
$stmt = $this->pdoConn->prepare($sql);
foreach ($bindValues as $name => $value) {
    $type = PDOBindings::getType($value);
    //see below for PDOBindings::getType()
    $stmt->bindParam($name, $value, $type);
}
try {
     $this->pdoConn->beginTransaction();
     $stmt->execute();
     $this->lastInserted = $this->pdoConn->lastInsertId();
     $this->pdoConn->commit();
} catch(Execption $e) {
     $this->pdoConn->rollback();
     return $e->getMessage();
}
return ($this->lastInserted > 0) ? $this->lastInserted : null;

The PDOBindings::getType() static method is fairly straightforward:

public static function getType($bindValue) {
    $itsType = gettype($bindValue);
    switch ($itsType) {
        case "string":
            return PDO::PARAM_STR;
        break;
        case "integer":
            return PDO::PARAM_INT;
        break;
        case "boolean":
            return PDO::PARAM_BOOL;
        break;
        default :
        return PDO::PARAM_STR;
    }
}

An insert of:

INSERT INTO post_data (`headline`, `body`,`online`,`category_id`,`post_date`)
VALUES (:headline, :body, :online, :categoryId, NOW())

with the following:

$bindValues = array('headline' => (string) "This is the headline"
       , 'body' => (string) "This is the body field to be inserted"
       , 'online' => (int) 0
       , 'categoryId' => (int) 2);

Inserts the following row:

id  headline    body    online  category_id     post_date

7   2           2   2   2           2013-11-03 08:34:49

Note that the categoryId had the value of 2.

Stepping through the query with Xdebug does not indicate any issues with the data being set incorrectly.

It's difficult to debug as I cannot step into the PDO libraries themselves to determine where it's overriding the interpolations.

A quick note on the schemas. headline is a varchar, body is text, online is tinyint and category_id is a medium int.

Also, remember that this insert works just fine for another table.

Here's what didn't work:

Re-arranging the order of the insert items, and bindings arrays.

Removing date-time field. (throws exception.)

What works is inserting directly into rows, or using old-school mysql query building.

Additionally, this should ideally be a different question but PDO also seems not to be recognizing exception handlers:

$this->pdoConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Doesn't throw exceptions in the above try block. execution just fails.

Answer 1

The reason is that bindParam binds the parameters by reference. You're binding all your parameters to the same variable $value. So when you execute the prepared statement, it will use the last value of this variable for all the parameters. That's why it's inserting 2 in every column.

Use bindValue instead of bindParam and I think it should solve your problem. Or get rid of your loop that calls bindParam entirely, and just pass $bindValues to execute().