How to store UNICODE in a non-UNICODE database

For a side project I am working on (Glad you asked! PostRecycler which makes the most of your social posts) I needed to all entry of unicode characters for just one field.

This left me in a bit of a quandary as I really didn’t want to have to convert the whole of my database to UTF-8 just so I could have this one column accept unicode characters.

Then I remembered something I had discovered in our companies web app which accepted unicode characters but I knew the database wasn’t unicode enabled. However, I knew that this column was encrypted and this proved to be the key.

If you are using a framework such as as I am with Codeigniter then this is incredibly easy. All I needed to do was the following before writing to the database. Of course I needed to decrypt when read too.

    $ciphertext = $this->encryption->encrypt($text);

Why does this work? Simply the encrypted text contains only basic alphanumeric characters and some symbols all of which appear within the ASCII character set and can be stored without resorting to changing the database structure.

If you want to test this out I suggest that you take a look at the big list of naughty strings and try copy and pasting some in to check it is working as expected.

It feels like a cheat doing it this way but it has the advantage of being quick AND you get encrypted data too!

Leave a Reply

Your email address will not be published. Required fields are marked *