Invalid generation where expresion sql when Type in use SQLConversion (convertToPHPValueSQL, convertToDatabaseValueSQL) #7022

New Issue

admin · 2026-01-22T15:43:19+01:00

admin commented

2026-01-22 15:43:19 +01:00

Originally created by @dbannik on GitHub (Aug 11, 2022).

Bug Report

Q	A
BC Break	no
Version	2.20

Summary

Invalid generation where expresion sql when Type in use SQLConversion (convertToPHPValueSQL, convertToDatabaseValueSQL)

How to reproduce

final class EncryptedType extends BlobType
{
    public const NAME = 'encrypted';

    public function getName(): string
    {
        return self::NAME;
    }

    private function getSecret(): string
    {
        return EncryptedKey::getKey();
    }

    public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform): string
    {
        return sprintf('AES_ENCRYPT(%s, \'%s\')', $sqlExpr, $this->getSecret());
    }

    public function convertToPHPValueSQL($sqlExpr, $platform): string
    {
        return sprintf('AES_DECRYPT(%s, \'%s\')', $sqlExpr, $this->getSecret());
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return $value;
    }

    public function requiresSQLCommentHint(AbstractPlatform $platform): bool
    {
        return true;
    }
}

/**
 * @ORM\Table(name="User")
 * @ORM\Entity
 */
class User
{
    /**
     * @ORM\Id
     * @ORM\Column(name="Usr_Id", type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    public int $id;

    /**
     * @ORM\Column(name="Usr_FirstName", type="encrypted", nullable=false)
     */
    public string $firstName;

    /**
     * @ORM\Column(name="Usr_LastName", type="string", nullable=false)
     */
    public string $lastName;

    /**
     * @ORM\Column(name="Usr_Created", type="datetime_immutable", nullable=false)
     */
    public DateTimeImmutable $created;

    /**
     * @ORM\Column(name="Usr_DOB", type="date_immutable", nullable=false)
     */
    public DateTimeImmutable $dob;

    public function __construct(string $firstName, string $lastName, DateTimeImmutable $created, DateTimeImmutable $dob)
    {
        $this->firstName = $firstName;
        $this->lastName  = $lastName;
        $this->created   = $created;
        $this->dob       = $dob;
    }
}

$result = $entityManager->createQueryBuilder()
    ->from(User::class, 'user')
    ->select('user.firstName')
    ->where('user.firstName like :search')
    ->setParameter('search', '%testname%')
    ->getQuery()
    ->getResult();

Expected behavior

SELECT AES_DECRYPT(u0_.Usr_FirstName, 'secretKey') AS Usr_FirstName_0 FROM User u0_ WHERE AES_DECRYPT(u0_.Usr_FirstName, 'secretKey') LIKE '%testname%'

Current behavior

SELECT AES_DECRYPT(u0_.Usr_FirstName, 'secretKey') AS Usr_FirstName_0 FROM User u0_ WHERE u0_.Usr_FirstName LIKE '%testname%'

Originally created by @dbannik on GitHub (Aug 11, 2022). ### Bug Report  | Q | A |------------ | ------ | BC Break | no | Version | 2.20 #### Summary Invalid generation `where expresion` sql when `Type` in use SQLConversion (`convertToPHPValueSQL`, `convertToDatabaseValueSQL`) #### How to reproduce ```php final class EncryptedType extends BlobType { public const NAME = 'encrypted'; public function getName(): string { return self::NAME; } private function getSecret(): string { return EncryptedKey::getKey(); } public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform): string { return sprintf('AES_ENCRYPT(%s, \'%s\')', $sqlExpr, $this->getSecret()); } public function convertToPHPValueSQL($sqlExpr, $platform): string { return sprintf('AES_DECRYPT(%s, \'%s\')', $sqlExpr, $this->getSecret()); } public function convertToPHPValue($value, AbstractPlatform $platform) { return $value; } public function requiresSQLCommentHint(AbstractPlatform $platform): bool { return true; } } ``` ```php /** * @ORM\Table(name="User") * @ORM\Entity */ class User { /** * @ORM\Id * @ORM\Column(name="Usr_Id", type="integer") * @ORM\GeneratedValue(strategy="AUTO") */ public int $id; /** * @ORM\Column(name="Usr_FirstName", type="encrypted", nullable=false) */ public string $firstName; /** * @ORM\Column(name="Usr_LastName", type="string", nullable=false) */ public string $lastName; /** * @ORM\Column(name="Usr_Created", type="datetime_immutable", nullable=false) */ public DateTimeImmutable $created; /** * @ORM\Column(name="Usr_DOB", type="date_immutable", nullable=false) */ public DateTimeImmutable $dob; public function __construct(string $firstName, string $lastName, DateTimeImmutable $created, DateTimeImmutable $dob) { $this->firstName = $firstName; $this->lastName = $lastName; $this->created = $created; $this->dob = $dob; } } ``` ```php $result = $entityManager->createQueryBuilder() ->from(User::class, 'user') ->select('user.firstName') ->where('user.firstName like :search') ->setParameter('search', '%testname%') ->getQuery() ->getResult(); ``` #### Expected behavior ```sql SELECT AES_DECRYPT(u0_.Usr_FirstName, 'secretKey') AS Usr_FirstName_0 FROM User u0_ WHERE AES_DECRYPT(u0_.Usr_FirstName, 'secretKey') LIKE '%testname%' ``` #### Current behavior ```sql SELECT AES_DECRYPT(u0_.Usr_FirstName, 'secretKey') AS Usr_FirstName_0 FROM User u0_ WHERE u0_.Usr_FirstName LIKE '%testname%' ```

admin commented

2026-01-22 15:43:20 +01:00

@mpdude commented on GitHub (Jan 19, 2023):

Does this work in 2.x, and if so, could you provide a test case to help isolate the regression?

@mpdude commented on GitHub (Jan 19, 2023): Does this work in 2.x, and if so, could you provide a test case to help isolate the regression?

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#7022