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

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

Converting them back into there hex representation is similar:

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.

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:

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;

Last part is our entity:

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

Is TurboDbAdmin the new phpMyAdmin? 0

It’s not that fast because of extensive JavaScript usage, but it is exactly the thing I want. I have asked myself many times, why we developers do not use the current state of web technology for our own tools. Try it!

Filed on 19-09-2007, 15:03 under , , , & no comments & no trackbacks

Wanted: Large scale consultants 0

I am wondering whether there is a good consultant/consultant team in Germany which is specialized on attending an enlargement of a currently running, profitable PHP/MySQL-environment with tons of users. I am looking for something like OmniTI in Germany. No babbling, proofed success in the past, able to work with hitherto unknown components.

Filed on 01-07-2007, 17:05 under , , , , & no comments & no trackbacks

What to do? 0

During my daily work I often heard discussions about how to handle charset properly. What a server must provide to handle charsets correctly, which configuration for Apache is needed, what options must be set in php.ini to make PHP correctly working, which functions should be avoided when using PHP, which locales must be used and so on. So I want to give a short overview how to sail around common problems in a LAMP-setup.


Just to make sure the option CONFIG_NLS_UTF8 is set to y.


To make sure, newly created filenames are there in UTF-8 and in general VT-input is handled correctly, you have to choose a charset, which comes with an .UTF-8-suffix. For german feel free to choose de_DE.UTF-8. Make sure your glibc is provides this locales. To convert current names of files you can just use convmv. For a desktop you must also adjust the font and set a correct TTY-font but this could be ignored for a server which is just administrated via remote shell.

Webservers in general – focus on Apache

To make sure, the users input is UTF-8, the server has to deliver the correct Content-Type-header. Take a look at the output of wget -S http://usrportage.de, my weblog, which is hosted on Schokokeks.org, a properly configured server (sure!):
wget -S usrportage.de
—21:00:33— http://usrportage.de/ => `index.html’
Resolving usrportage.de…
Connecting to usrportage.de||:80… connected.
HTTP request sent, awaiting response… HTTP/1.1 200 OK Date: Thu, 13 Jul 2006 19:00:27 GMT Server: Apache X-Powered-By: PHP/5.1.4-pl0-gentoo with Hardening-Patch X-Blog: Serendipity Set-Cookie: PHPSESSID=9da2ded6522851ef8ddc3ebe7590b354; path=/ Expires: Thu, 19 Nov 1981 08:52:00 GMT Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0 Pragma: no-cache X-Serendipity-InterfaceLang: de X-FreeTag-Count: Array Connection: close Content-Type: text/html; charset=UTF-8
Length: unspecified [text/html]

[ <=> ] 65,557 250.71K/s

21:00:33 (250.19 KB/s) – `index.html’ saved [65557]

You see the header Content-Type: text/html; charset=UTF-8. (You can also the a bug in S9Y, which poorly casts an array, but anyway.) So your browser is notified, that it should send UTF-8 encoded data. That’s the whole secret. To configure Apache properly, make sure the directive AddDefaultCharset is set to UTF-8.

One thing at last: if you’re using AJAX-functions from Prototype for JavaScript-purposes, you have to reencode the string delivered by the AJAX-call. In PHP the following would work:
$string = utf8_encode( $_POST[‘key’] );


Before transacting any data, make sure your connection charset is set to UTF-8:
By the way: have I ever mentioned you should ever use mysql_real_escape_string() instead of mysql_escape_string()?


Just two rules: use mb_string-functions whereever it is possible, set the INI-setting default_charset to UTF-8 and – anyway – don’t use functions from the ereg-family also they have an mb_-Prefix. They aren’t binary-safe, that’s all you need to know.
Also make sure, your sources are UTF-8 encoded. Use iconv to correct those who are not.


I forgot to mention, that the functions htmlentities(), html_entity_decode() and htmlspecialchars() does not reflect PHPs default_charset-directive but assumes iso-8859-15 as the default charset, which is pretty annoying and should be considered as a bug, from my point of view. So you need to pass UTF-8 as the third parameter to the function to make sure it will work properly with Unicode.

Filed on 13-07-2006, 20:08 under , , , , , , , & no comments & no trackbacks

Way to dynamically create table names? 0

Assume I have the following stored procedure:

CREATE PROCEDURE addToList(IN string VARCHAR(128), IN tbl VARCHAR(128))
    DECLARE query TEXT;
    SET query = REPLACE( 'INSERT INTO __table__ SET string = ?', '__table__', tbl );
    PREPARE stmt FROM query;
    SET string = string;
    EXECUTE stmt USING string;

This will sadly not work, because it is not possible to prepare a query from a variable. A workaround is to write an endless long CASE-construct, which is not elegant and not flexible. Do you have any idea how to solve this problem?

Filed on 11-06-2006, 04:04 under , , & no comments & no trackbacks

MySQL Storend Procedure Programming 2

Falls mir jemand einen Gefallen tun möchte. Über dieses Buch würde ich mich sehr freuen.

Filed on 13-05-2006, 17:05 under , , , , & two comments & no trackbacks

PHP magic stuff 2

Sometimes I feel like being one of the little who like PHP. But, one things which brings me to hell currently is the mysqli-extension. Not that it is not easy to use, there is one strange thing I’m fucked up with. If I read an Image-file via file_get_contents() and want to write it to a database, It’s sensible to use a BLOB-field. So far so fine. I guessed I have to do it this way:

$image_data = file_get_contents( "my_image.png" );
$database = new mysqli( "host", "user", "password" );
$database->select_db( "my_db" );
if( $statement = $database->prepare( "INSERT INTO my_table SET my_blob = ?" ) ) {
   <strong>$statement->bind_param( "b", $image_data );</strong>
} else {

The important line is marked bold. It assignes my binary data as a BLOB to the placeholder »?« in mysqli::prepare(). So I guessed nothing wrong with it. But it seems to be wrong. If I use $statement->bind_param( "s", $image_data);, which means passing the value as a string, it works as expected. Is this a bug or can someone explain this strange behaviour to me?

I embarrass myself in my bugtracker entry as too lazy to read the entire documentation. This example cleared things up.

Filed on 12-05-2006, 08:08 under , , , , & two comments & no trackbacks

Ejabberd 1.1.1 in BreakMyGentoo 0

I’m proud to provide a new Ejabberd-ebuild to our users. Ejabberd 1.1.1 comes with an important new feature: it supports MySQL natively. I fixed up PostgreSQL-issues in the ebuild and provide now the native drivers for MySQL and PostgreSQL Also I’m happy to mention that NU2M, the company which is involved in Mabber and for which I’m currently working for, is going to release some really interesting Jabber-components, especially for Ejabberd, during the next weeks.


Filed on 07-05-2006, 02:02 under , , , , , , , , & no comments & no trackbacks