Typified FALSE value in PDO (Postgres) bug

[fankandin]

There is a problem with casting false boolean values in PDO (e.g. in Postgres) working through Yii DAO. The error message completely describes the problem:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: ""
LINE 1: INSERT INTO "test" ("id", "val") VALUES ('1', '')
^. The SQL statement executed was: INSERT INTO "test" ("id", "val") VALUES (:id, :val). Bound with :id=1, :val=false

There is an official solution:

This issue has been resolved on the tip of the 5.1 branch.
There was a bug, but your script was still technically "wrong".

By default, PDO treats all data as strings, so the bool was being cast to string, which proved to be incompatible with the bool that pgsql expected.

You need to tell PDO when you're binding booleans (or any type that might be ambiguous to the driver); you can do this using either PDOStatement::bindParam() or PDOStatement::bindValue():

$res->bindValue(1, false, PDO_PARAM_BOOL);
$res->execute();

See for details: https://bugs.php.net/bug.php?id=33876

[samdark]

@vintage-dreamer, when working through DAO you can specify parameter type using bindParam or bindValue. What's the code you're using exactly?

[fankandin]

The problem is in code like this:

Yii::app()->db->createCommand()->insert('test', array(
                'id'=>1,
                'val'=>false,
        ));

for PgSQL table like this:

CREATE TABLE public.test (
  id INTEGER NOT NULL, 
  val BOOLEAN NOT NULL, 
  CONSTRAINT test_pkey PRIMARY KEY(id)
);

Some more details I described on the forum: http://www.yiiframework.com/forum/index.php/topic/32334-boolean-type-with-postgresql/

[samdark]

Currently I don't have PostgreSQL test environment so if you'll be able to fix, test and pull-request it would be great.

[fankandin]

So, Alexander,
there is no trivial way to fix this problem.

All the the queries' functions (like select, insert, update) in CDbCommand misses binding values through PDO ($this->_statement). But for type casting as in the described situation we must do bindValue(), and we cannot composite it with CDbCommand::insert(), we can only use CDbCommand::execute() if we bind values directly.

I have corrected the code in CDbCommand - added binding values in CDbCommand::execute():

            if($params)
            {
                foreach($params as $param=>$value)
                {
                    $this->bindValue($param, $value);
                }
            }
            $this->_statement->execute();

I tested new code with all basic data types in MySQL and PostgreSQL: everything works OK. The issue become fixed. But I doubt that my fix corresponds to the author's plan (who as I can see in the function comments deliberately declined this binding due to performance (?)).

But I pay your attention: the problem is gone only with strong binding all params, and the current realization really has a defect described in this issue.

How do you think, what should I do now?

[stelio]

I experience this issue today with PHP 5.3.10-1ubuntu3.4 and PostgreSQL 9.1.5 (latest versions in the ubuntu repos at time of writing) using the latest checkout of Yii. The suggestion from vintage-dreamer didn't seem to remedy the issue. I ended up adding a implicit cast from int to bool (which is frowned upon by the Postgres gurus), so hopefully this issue gets resolved soon.

[shakal]

confirming bug with PHP 5.3.10-1ubuntu3.4 and PostgreSQL 9.1. and yii 1.1.10
I revert php to 5.3.10-1ubuntu3 until this is resolved

[jagutex]

I have Datatype mismatch, yii is inserting integer (1) when it should be boolean (true). Is there any fix to this issue? I have tried follow tips from this topic on yii forum, but they don't seem to work http://www.yiiframework.com/forum/index.php/topic/32334-boolean-type-with-postgresql/

[salvarezm]

I needed a solution urgent and i changed the function bindValue in CDbCommand.php

#yii/framework/db/CDbCommand.php
public function bindValue($name, $value, $dataType=null)
{

    $this->prepare();
            if($value === false)
                $this->_statement->bindValue($name,'FALSE',PDO::PARAM_STR);
            else if($value === true)
                $this->_statement->bindValue($name,'TRUE',PDO::PARAM_STR);
    else if($dataType===null)
        $this->_statement->bindValue($name,$value,$this->_connection->getPdoType(gettype($value)));
    else
        $this->_statement->bindValue($name,$value,$dataType);
    $this->_paramLog[$name]=$value;
    return $this;
}

This work for me, and this will be not dangerous in the future, al least in postgresql

For some reason the function binValue not work, and i believe that is a php bug

[stelio]

@salvarezm Your fix worked for me, thanks.