DDC-1144: How insert a AES_ENCRYPT value in a table field #1744

Closed
doctrinebot opened this Issue May 10, 2011 · 2 comments

2 participants

@doctrinebot

Jira issue originally created by user dquintard:

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

Because {quote}INSERT statements are not allowed in DQL, ....{quote} 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."')"
    }
@doctrinebot

Comment created by @ocramius:

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.

@doctrinebot

Issue was closed with resolution "Won't Fix"

@Ocramius Ocramius was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment