MySQL - FROM_BASE64() Function

The MySQL FROM_BASE64() function accepts a string value which is encoded using base-64 encoded format, decodes it as a binary string and returns the result. The Base64 encoding is a way to represent binary data using only printable ASCII characters.

If the argument passed to this function is not a valid base-64 string or, NULL, it returns the value NULL. If you invoke this function locally, the resultant value will be a hexadecimal notation.

Syntax

Following is the syntax of MySQL FROM_BASE64() function −

FROM_BASE64(str)

Parameters

This function takes a base64-encoded string value as a parameter.

Return Value

This function returns the decoded binary data of the given string.

Example

In the following example, we decode a Base64-encoded string and display the result in hexadecimal format −

SELECT FROM_BASE64('VHV0b3JpYWxzcG9pbnQ=');

Following is the output of the above code −

FROM_BASE64('VHV0b3JpYWxzcG9pbnQ=')
0x5475746F7269616C73706F696E74

If you disable the --binary-as-hex value, the result of the above query will be as follows −

SELECT FROM_BASE64('VHV0b3JpYWxzcG9pbnQ=');

Output of the above code is as shown below −

FROM_BASE64('VHV0b3JpYWxzcG9pbnQ=')
Tutorialspoint

Example

Here is another example, where we decode a shorter Base64 string −

SELECT FROM_BASE64('dGVzdA==');

The output obtained is as follows −

FROM_BASE64('dGVzdA==')
0x74657374

If you disable the --binary-as-hex value the result of the above query will be as follows −

SELECT FROM_BASE64('dGVzdA==');

The result produced is as follows −

FROM_BASE64('dGVzdA==')
0test

Example

You can also use the TO_BASE64() and FROM_BASE64() functions together to encode and then decode a string −

SELECT TO_BASE64('Hello how are you'),
FROM_BASE64(TO_BASE64('Hello how are you'));

We get the output as follows −

TO_BASE64('Hello how are you') FROM_BASE64(TO_BASE64('Hello how are you'))
SGVsbG8gaG93IGFyZSB5b3U= 0x48656C6C6F20686F772061726520796F75

If you disable the --binary-as-hex value, the result of the above query will be as follows &mnus;

SELECT TO_BASE64('Hello how are you'),
FROM_BASE64(TO_BASE64('Hello how are you'));

Following is the output of the above code −

TO_BASE64('Hello how are you') FROM_BASE64(TO_BASE64('Hello how are you'))
SGVsbG8gaG93IGFyZSB5b3U= Hello how are you

Example

If you pass NULL or invalid string as an argument, this function returns NULL −

SELECT FROM_BASE64(NULL);

The result produced is as shown below −

FROM_BASE64(NULL)
0x

Here, we are passing 'Invalid String' as an argument to the FROM_BASE64() function −

SELECT FROM_BASE64('Invalid string');

The result produced is as shown below −

FROM_BASE64('Invalid string')
0x

If you disable the --binary-as-hex value, the result of the above query will be as follows −

SELECT FROM_BASE64(NULL);

The result produced is as follows −

FROM_BASE64(NULL)
NULL

Example

You can also pass a column name of a table as a parameter to this function and decode the values in it.

Let us create a table named "DATA" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE DATA (
   Name VARCHAR(15), 
   UserID INT, 
   Val VARCHAR(50)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO DATA VALUES 
('Raju', 1001, 'S2V5MWFiYzEyMw'),
('Rahman', 1002, 'S2V5MWFiYzEyMw=='),
('Robert', 1002, 'a2V5Mw==');

The DATA obtained is as follows −

Name UserID Val
Raju 1001 S2V5MWFiYzEyMw
Rahman 1002 S2V5MWFiYzEyMw==
Robert 1002 a2V5Mw==

Following query decodes the values of the column 'Val' from Base64 strings to binary strings −

SELECT Name, UserID, Val, FROM_BASE64(Val) 
FROM DATA;

After executing the above code, we get the following output −

Name UserID Val FROM_BASE64(Val)
Raju 1001 S2V5MWFiYzEyMw 0x4B657931616263313233
Rahman 1002 S2V5MWFiYzEyMw== 0x4B657931616263313233
Robert 1002 a2V5Mw== 0x6B657933

If you disable the --binary-as-hex value, the result of the above query will be as follows −

SELECT Name, UserID, Val, FROM_BASE64(Val) 
FROM DATA;

Output of the above code is as follows −

Name UserID Val FROM_BASE64(Val)
Raju 1001 S2V5MWFiYzEyMw key1abc123
Rahman 1002 S2V5MWFiYzEyMw== key2abc
Robert 1002 a2V5Mw== key3
mysql-from-base64-function.htm
Advertisements