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

        dquintard created issue -
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Workflow jira [ 12626 ] jira-feedback [ 13924 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 13924 ] jira-feedback2 [ 15788 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 15788 ] jira-feedback3 [ 18045 ]
        Marco Pivetta made changes -
        Description Hi there,
        I'm trying to insert an encrypted data:

        Because '"INSERT statements are not allowed in DQL, ...." i processed like this:
        <?php
        ...
        // controller
        $membre = new \Entity\TMembre();
        $membre->setPassword($password);
        $em->persist($membre);
        $em->flush();
        ...
        ?>
        //entity
        <?php
        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."')"
            }

        Best regards

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

        Because {quote}INSERT statements are not allowed in DQL, ....{quote} i processed like this:

        {code}
        ...
        // controller
        $membre = new \Entity\TMembre();
        $membre->setPassword($password);
        $em->persist($membre);
        $em->flush();
        ...
        ?>
        {code}

        {code}
        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
            }
        }
        {code}

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

        {code}
            /**
            public function aesEncrypt($value)
            {
               return "AES_ENCRYPT('".$value."','"._MYSQL_CRYPT."')"
            }
        {code}
        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.
        Marco Pivetta made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Assignee Benjamin Eberlei [ beberlei ] Marco Pivetta [ ocramius ]
        Resolution Won't Fix [ 2 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1144, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated:
              Resolved: