How the FROM_BASE64() function works in Mariadb?

The MariaDB FROM_BASE64() function is used to decode a string that is encoded using the base64 encoding scheme. It is useful for situations where data needs to be transmitted over media that can only handle a limited character set, such as email or web forms. Base64 encoding ensures that the data can be safely transmitted without corruption or loss of information.

Syntax

The syntax for the MariaDB FROM_BASE64() function is as follows:

FROM_BASE64(str)

• str: A required parameter that represents the base64-encoded string to be decoded.

The function returns a binary string containing the decoded value of the input base64 string. If the input string contains invalid base64 characters, the function returns NULL.

Examples

Example 1: Decoding a simple base64 string

This example demonstrates how to decode a simple base64-encoded string using the FROM_BASE64() function.

SELECT FROM_BASE64('SGVsbG8gV29ybGQ=');

The output of this statement is:

Hello World

The base64 string 'SGVsbG8gV29ybGQ=' represents the plaintext 'Hello World', which the FROM_BASE64() function successfully decodes.

Example 2: Decoding a base64 string with special characters

This example shows how the FROM_BASE64() function can handle base64-encoded strings containing special characters.

SELECT FROM_BASE64('SGVsbG8sINCf0L7Rh9C10L3QuNGG0LjRjw==');

The output of this statement is:

Hello, Пример

The base64 string 'SGVsbG8sINCf0L7Rh9C10L3QuNGG0LjRjw==' represents the plaintext 'Hello, Пример' (which includes Cyrillic characters), and the FROM_BASE64() function correctly decodes it.

Example 3: Decoding a base64-encoded image

This example demonstrates how to decode a base64-encoded image using the FROM_BASE64() function.

DROP TABLE IF EXISTS images;
CREATE TABLE images (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), image LONGBLOB);

INSERT INTO images (name, image) VALUES
('Example Image', FROM_BASE64('iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAHWSURBVDjLpZM/aBNRGMVfO2lnZdJJnfQPrFQchIpFinSoIkhBOCgKgkPrUK0SxcWn0KFIXQJSCqXQlkJLWxCsWEGsiEKLQxEpwaiESn+bM2eJdze+3cWnAx/3vvd93rv3PVbTNIq6cqNh28Vfi5WnIzrZGgLEUqH7apLrrymT/iwlTdf7yP2HWcr0WoxXrFy/7k0VcyRkhYbwNrU1goO2iDIj5ZYuaDo8WsA8zcVs2U6C5O7tod2LxZIKq7taDLhfU2Dv3Y//ot/gMJ5SnZwiCb13edw8P3Jc4CVHKLbqmr6SFxlQzBRQFsQ/ehUrbo31sSVRsfKhT8GCEYH8xNI9gdnJqLiqNe4LlcD7goIpwCNmSRgvaMSkHRGmiIwAUkkDFTOFNMJQqSN6ErLNuNDIpoVAS4xrO7jH9nRNrhx/TkJGTDze/6yFD86FN/m2nPbInrOF7LRMd/qQQh+wlnlZjuLt83d4NoMrf0N0MYUxW4pNI4C+Ajv97tHI3YtDLgdSSgtd9uArIaiuCm5eLuPEjemhE3yjxu4oSWQk5ZopcXwmj/Mzhwo4OXml71w7et3xFiQr1FwdqMGTryXBC+HrT1zSqqQbIBPVIZl0uB9LSTv1fOW/gL8bh0lzfnLLTlQAAAAASUVORK5CYII='));

The output of this statement is:

Query OK, 1 row affected

In this example, we first create a table images with columns for storing an image name and the image data as a LONGBLOB. We then insert a base64-encoded string representing a PNG image into the image column using the FROM_BASE64() function. The query executes successfully, and the base64-encoded image data is correctly decoded and stored in the images table.

Example 4: Handling invalid base64 strings

This example demonstrates how the FROM_BASE64() function handles invalid base64-encoded strings.

SELECT FROM_BASE64('Invalid Base64 String');

The output of this statement is:

NULL

Since the input string 'Invalid Base64 String' is not a valid base64-encoded string, the FROM_BASE64() function returns NULL.

Example 5: Combining with other string functions

This example shows how the FROM_BASE64() function can be used in combination with other string functions.

SELECT CONCAT('Decoded string: ', CAST(FROM_BASE64('SGVsbG8gV29ybGQ=') AS CHAR));

The output of this statement is:

Decoded string: Hello World

Here, we use the CONCAT() function to concatenate the string 'Decoded string: ' with the result of decoding the base64 string 'SGVsbG8gV29ybGQ=' (which is 'Hello World'). The CAST() function is used to convert the binary output of FROM_BASE64() to a character string.

Related Functions

The following are some functions related to the MariaDB FROM_BASE64() function:

• MariaDB TO_BASE64() function is used to encode a string using the base64 encoding scheme.
• MariaDB HEX() function is used to encode a string using the hexadecimal encoding scheme.
• MariaDB UNHEX() function is used to decode a hexadecimal-encoded string.

Conclusion

The MariaDB FROM_BASE64() function is a useful tool for decoding base64-encoded strings, which can be beneficial in various scenarios where data needs to be transmitted over limited character set media. It provides a convenient way to handle and manipulate encoded data within a database environment. By understanding its syntax, usage, and related functions, developers can effectively work with base64-encoded data in their MariaDB applications.