CODEWITHSUNDEEP
mysqlbase64
Thomas Clayson
Thomas Clayson

Reputation: 29935

base64 decode a mysql column before performing a WHERE

Basically - its a long story - but I have a field in a database that is encoded as a base64 string.

EG: this is stored in the database:

YToyOntzOjIwOiJUeXBlX29mX29yZ2FuaXNhdGlvbiI7czoyMDoiTWVtYmVyIG9mIFBhcmxpYW1lbnQiO3M6ODoiUG9zdGNvZGUiO3M6NzoiUEUxIDFKQSI7fQ==

Which equals this:

a:2:{s:20:"Type_of_organisation";s:20:"Member of Parliament";s:8:"Postcode";s:7:"#postcode#";}

What I want to be able to do is select where this string LIKE '%Member of Parliament%'. Is there any way to base64 decode a mysql column before performing a WHERE?

eg: SELECT * FROM table WHERE base64_decode(column) LIKE '%Member of Parliament%'

Thanks

Upvotes: 20

Views: 64456

Answers (1)

Boy Baukema
Boy Baukema

Reputation: 2970

If you're using MySQL 5.6.1 or higher you can use the FROM_BASE64() function:

Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string. The result is NULL if the argument is NULL or not a valid base-64 string. See the description of TO_BASE64() for details about the encoding and decoding rules.

This function was added in MySQL 5.6.1.

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc')); -> 'JWJj', 'abc'

Otherwise you can use the User Defined Function from: https://github.com/y-ken/mysql-udf-base64

Upvotes: 39

Related Questions