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
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.
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").
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With