Skip to content

Instantly share code, notes, and snippets.

@bbars
Last active August 16, 2018 11:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save bbars/d7d16c22da7c414e5f2d5fc812a01ac0 to your computer and use it in GitHub Desktop.
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)
-- 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 `ёлка`;
@creativetags
Copy link

I got a syntax error on line 5 trying on MySQL 5.5.59

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment