Support true VARCHAR binding to prevent implicit conversions?

[gjdanis]

Feature Request

Q	A
New Feature	yes
RFC	no
BC Break	no

Summary

PHP 7.2 added support for specifying different bind types for unicode and non-unicode string data: https://wiki.php.net/rfc/extended-string-types-for-pdo

This is a major improvement over the default (PDO::PARAM_STR) which binds strings as unicode data. In the past, the inability for PHP to handle this has led to performance problems in certain cases. For example if we have something like:

$statement = $connection->prepare(
   'SELECT * FROM ... WHERE myVarcharColumn = :value' -- will bind as N'...'
);

$statement->bindValue(':value', $myVarcharValue);
$statement->execute();

If there's an index on myVarcharColumn SQL won't be able to use the index effectively since the binding will be an NVARCHAR string and force an implicit conversation.

Does the team have a plan to support this binding in the future? We actually implemented this internally in a wrapper we have DBAL. I'm happy to open a PR if the team supports this feature:

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\StringType;
use PDO;

class VarcharType extends StringType
{
    public const NAME = 'varchar';

    public function getSQLDeclaration(array $field, AbstractPlatform $platform)
    {
        $length = $field['length'] ?? $platform->getVarcharDefaultLength();
        return 'VARCHAR(' . $length . ')';
    }

    public function getBindingType()
    {
        return PDO::PARAM_STR | PDO::PARAM_STR_CHAR;
    }

    public function getName()
    {
        return static::NAME;
    }
}

[morozov]

Does the team have a plan to support this binding in the future?

I believe it would be a valuable addition. Note that the direct usage of PDO with DBAL is deprecated (see ParameterType). The solution will have to also work with sqlsrv, support PARAM_STR_ARRAY (which will probably warrant some refactoring) and will require some integration testing to prove that with the properly bound parameters no conversion is happening.

[gjdanis]

Started working on this here @morozov: https://github.com/doctrine/dbal/pull/4270/files#diff-0abfe812e47f01473ac9b172da2d2c3cR87

Please let me know if this is close to what you had in mind.

[morozov]

Please let me know if this is close to what you had in mind.

I didn't know it could be tested like this but this way looks good.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.