Appropriate way to convert unsanitized varchar from Base64 to Hex within MariaDB / MySQL

Question

I have a table with a column that contains varchars. The values within that column should be Base64 encoded, however I want to save them as HEX instead.

There is a chance that the values contained in the column are not Base64 encoded at all but I also know that IF they are Base64 encoded they WILL be 12 characters long.

According to MariaDB documentation FROM_BASE64 returns null if the varchar to be converted is either null or invalid Base64.

Knowing that I wrote the following SELECT statement

SELECT column_name,
HEX(FROM_BASE64(column_name)) AS column_name_converted
FROM table_name tn 
WHERE CHAR_LENGTH(column_name) = 12 AND FROM_BASE64(column_name) IS NOT NULL

This SELECT works as expected.

However when I try to write an UPDATE statement, I get an error "Bad base64 data as position 8" - Position 8 in this case is a "#" within the varchar which is not a valid symbol.

The result I am trying to achieve: Convert only the values that are valid Base64 AND 12 characters long into HEX.

For example:

UPDATE table_name
SET column_name =
    CASE
        WHEN FROM_BASE64(column_name) IS NOT NULL
        THEN HEX(FROM_BASE64(column_name))
        ELSE column_name
    END
WHERE CHARACTER_LENGTH(column_name) = 12 

UPDATE table_name 
SET column_name  =
HEX(FROM_BASE64(column_name))
WHERE CHAR_LENGTH(column_name) = 12 AND FROM_BASE64(column_name) IS NOT NULL

None of these work. SQL execution gets aborted with the above error despite SELECT working flawlessly.

Sanitizing the data before execution is not possible.

-----POTENTIAL SOLUTION-----

Edit: Seems like I managed to make it work by filtering invalid results using Regex. Are there any cleaner ways of accomplishing the same result?

UPDATE table_name
SET column_name =
    CASE
        WHEN column_name REGEXP '^(?:[A-Za-z0-9+/]{4})*(?:[A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=|[A-Za-z0-9+/]{4})$'
        THEN HEX(FROM_BASE64(column_name))
        ELSE column_name
    END
WHERE CHARACTER_LENGTH(column_name) = 12

Answer 1

The STRICT_TRANS_TABLES sql_mode (which should be used wherever possible) unfortunately promotes some warnings to errors when in an insert/update/etc, even where you guard against it with checking the results of a function call.

You could temporarily disable it before your update:

set session sql_mode=replace(@@sql_mode, 'STRICT_TRANS_TABLES', '');
update ...
set session sql_mode=concat(@@sql_mode, ',STRICT_TRANS_TABLES');

but I prefer using a regexp (as you suggest).