Search code examples
phpnumbersprepared-statementnegative-number

php pdo: prepare() negatives become positive


I'm using a prepared statement to insert values from a form into a Db, and some of the values are negative.

# html
<input type="radio" name="opinion" value='-2' />
<input type="radio" name="opinion" value='-1' />
<input type="radio" name="opinion" value='0' />
<input type="radio" name="opinion" value='1' />
<input type="radio" name="opinion" value='2' />

# prepare
prepare("UPDATE $db.$dbt SET $question = :value WHERE `key` = :key;");

Troubleshooting steps already taken:
• I echoed the POSTed values from the form and negatives return negative.
• I copied the statement in the prepare() into the CLI and manually inserted a negative value into the same column where prepare is inserting the positive value:
UPDATE db.dbt SET opinion = '-1' WHERE key = '10101'; (tickmarks present in actual SQL)
^ This worked.
• I printed the prepared statements as they're executed, and I noticed that the values are not enclosed in quotes. I think this is where the issue is coming from.

I read that values should not be quoted in the prepare(), so I can't think of anything else to do…

EDIT: The prepare() is inside a loop

foreach ( $pairs as $pair ) {
    list($question , $answer) = explode('=', $pair);
    try {
        $record_data = $dbh->prepare("UPDATE $db.$dbt SET $question = :value WHERE `key` = :key;");
        $record_data->bindParam(':value', $answer);
        $record_data->bindParam(':key', $key);
        $record_data->execute();
    } catch(PDOException $e){};
} // end foreach

Solution

  • If you are using PDO :-

    $sth->bindParam(':value', $opinion, PDO::PARAM_INT);
    

    Default data type for bindParam is PDO::PARAM_STR