base64 encode in MySQL

Question

I want to select a blob col from one table  base64 encode it and insert it into another tables  Is there any way to do this without round tripping the data out of the DB and through my app

User · Answer

create table encrypt username varchar 20  password varbinary 200    insert into encrypt values  raju  aes encrypt  kumar   key    select   cast aes decrypt password  key   as char 40   from encrypt where username  raju

User · Answer

For those interested  these are the only alternatives so far   1  Using these Functions    http   wi-fizzle com downloads base64 sql  2  If you already have the sys eval UDF   Linux  you can do this   sys eval CONCAT  echo    myField      base64       The first method is known to be slow  The problem with the second one  is that the encoding is actually happening  outside  MySQL  which can have encoding problems  besides the security risks that you are adding with sys   functions    Unfortunately there is no UDF compiled version  which should be faster  nor a native support in MySQL  Posgresql supports it     It seems that the MySQL development team are not interested in implement it as this function already exists in other languages  which seems pretty silly to me

User · Answer

If you need this for  lt  5 6  I tripped across this UDF which seems to work fine   https   github com y-ken mysql-udf-base64

User · Answer

Looks like no  though it was requested  and there   s a UDF for it   Edit  Or there   s    this  Ugh

User · Answer

I was looking for the same thing and I ve just seen that MySQL 5 6 has a couple of new string functions supporting this functionality  TO BASE64 and FROM BASE64

User · Answer

Functions from http   wi-fizzle com downloads base64 sql contain some error when in encoded string are 32-byte  space   ex BASE64 ENCODE CONCAT CHAR 15   CHAR 32     Here is corrected function  DELIMITER     USE  YOUR DATABASE     DROP TABLE IF EXISTS core base64 data   CREATE TABLE core base64 data  c CHAR 1  BINARY  val TINYINT    INSERT INTO core base64 data VALUES    A  0     B  1     C  2     D  3     E  4     F  5     G  6     H  7     I  8     J  9     K  10     L  11     M  12     N  13     O  14     P  15     Q  16     R  17     S  18     T  19     U  20     V  21     W  22     X  23     Y  24     Z  25     a  26     b  27     c  28     d  29     e  30     f  31     g  32     h  33     i  34     j  35     k  36     l  37     m  38     n  39     o  40     p  41     q  42     r  43     s  44     t  45     u  46     v  47     w  48     x  49     y  50     z  51     0  52     1  53     2  54     3  55     4  56     5  57     6  58     7  59     8  60     9  61        62        63        0      DROP FUNCTION IF EXISTS  BASE64 ENCODE     CREATE DEFINER  YOUR DATABASE      FUNCTION  BASE64 ENCODE  input BLOB  RETURNS BLOB     DETERMINISTIC     SQL SECURITY INVOKER BEGIN     DECLARE ret BLOB DEFAULT         DECLARE done TINYINT DEFAULT 0      IF input IS NULL THEN         RETURN NULL      END IF  each block      WHILE NOT done DO BEGIN         DECLARE accum value BIGINT UNSIGNED DEFAULT 0          DECLARE in count TINYINT DEFAULT 0          DECLARE out count TINYINT  each input char          WHILE in count  lt  3 DO BEGIN             DECLARE first char BLOB 1                IF LENGTH input    0 THEN                 SET done   1                  SET accum value   accum value  lt  lt   8    3 - in count                    LEAVE each input char              END IF               SET first char   SUBSTRING input 1 1               SET input   SUBSTRING input 2                SET accum value    accum value  lt  lt  8    ASCII first char               SET in count   in count   1          END  END WHILE           -- We ve now accumulated 24 bits  deaccumulate into base64 characters         -- We have to work from the left  so use the third byte position and shift left         CASE             WHEN in count   3 THEN SET out count   4              WHEN in count   2 THEN SET out count   3              WHEN in count   1 THEN SET out count   2              ELSE RETURN ret          END CASE           WHILE out count  gt  0 DO BEGIN             BEGIN                 DECLARE out char CHAR 1                   DECLARE base64 getval CURSOR FOR SELECT c FROM core base64 data WHERE val    accum value  gt  gt  18                   OPEN base64 getval                  FETCH base64 getval INTO out char                  CLOSE base64 getval                  SET ret   CONCAT ret out char                   SET out count   out count - 1                  SET accum value   accum value  lt  lt  6  amp  0xffffff              END          END  END WHILE          CASE             WHEN in count   2 THEN SET ret   CONCAT ret                   WHEN in count   1 THEN SET ret   CONCAT ret                    ELSE BEGIN END          END CASE       END  END WHILE      RETURN ret  END    DELIMITER

User · Answer

SELECT  id   name   TO BASE64 content  FROM  db   upload    this will convert the blob value from content column to base64 string  Then you can do with this string whatever you want even insert it into another table

User · Answer

Looks like no  though it was requested  and there   s a UDF for it   Edit  Or there   s    this  Ugh

User · Answer

Looks like no  though it was requested  and there   s a UDF for it   Edit  Or there   s    this  Ugh

User · Answer

If you need this for  lt  5 6  I tripped across this UDF which seems to work fine   https   github com y-ken mysql-udf-base64

User · Answer

create table encrypt username varchar 20  password varbinary 200    insert into encrypt values  raju  aes encrypt  kumar   key    select   cast aes decrypt password  key   as char 40   from encrypt where username  raju

User · Answer

Yet another custom implementation that doesn t require support table   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  lt   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  gt  gt  18   amp  63    1  1                if   lim- i  gt  0  substr  alphabet     chk3int  gt  gt  12   amp  63    1  1                      if   lim- i  gt  1  substr  alphabet     chk3int  gt  gt   6   amp  63    1  1                      if   lim- i  gt  2  substr  alphabet     chk3int  gt  gt   0   amp  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  lt   lim     do         set  chr1byte   locate substr  enc   i   1  1   binary  alphabet  - 1          if   chr1byte  gt  -1          then             set  chk4int     chk4int  lt  lt  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  lt  lt  0                  end if                   set  dec   concat                       dec                                              char   chk4int  gt  gt    chk4int bits -  8    amp  0xff                        if  chk4int bits  gt   8  char   chk4int  gt  gt    chk4int bits - 16    amp  0xff     0                         if  chk4int bits  gt  16  char   chk4int  gt  gt    chk4int bits - 24    amp  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    Gist  You should convert charset after decoding  convert base64 decode base64 encode          using utf8

User · Answer

I was looking for the same thing and I ve just seen that MySQL 5 6 has a couple of new string functions supporting this functionality  TO BASE64 and FROM BASE64

User · Answer

For those interested  these are the only alternatives so far   1  Using these Functions    http   wi-fizzle com downloads base64 sql  2  If you already have the sys eval UDF   Linux  you can do this   sys eval CONCAT  echo    myField      base64       The first method is known to be slow  The problem with the second one  is that the encoding is actually happening  outside  MySQL  which can have encoding problems  besides the security risks that you are adding with sys   functions    Unfortunately there is no UDF compiled version  which should be faster  nor a native support in MySQL  Posgresql supports it     It seems that the MySQL development team are not interested in implement it as this function already exists in other languages  which seems pretty silly to me

User · Answer

Yet another custom implementation that doesn t require support table   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  lt   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  gt  gt  18   amp  63    1  1                if   lim- i  gt  0  substr  alphabet     chk3int  gt  gt  12   amp  63    1  1                      if   lim- i  gt  1  substr  alphabet     chk3int  gt  gt   6   amp  63    1  1                      if   lim- i  gt  2  substr  alphabet     chk3int  gt  gt   0   amp  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  lt   lim     do         set  chr1byte   locate substr  enc   i   1  1   binary  alphabet  - 1          if   chr1byte  gt  -1          then             set  chk4int     chk4int  lt  lt  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  lt  lt  0                  end if                   set  dec   concat                       dec                                              char   chk4int  gt  gt    chk4int bits -  8    amp  0xff                        if  chk4int bits  gt   8  char   chk4int  gt  gt    chk4int bits - 16    amp  0xff     0                         if  chk4int bits  gt  16  char   chk4int  gt  gt    chk4int bits - 24    amp  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    Gist  You should convert charset after decoding  convert base64 decode base64 encode          using utf8

User · Answer

Functions from http   wi-fizzle com downloads base64 sql contain some error when in encoded string are 32-byte  space   ex BASE64 ENCODE CONCAT CHAR 15   CHAR 32     Here is corrected function  DELIMITER     USE  YOUR DATABASE     DROP TABLE IF EXISTS core base64 data   CREATE TABLE core base64 data  c CHAR 1  BINARY  val TINYINT    INSERT INTO core base64 data VALUES    A  0     B  1     C  2     D  3     E  4     F  5     G  6     H  7     I  8     J  9     K  10     L  11     M  12     N  13     O  14     P  15     Q  16     R  17     S  18     T  19     U  20     V  21     W  22     X  23     Y  24     Z  25     a  26     b  27     c  28     d  29     e  30     f  31     g  32     h  33     i  34     j  35     k  36     l  37     m  38     n  39     o  40     p  41     q  42     r  43     s  44     t  45     u  46     v  47     w  48     x  49     y  50     z  51     0  52     1  53     2  54     3  55     4  56     5  57     6  58     7  59     8  60     9  61        62        63        0      DROP FUNCTION IF EXISTS  BASE64 ENCODE     CREATE DEFINER  YOUR DATABASE      FUNCTION  BASE64 ENCODE  input BLOB  RETURNS BLOB     DETERMINISTIC     SQL SECURITY INVOKER BEGIN     DECLARE ret BLOB DEFAULT         DECLARE done TINYINT DEFAULT 0      IF input IS NULL THEN         RETURN NULL      END IF  each block      WHILE NOT done DO BEGIN         DECLARE accum value BIGINT UNSIGNED DEFAULT 0          DECLARE in count TINYINT DEFAULT 0          DECLARE out count TINYINT  each input char          WHILE in count  lt  3 DO BEGIN             DECLARE first char BLOB 1                IF LENGTH input    0 THEN                 SET done   1                  SET accum value   accum value  lt  lt   8    3 - in count                    LEAVE each input char              END IF               SET first char   SUBSTRING input 1 1               SET input   SUBSTRING input 2                SET accum value    accum value  lt  lt  8    ASCII first char               SET in count   in count   1          END  END WHILE           -- We ve now accumulated 24 bits  deaccumulate into base64 characters         -- We have to work from the left  so use the third byte position and shift left         CASE             WHEN in count   3 THEN SET out count   4              WHEN in count   2 THEN SET out count   3              WHEN in count   1 THEN SET out count   2              ELSE RETURN ret          END CASE           WHILE out count  gt  0 DO BEGIN             BEGIN                 DECLARE out char CHAR 1                   DECLARE base64 getval CURSOR FOR SELECT c FROM core base64 data WHERE val    accum value  gt  gt  18                   OPEN base64 getval                  FETCH base64 getval INTO out char                  CLOSE base64 getval                  SET ret   CONCAT ret out char                   SET out count   out count - 1                  SET accum value   accum value  lt  lt  6  amp  0xffffff              END          END  END WHILE          CASE             WHEN in count   2 THEN SET ret   CONCAT ret                   WHEN in count   1 THEN SET ret   CONCAT ret                    ELSE BEGIN END          END CASE       END  END WHILE      RETURN ret  END    DELIMITER

User · Answer

SELECT  id   name   TO BASE64 content  FROM  db   upload    this will convert the blob value from content column to base64 string  Then you can do with this string whatever you want even insert it into another table

User · Answer

Looks like no  though it was requested  and there   s a UDF for it   Edit  Or there   s    this  Ugh

[mysql] base64 encode in MySQL

Examples related to mysql

Examples related to base64