Too Far Afield

Sometimes you need to store binary data inside a MySQL database. While there are various field types for binary data in MySQL, its handling of binary data has a share of different problems. As far as I know you might hit some bugs, if you’re using non-standard connection encodings or character sets. Also, binary data is a hell to escape properly. For example, if you store S????'x7?ma?X?UHMh? inside the MySQL database you might end up with ?????r??v ???????v later under certain conditions. This is not really ideal, to say the least.

The best thing to do is to convert the binary data to a string-based representation, e.g. a hexadecimal form or use a two-way encryption method like Base64. In fact, I recommend to use Base64. The drawback is, that a Base64 representation uses around 35% more space than the original binary representation. For example, in PHP you would do something like this:

1. // Encode binary data
2. $binary = base64_encode($binary);
3.  
4. // Decode binary data
5. $binary = base64_decode($binary);

Update: Changed the example data slightly to accommodate some screen readers, that require a valid feed. Thank you Jan for pointing this out.

Tags: base64, binary, mysql, php

Shameless plug: If this post was useful to you, please consider buying yourself something from one of my Amazon stores: US store, UK store, FR store, DE store, CA store. If you're not into Amazon, why not donate something to GNOME, Mozilla or Wikipedia? Thank you!

This entry was posted on Tuesday, April 28th, 2009 at 07:52 UTC and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.