News Archive
PhpRiot Newsletter
Your Email Address:

More information

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

Note: This article was originally published at Planet PHP on 2 August 2010.
Planet PHP

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():

string(16) "?Irp??s)??m"

Converting them back into there hex representation is similar:

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.

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

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:

_email = $email; return $this; } a public function getId() { return $this-_id; } a /** * @PrePersist */ public function generateUuid() { $this-_id = str_replace('-', '', uuid_create(UUID_TYPE_TIME)); } }

The important p

Truncated by Planet PHP, read more at the original (another 901 bytes)