FROM_BASE64 custom function for old MySQL

from_base64.sql

DROP FUNCTION IF EXISTS FROM_BASE64;

DELIMITER ;;

CREATE FUNCTION FROM_BASE64 (base64 BLOB) RETURNS BLOB DETERMINISTIC SQL SECURITY INVOKER
BEGIN

  DECLARE plain BLOB DEFAULT '';
  DECLARE o INTEGER DEFAULT 0;

  IF base64 IS NULL THEN RETURN NULL;
  END IF;

  WHILE LENGTH(base64) > 0 DO BEGIN

    DECLARE i INTEGER DEFAULT 0;
    DECLARE packed INTEGER DEFAULT 0;

    IF LENGTH(base64) < 4 THEN RETURN NULL;
    END IF;

    SET o = o + 3;

    REPEAT BEGIN

      DECLARE v INTEGER DEFAULT ASCII(base64);

      SET v = CASE
        WHEN v BETWEEN 0x30 AND 0x39 THEN v + 4
        WHEN v BETWEEN 0x41 AND 0x5A THEN v - 65
        WHEN v BETWEEN 0x61 AND 0x7A THEN v - 71
        WHEN v = 0x2B THEN 62
        WHEN v = 0x2F THEN 63
        WHEN v = 0x3D THEN -1
        ELSE -2
      END;

      IF v = -2 THEN RETURN NULL;
      END IF;

      IF v = -1 THEN
        SET v = 0;
        SET o = o - 1;
      END IF;

      SET packed = (packed << 6) | v;
      SET base64 = SUBSTRING(base64 FROM 2);
      SET i = i + 1;

    END; UNTIL i >= 4 END REPEAT;

    SET plain = CONCAT(plain, CHAR(packed >> 16, (packed >> 8) & 0xFF, packed & 0xFF));

  END; END WHILE;

  RETURN SUBSTRING(plain FROM 1 FOR o);

END

;;

DELIMITER ;