[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
Environment:

Win XP, MySql5, Php5.3, ZendFramework 1.11.4



 Description   

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

Because

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

i processed like this:

...
// controller
$membre = new \Entity\TMembre();
$membre->setPassword($password);
$em->persist($membre);
$em->flush();
...
?>
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."')"
    }


 Comments   
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 Mon Apr 21 10:02:10 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.