How to enable cryptographic hash functions (MD5, SHA1 and SHA2) in Doctrine and Symfony 3

A cryptographic hash function is a hash function which takes an input (or 'message') and returns a fixed-size alphanumeric string, which is called the hash value (sometimes called a message digest, a digital fingerprint, a digest or a checksum). The most known of them are functions like MD5, SHA1 and SHA2.

These functions are available natively in the MySQL environment, however are not available in doctrine and you'll get errors if you try to access them in your queries. To add availability to these functions in doctrine, we are going to add 3 doctrine extensions (1 for every function), then we are going to register them in the config.yml file and finally we'll show some use examples.

Implementation

To get started, locate yourself in the root folder of your bundle and create a folder named Extensions (or the root directory /src) in case that it doesn't exists. Then create a folder inside named Doctrine, it will contain all the extension classes for doctrine.

And please, don't forget to change the namespace of every class according to the location inside your project.

MD5

Create inside the previously created doctrine folder a new class named Md5.php and save the following code inside.

<?php

namespace myBundle\Extensions\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;

/**
 * @author Andreas Gallien <[email protected]>
 */
class Md5 extends FunctionNode
{
    public $stringPrimary;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return $sqlWalker->getConnection()->getDatabasePlatform()->getMd5Expression(
            $sqlWalker->walkStringPrimary($this->stringPrimary)
        );
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->stringPrimary = $parser->StringPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

The previous class will allow you to use the MD5 function inside a doctrine query and prevent the known error :

Now the class exists in our project, but it's not registered. In order to use it, we need to register the function in the config.yml file of our project, just go the doctrine area and save the MD5 property with the class path in the dql property of the ORM.

# app/config/config.yml
# Doctrine Configuration
doctrine:
    # Search for the ORM property
    orm:
        # Those values should be already in your file and this doesn't matter
        auto_generate_proxy_classes: "%kernel.debug%"
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        # We need this the dql property to register the custom doctrine functions :
        dql:
            string_functions:
                # Match agains should have the path to the Md5 class created in the previous step
                MD5: myBundle\Extensions\Doctrine\Md5

And you're ready to go! clear the cache in case you're working in the prod environment and let's create some queries.

MD5 Usage

The MD5 function is available for it's use, you can use it within a query builder or plain DQL.

Given the following table Breeds (with entity Breeds), find the breed with the MD5 hash of "1".

Then we could just use a query builder (remember, the MD5 hash of 1 is c4ca4238a0b923820dcc509a6f75849b):

<?php

class DefaultController extends Controller
{
    public function indexAction()
    {
        $em = $this->getDoctrine()->getManager();
        
        $repository = $em->getRepository("sandboxBundle:Breeds");
                
        $reg = $repo->createQueryBuilder('a')
            ->where("a.hash = MD5(:id)")
            ->setParameter('id', 1 )
            ->getQuery()
            ->getResult();
        
        dump($reg);
    }
}

The $reg variable should return the row of Alaskan Malamute.

SHA1

Create inside the previously created doctrine folder a new class named Sha1.php and save the following code inside.

<?php
namespace myBundle\Extensions\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;
/**
 * @author Andreas Gallien <[email protected]>
 */
class Sha1 extends FunctionNode
{
    public $stringPrimary;
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'SHA1(' .
            $sqlWalker->walkStringPrimary($this->stringPrimary) .
        ')';
    }
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->stringPrimary = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

The previous class will allow you to use the SHA1 function inside a doctrine query and prevent the known error :

Now the class exists in our project, but it's not registered. In order to use it, we need to register the function in the config.yml file of our project, just go the doctrine area and save the SHA1 property with the class path in the dql property of the ORM.

# app/config/config.yml
# Doctrine Configuration
doctrine:
    # Search for the ORM property
    orm:
        # Those values should be already in your file and this doesn't matter
        auto_generate_proxy_classes: "%kernel.debug%"
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        # We need this the dql property to register the custom doctrine functions :
        dql:
            string_functions:
                # Match agains should have the path to the Sha1 class created in the previous step
                SHA1: myBundle\Extensions\Doctrine\Sha1

And you're ready to go! clear the cache in case you're working in the prod environment and let's create some queries.

SHA1 Usage

The SHA1 function is available for it's use, you can use it within a query builder or plain DQL.

Given the following table Breeds (with entity Breeds), find the breed with the SHA1 hash of "1".

Then we could just use a query builder (remember, the SHA1 hash of 1 is 356a192b7913b04c54574d18c28d46e6395428ab):

<?php

class DefaultController extends Controller
{
    public function indexAction()
    {
        $em = $this->getDoctrine()->getManager();
        
        $repository = $em->getRepository("sandboxBundle:Breeds");
                
        $reg = $repo->createQueryBuilder('a')
            ->where("a.hash = SHA1(:id)")
            ->setParameter('id', 1 )
            ->getQuery()
            ->getResult();
        
        dump($reg);
    }
}

The $reg variable should return the row of Alaskan Malamute.

SHA2

Create inside the previously created doctrine folder a new class named Sha2.php and save the following code inside.

<?php

namespace myBundle\Extensions\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;

/**
 * @author Andreas Gallien <[email protected]>
 */
class Sha2 extends FunctionNode
{
    public $stringPrimary;

    public $simpleArithmeticExpression;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'SHA2(' .
            $sqlWalker->walkStringPrimary($this->stringPrimary) .
            ',' .
            $sqlWalker->walkSimpleArithmeticExpression($this->simpleArithmeticExpression) .
        ')';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->stringPrimary = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->simpleArithmeticExpression = $parser->SimpleArithmeticExpression();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

The previous class will allow you to use the SHA2 function inside a doctrine query and prevent the known error :

Now the class exists in our project, but it's not registered. In order to use it, we need to register the function in the config.yml file of our project, just go the doctrine area and save the SHA2 property with the class path in the dql property of the ORM.

# app/config/config.yml
# Doctrine Configuration
doctrine:
    # Search for the ORM property
    orm:
        # Those values should be already in your file and this doesn't matter
        auto_generate_proxy_classes: "%kernel.debug%"
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        # We need this the dql property to register the custom doctrine functions :
        dql:
            string_functions:
                # Match agains should have the path to the Sha2 class created in the previous step
                SHA2: myBundle\Extensions\Doctrine\Sha2

And you're ready to go! clear the cache in case you're working in the prod environment and let's create some queries.

SHA2 Usage

The SHA2 function is available for it's use, you can use it within a query builder or plain DQL. Remember that the SHA2 function alculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). The first argument is the cleartext string to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Otherwise, the function result is a hash value containing the desired number of bits.

If we don't provide the second parameter in the doctrine instruction, we'll get the following error message: 

Given the following table Breeds (with entity Breeds), find the breed with the SHA2 hash of "1".

Then we could just use a query builder (remember, the SHA2 in 256 hash of 1 is 6b86b273ff34fce19d6b804eff5a3f5747ada4eaa22f1d49c01e52ddb7875b4b):

<?php 

class DefaultController extends Controller
{
    public function indexAction()
    {
        $em = $this->getDoctrine()->getManager();
        
        $repository = $em->getRepository("sandboxBundle:Breeds");
                
        $reg = $repo->createQueryBuilder('a')
            // Read about SHA2 MYSQL : https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_sha2
            // The hash is stored with SHA2-256, therefore the second parameter of SHA2 is 256
            ->where("a.hash = SHA2(:id,256)")
            ->setParameter('id', 1 )
            ->getQuery()
            ->getResult();
        
        dump($reg);
    }
}

The $reg variable should return the row of Alaskan Malamute.

Have fun !