query with select() return not decrypted

[Paul75]

Hello

When I do like this :

this.query->createQuery('table')
.select('SUBSTRING(table.cp, 0, 2)', 'cp')
.andSelect('COUNT(table.id)', 'nb')
....

That return good number (nb) but the result is returned (cp) the two letters but not decrypred ... Exemple have in CP value 75011 (crypted: start by pM) and return pM not 75.

Thanks

[generalpiston]

@Paul75 could you clarify a bit by giving your entity definition and full query?

[Paul75]

Hello,

I do this in repository :

public async findByLieuVie(): Promise<Usager[]> {
        const query = this.createQueryBuilder('usager')
            .select('COUNT(DISTINCT usager.cp) as nombre , SUBSTRING(usager.cp,1,2)', 'codePostal')
            .groupBy('SUBSTRING(usager.cp,1,2)');
        const result = await query.execute();

        console.log('result vie nombre', result);

        return result;
    }

And When execute the api in POSTMAN for exemple or seeresult in console of firefox I see the good result into 'nombre' but into 'codePostal' have not see 75 but the two first crypted characters.....

Like :

[
  [
    {
      "nombre": "25915",
      "codePostal": "pM"
    }
  ]
]

Thanks

[generalpiston]

Assuming cp is the encrypted column -- the transformer kicks in AFTER the result is returned from the database. Complex queries won't work. To do complex queries on an encrypted field, your database would have to be in charge of encryption/decryption.

[generalpiston]

Closing since this seems like a non-issue.

[Paul75]

Hello,

Thank you but therefore to carry out requests of this kind I cannot use your tool but in Mysql request manage decryption? So using your program can I not get the result? I understood well ?

Thank you

[generalpiston]

@Paul75 correct. Your use-case is currently not the goal of this project. For MySQL, I'd look into MySQL encryption data-at-rest.