[DDC-1144] How insert a AES_ENCRYPT value in a table field Created: 10/May/11  Updated: 19/Mar/14  Resolved: 19/Mar/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.0.4
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Major
Reporter: dquintard Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 0
Labels: None

Win XP, MySql5, Php5.3, ZendFramework 1.11.4


Hi there,
I'm trying to insert an encrypted data:


INSERT statements are not allowed in DQL, ....

i processed like this:

// controller
$membre = new \Entity\TMembre();
namespace Entity;
 * TMembre
 * @Table(name="t_membre")
 * @Entity(repositoryClass="Repository\TMembreRepository")
class TMembre
     * Set password     *
     * @param string $password     */
    public function setPassword($password)
    	$this->email = "AES_ENCRYPT('".$email."','"._MYSQL_CRYPT."')"; => insert this entire string without executing encryption
    	$this->email = new \Doctrine\ORM\Query\Expr\Func("AES_ENCRYPT",array("'".$email."'","'"._MYSQL_CRYPT."'")); => does not work

How can i do ?
Add this method to Doctrine\ORM\Query\Expr class ?

    public function aesEncrypt($value)
       return "AES_ENCRYPT('".$value."','"._MYSQL_CRYPT."')"

Comment by Marco Pivetta [ 19/Mar/14 ]

This approach is flawed from a security perspective, since your data AND the encryption key are likely flowing through either a socket to the DB server.

This also allows people to just log the queries and catch any calls to AES_* functions.

Once the attacker got in, he can simply copy all the data and decrypt it on his own machine from an SQL dump.

I would suggest to NOT encrypt in custom DBAL types nor through SQL queries: do it in your service layer with proper encryption built into PHP.

Generated at Sun Oct 04 13:09:24 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.