[pgsql] invalid input syntax for type boolean: ""

[fabpico]

Bug Report

Q	A
Version	3.9.3

Summary

I usually use $queryBuilder->createNamedParameter($value) to create queries with prepared statements. Even for boolean values. It was never a problem in mysql, mariadb and mssql in my cases years ago. Sometimes I had to add a type as second parameter. pgsql now fails when the value ist false.

Current behavior

When I run one of following code, and the value is false

$queryBuilder->insert(self::TABLE)->values([
    'is_anonymous' => $queryBuilder->createNamedParameter($entity->isAnonymous()),
    // ..
])->executeQuery();

$queryBuilder->insert(self::TABLE)->values([
    'is_anonymous' => $queryBuilder->createNamedParameter($entity->isAnonymous(), ParameterType::BOOLEAN),
    // ..
])->executeQuery();

Following happens

Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing a query: invalid input syntax for type boolean: ""

  at vendor/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:87
  at Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(object(Exception), object(Query))
     (vendor/doctrine/dbal/src/Connection.php:1939)
  at Doctrine\DBAL\Connection->handleDriverException(object(Exception), object(Query))
     (vendor/doctrine/dbal/src/Connection.php:1881)
...

After some debugging, I worked around to pass a literal 'false' string.

    private function booleanToLiteral(bool $value): string
    {
        return $value ? 'true' : 'false';
    }

$queryBuilder->insert(self::TABLE)->values([
    'is_anonymous' => $queryBuilder->createNamedParameter($this->booleanToLiteral($entity->isAnonymous())),
    // ..
])->executeQuery();

Expected behavior

The Query should succeed with one of these (without having to do an own conversion).

$queryBuilder->insert(self::TABLE)->values([
    'is_anonymous' => $queryBuilder->createNamedParameter($entity->isAnonymous()),
    // ..
])->executeQuery();

$queryBuilder->insert(self::TABLE)->values([
    'is_anonymous' => $queryBuilder->createNamedParameter($entity->isAnonymous(), ParameterType::BOOLEAN),
    // ..
])->executeQuery();

How to reproduce

See the code snippets.

Further

I searched for this issue and found some related issues, but they didn't talk directly about createNamedParameter: #1847, https://github.com/doctrine/dbal/pull/564, https://github.com/doctrine/dbal/pull/625,

[morozov]

Could you provide more details on reproducing the issue? This is what I tried:

<?php

declare(strict_types=1);

namespace Doctrine\DBAL\Tests;

use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Schema\Table;

class AdHocTest extends FunctionalTestCase
{
    public function testBoolean(): void
    {
        $table = new Table('t');
        $table->addColumn('v', 'boolean');
        $this->dropAndCreateTable($table);

        $queryBuilder = $this->connection->createQueryBuilder();

        $result = $queryBuilder->insert('t')->values([
            'v' => $queryBuilder->createNamedParameter(true, ParameterType::BOOLEAN),
        ])->executeStatement();

        self::assertSame(1, $result);
    }
}

The test passes on both the pgsql and pdo_pgsql drivers on DBAL 3.9.3. Are you using the former? What else is missing from the test?

[fabpico]

@morozov The problem is when the value is false.

This will work: $queryBuilder->createNamedParameter(true, ParameterType::BOOLEAN). But this doesn't work: $queryBuilder->createNamedParameter(false, ParameterType::BOOLEAN)

My connection uses driver: pgsql DBAL version is: 3.9.3

What happens on your setup when you pass value false?

[morozov]

Yes, the issue with false is reproducible on pgsql. @fabpico, thanks for the clarification!

The underlying issue is that pg_send_execute() accepts only parameter values but not their types, so it seems to interpret all parameters as strings (hence, false becomes empty string). The documentation doesn't say anything about supported parameter types.

This is similar to https://github.com/doctrine/dbal/issues/2481 / https://github.com/php/pecl-database-pdo_oci/issues/12.

@derrabus do you think this one actually should be fixed in DBAL? Unlike PDO_OCI (which fails to fulfill the PDO API contract), pgsql doesn't declare that is supports various parameter types, and the need to convert binary data to strings before binding implies that the same should be done for all types that PHP cannot convert to a string representation understandable by Postgres.

[derrabus]

This looks like a bug to me. The pgsql extension does not care at all about PHP types and communicates in strings only. PHP's default string cast for booleans is not something Postgres understands. We can probably fix this by mapping the true and false to 't' and 'f' when the binding type is ParameterType::BOOLEAN.

[JanSchuerlein]

I can confirm, I experienced the same bug, only works when 'f' or 't' as string is passed.

if(!$json['active']) { $json['active'] = 'f'; } else { $json['active'] = 't'; }

[ashutoshagrawal1010]

#6936