Last active
August 16, 2018 11:58
-
-
Save bbars/d7d16c22da7c414e5f2d5fc812a01ac0 to your computer and use it in GitHub Desktop.
MySQL base64 custom implementation (as of version 5.6+ there are native ones: https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_from-base64)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- drop function if exists base64_encode; | |
create function base64_encode(_data blob) | |
returns text | |
begin | |
declare _alphabet char(64) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'; | |
declare _lim int unsigned default length(_data); | |
declare _i int unsigned default 0; | |
declare _chk3 char(6) default ''; | |
declare _chk3int int default 0; | |
declare _enc text default ''; | |
while _i < _lim do | |
set _chk3 = rpad(hex(binary substr(_data, _i + 1, 3)), 6, '0'); | |
set _chk3int = conv(_chk3, 16, 10); | |
set _enc = concat( | |
_enc | |
, substr(_alphabet, ((_chk3int >> 18) & 63) + 1, 1) | |
, if (_lim-_i > 0, substr(_alphabet, ((_chk3int >> 12) & 63) + 1, 1), '=') | |
, if (_lim-_i > 1, substr(_alphabet, ((_chk3int >> 6) & 63) + 1, 1), '=') | |
, if (_lim-_i > 2, substr(_alphabet, ((_chk3int >> 0) & 63) + 1, 1), '=') | |
); | |
set _i = _i + 3; | |
end while; | |
return _enc; | |
end; | |
-- drop function if exists base64_decode; | |
create function base64_decode(_enc text) | |
returns blob | |
begin | |
declare _alphabet char(64) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'; | |
declare _lim int unsigned default 0; | |
declare _i int unsigned default 0; | |
declare _chr1byte tinyint default 0; | |
declare _chk4int int default 0; | |
declare _chk4int_bits tinyint default 0; | |
declare _dec blob default ''; | |
declare _rem tinyint default 0; | |
set _enc = trim(_enc); | |
set _rem = if(right(_enc, 3) = '===', 3, if(right(_enc, 2) = '==', 2, if(right(_enc, 1) = '=', 1, 0))); | |
set _lim = length(_enc) - _rem; | |
while _i < _lim | |
do | |
set _chr1byte = locate(substr(_enc, _i + 1, 1), binary _alphabet) - 1; | |
if (_chr1byte > -1) | |
then | |
set _chk4int = (_chk4int << 6) | _chr1byte; | |
set _chk4int_bits = _chk4int_bits + 6; | |
if (_chk4int_bits = 24 or _i = _lim-1) | |
then | |
if (_i = _lim-1 and _chk4int_bits != 24) | |
then | |
set _chk4int = _chk4int << 0; | |
end if; | |
set _dec = concat( | |
_dec | |
, char((_chk4int >> (_chk4int_bits - 8)) & 0xff) | |
, if(_chk4int_bits > 8, char((_chk4int >> (_chk4int_bits - 16)) & 0xff), '\0') | |
, if(_chk4int_bits > 16, char((_chk4int >> (_chk4int_bits - 24)) & 0xff), '\0') | |
); | |
set _chk4int = 0; | |
set _chk4int_bits = 0; | |
end if; | |
end if; | |
set _i = _i + 1; | |
end while; | |
return substr(_dec, 1, length(_dec) - _rem); | |
end; | |
-- example: | |
select base64_encode('123') as `123`, | |
base64_encode('1231') as `1231`, | |
base64_encode('12312') as `12312`, | |
base64_encode('123123') as `123123`, | |
base64_encode('0123456789') as `0123456789`, | |
base64_encode('ололо') as `ололо`, | |
base64_encode('ёлка') as `ёлка` | |
union all | |
select base64_decode(base64_encode('123')) as `123`, | |
base64_decode(base64_encode('1231')) as `1231`, | |
base64_decode(base64_encode('12312')) as `12312`, | |
base64_decode(base64_encode('123123')) as `123123`, | |
base64_decode(base64_encode('0123456789')) as `0123456789`, | |
base64_decode(base64_encode('ололо')) as `ололо`, | |
convert(base64_decode(base64_encode('ёлка')) using utf8) as `ёлка`; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I got a syntax error on line 5 trying on MySQL 5.5.59