Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

base64 decode a mysql column before performing a WHERE

Tags:

mysql

base64

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

like image 457
Thomas Clayson Avatar asked Jun 28 '11 13:06

Thomas Clayson


People also ask

How do I use base64 decode?

To decode a file with contents that are base64 encoded, you simply provide the path of the file with the --decode flag. As with encoding files, the output will be a very long string of the original file. You may want to output stdout directly to a file.

Can base64 encoding be decoded?

In JavaScript there are two functions respectively for decoding and encoding Base64 strings: btoa() : creates a Base64-encoded ASCII string from a "string" of binary data ("btoa" should be read as "binary to ASCII"). atob() : decodes a Base64-encoded string ("atob" should be read as "ASCII to binary").

What is base64binary?

The base64 is a binary to a text encoding scheme that represents binary data in an ASCII string format. base64 is designed to carry data stored in binary format across the channels. It takes any form of data and transforms it into a long string of plain text.

What is b64 decode?

Base64 is an encoding and decoding technique used to convert binary data to an American Standard for Information Interchange (ASCII) text format, and vice versa.


1 Answers

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'

  • http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_from-base64

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

like image 125
Boy Baukema Avatar answered Sep 30 '22 15:09

Boy Baukema