/usr/portage

Proof of Concept: Binary packed UUIDs as primary keys with Doctrine2 and MySQL 8

The Problem

For a project I need non-guessable synthetic primary keys. I will use them to construct URIs and these URIs need to be non-guessable. If I would use the traditional way of doing so, going the down the route of integer primary keys with auto increments, or using a sequence table an attacker could easily increment or decrement the integer to find some similar items. Next idea was to use UUIDs or GUIDs. These identifiers are globally unique, so this would work for primary keys too. Reading some documentation on the topic brought up the interesting issue of space usage. Storing the UUIDs in a CHAR column would be a huge waste of space compared to an integer primary key. As primary keys are referenced in related table, this would be a huge issue. Finally I found a trick storing there binary representation in a BINARY column. Doing that in MySQL is fairly easy:

INSERT INTO items SET id = UNHEX(REPLACE(UUID(), '-', '');

Selecting a human readable reasult is easy too:

SELECT HEX(id) FROM items;

Achieving the same thing in PHP is pretty straightforward too. You need the PECL extension UUID (pecl install uuid) and pack()/unpack():

<?php
$uuid = uuid_create(UUID_TYPE_TIME);
$uuid = str_replace("-", "", $uuid);
var_dump(pack('H*', $uuid));
string(16) "?Irp??ߐ
                   )??m"

Converting them back into there hex representation is similar:

<?php
var_dump(array_shift(unpack('H*', $binaryUuid)));
string(32) "d2f268509db211df9010000c29abf06d"

Doctrine2 integration

Next step would be integration with Doctrine2. To do so, we need to create a custom mapping type. I’m not using Doctrine2 for database abstraction, but for it’s object relational mapping capabilities so I ignore portability and concentrate on MySQL.

<?php
namespace Lars\Doctrine2\Types\Mysql;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;
 
class BinaryType extends Type
{
    const BINARY = 'binary';
 
    public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return sprintf('BINARY(%d)', $fieldDeclaration['length']);
    }
 
    public function getName()
    {       
        return self::BINARY;
    }   
      
    public function convertToPhpValue($value, AbstractPlatform $platform)
    {
        if ($value !== null) {
            $value= unpack('H*', $value);
            return array_shift($value);
        }
    }
 
    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if ($value !== null) {
            return pack('H*', $value);
        }
    }
}

Now we are introducing the new type to Doctrine2 somewhere in our setup logic:

<?php
use Doctrine\DBAL\Types\Type;
Type::addType('binary', 'Lars\Doctrine2\Types\Mysql\BinaryType');

One issue I stumbled upon was the default Doctrine2 does. With MySQL it maps binary types to intermediate blob types (in the Doctrine2 type system). This default behavior is not configurable, so we need to patch Doctrine\DBAL\Schema\MySqlSchemaManager. I’m sure there is a more elegant way and I would love to receive some remarks here:

            case 'tinyblob':
            case 'mediumblob':
            case 'longblob':
            case 'blob':
            /** 
             * Commented out to make our custom mapping work
             * case 'binary':
             */         
            case 'varbinary':
                $type = 'blob';
                $length = null;
                break;

Last part is our entity:

<?php
namespace Lars\User\Domain;
 
/**
 * @Entity
 * @Table(name="user",indexes={@Index(name="user_email_idx",columns={"user_email"})})
 * @HasLifecycleCallbacks
 */
class User
{
    /**
     * @Id
     * @Column(type="binary",length=16,name="user_id")
     * @GeneratedValue(strategy="NONE")
     */
    protected $_id;
 
    /**
     * @Column(type="string",length=32,name="user_email")
     */
    protected $_email;
 
    public function changeEmail($email)
    {
        $this->_email = $email;
        return $this;
    }
 
    public function getId()
    {
        return $this->_id;
    }
 
    /**
     * @PrePersist
     */
    public function generateUuid()
    {
        $this->_id = str_replace('-', '', uuid_create(UUID_TYPE_TIME));
    }
}

The important part here is the createUuid()-method to generate the UUID once before persisting the domain object. With GeneratedValue(strategy="NONE") we told Doctrine not to generate the ID by itself and with HasLifecycleCallbacks we configure Doctrine to scan for lifecycle callback methods, so that generateUuid() will be called before persisting the entity.

Fetching an object by ID is as easy as ever, but don’t forget to convert the ID:

$user = $em->find(
    'Lars\User\Domain\User',
    pack('H*', '16aec29e9db011df8013000c29abf06d')
);

Further ideas

The whole UUID should be refactored towards an UUID value object to encapsulate UUID creation and binary conversion.

Filed on 01-08-2010, 23:11 under , , , & eight comments & no trackbacks

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

No comments

Add a Comment & let me know what you think