Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1144

How insert a AES_ENCRYPT value in a table field

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 2.0.4
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • 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."')"
          }
      

        Activity

        Hide
        Marco Pivetta added a comment -

        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.

        Show
        Marco Pivetta added a comment - 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.

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            dquintard
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: