mysql: base64



  1. -- base64.sql - MySQL base64 encoding/decoding functions
  2. -- Copyright (C) 2006 Ian Gulliver
  3. --
  4. -- This program is free software; you can redistribute it and/or modify
  5. -- it under the terms of version 2 of the GNU General Public License as
  6. -- published by the Free Software Foundation.
  7. --
  8. -- This program is distributed in the hope that it will be useful,
  9. -- but WITHOUT ANY WARRANTY; without even the implied warranty of
  10. -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  11. -- GNU General Public License for more details.
  12. --
  13. -- You should have received a copy of the GNU General Public License
  14. -- along with this program; if not, write to the Free Software
  15. -- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  16.  
  17. delimiter |
  18.  
  19. DROP TABLE IF EXISTS base64_data |
  20. CREATE TABLE base64_data (c CHAR(1) BINARY, val TINYINT) |
  21. INSERT INTO base64_data VALUES
  22.       ('A',0), ('B',1), ('C',2), ('D',3), ('E',4), ('F',5), ('G',6), ('H',7), ('I',8), ('J',9),
  23.        ('K',10), ('L',11), ('M',12), ('N',13), ('O',14), ('P',15), ('Q',16), ('R',17), ('S',18), ('T',19),
  24.  ('U',20), ('V',21), ('W',22), ('X',23), ('Y',24), ('Z',25), ('a',26), ('b',27), ('c',28), ('d',29),
  25.      ('e',30), ('f',31), ('g',32), ('h',33), ('i',34), ('j',35), ('k',36), ('l',37), ('m',38), ('n',39),
  26.  ('o',40), ('p',41), ('q',42), ('r',43), ('s',44), ('t',45), ('u',46), ('v',47), ('w',48), ('x',49),
  27.      ('y',50), ('z',51), ('0',52), ('1',53), ('2',54), ('3',55), ('4',56), ('5',57), ('6',58), ('7',59),
  28.  ('8',60), ('9',61), ('+',62), ('/',63), ('=',0) |
  29.  
  30.  
  31. DROP FUNCTION IF EXISTS BASE64_DECODE |
  32. CREATE FUNCTION BASE64_DECODE (input BLOB)
  33.         RETURNS BLOB
  34.         CONTAINS SQL
  35.         DETERMINISTIC
  36.        SQL SECURITY INVOKER
  37. BEGIN
  38.    DECLARE ret BLOB DEFAULT '';
  39.       DECLARE done TINYINT DEFAULT 0;
  40.  
  41.  IF input IS NULL THEN
  42.                RETURN NULL;
  43.         END IF;
  44.  
  45. each_block:
  46.   WHILE NOT done DO BEGIN
  47.              DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
  48.           DECLARE in_count TINYINT DEFAULT 0;
  49.          DECLARE out_count TINYINT DEFAULT 3;
  50.  
  51. each_input_char:
  52.                 WHILE in_count < 4 DO BEGIN
  53.                  DECLARE first_char CHAR(1);
  54.      
  55.                     IF LENGTH(input) = 0 THEN
  56.                            RETURN ret;
  57.                  END IF;
  58.      
  59.                     SET first_char = SUBSTRING(input,1,1);
  60.                       SET input = SUBSTRING(input,2);
  61.  
  62.                     BEGIN
  63.                                DECLARE tempval TINYINT UNSIGNED;
  64.                            DECLARE error TINYINT DEFAULT 0;
  65.                             DECLARE base64_getval CURSOR FOR SELECT val FROM base64_data WHERE c = first_char;
  66.                               DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET error = 1;
  67.      
  68.                             OPEN base64_getval;
  69.                          FETCH base64_getval INTO tempval;
  70.                                CLOSE base64_getval;
  71.  
  72.                             IF error THEN
  73.                                        ITERATE each_input_char;
  74.                             END IF;
  75.  
  76.                          SET accum_value = (accum_value << 6) + tempval;
  77.                  END;
  78.  
  79.                     SET in_count = in_count + 1;
  80.  
  81.                     IF first_char = '=' THEN
  82.                           SET done = 1;
  83.                           SET out_count = out_count - 1;
  84.                        END IF;
  85.              END; END WHILE;
  86.  
  87.          -- We've now accumulated 24 bits; deaccumulate into bytes
  88.  
  89.               -- We have to work from the left, so use the third byte position and shift left
  90.          WHILE out_count > 0 DO BEGIN
  91.                         SET ret = CONCAT(ret,CHAR((accum_value & 0xff0000) >> 16));
  92.                  SET out_count = out_count - 1;
  93.                       SET accum_value = (accum_value << 8) & 0xffffff;
  94.                 END; END WHILE;
  95.      
  96.     END; END WHILE;
  97.  
  98.  RETURN ret;
  99. END |
  100.  
  101. DROP FUNCTION IF EXISTS BASE64_ENCODE |
  102. CREATE FUNCTION BASE64_ENCODE (input BLOB)
  103.    RETURNS BLOB
  104.         CONTAINS SQL
  105.     DETERMINISTIC
  106.        SQL SECURITY INVOKER
  107. BEGIN
  108.        DECLARE ret BLOB DEFAULT '';
  109.       DECLARE done TINYINT DEFAULT 0;
  110.  
  111.  IF input IS NULL THEN
  112.                RETURN NULL;
  113.         END IF;
  114.  
  115. each_block:
  116.   WHILE NOT done DO BEGIN
  117.          DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
  118.               DECLARE in_count TINYINT DEFAULT 0;
  119.          DECLARE out_count TINYINT;
  120.  
  121. each_input_char:
  122.           WHILE in_count < 3 DO BEGIN
  123.             DECLARE first_char CHAR(1);
  124.  
  125.                     IF LENGTH(input) = 0 THEN
  126.                            SET done = 1;
  127.                                SET accum_value = accum_value << (8 * (3 - in_count));
  128.                               LEAVE each_input_char;
  129.                   END IF;
  130.      
  131.                     SET first_char = SUBSTRING(input,1,1);
  132.                       SET input = SUBSTRING(input,2);
  133.      
  134.                     SET accum_value = (accum_value << 8) + ASCII(first_char);
  135.  
  136.                    SET in_count = in_count + 1;
  137.                 END; END WHILE;
  138.  
  139.          -- We've now accumulated 24 bits; deaccumulate into base64 characters
  140.  
  141.           -- We have to work from the left, so use the third byte position and shift left
  142.          CASE
  143.                         WHEN in_count = 3 THEN SET out_count = 4;
  144.                    WHEN in_count = 2 THEN SET out_count = 3;
  145.                    WHEN in_count = 1 THEN SET out_count = 2;
  146.                        ELSE RETURN ret;
  147.             END CASE;
  148.  
  149.                WHILE out_count > 0 DO BEGIN
  150.                         BEGIN
  151.                                DECLARE out_char CHAR(1);
  152.                            DECLARE base64_getval CURSOR FOR SELECT c FROM base64_data WHERE val = (accum_value >> 18);
  153.  
  154.                          OPEN base64_getval;
  155.                          FETCH base64_getval INTO out_char;
  156.                           CLOSE base64_getval;
  157.  
  158.  
  159.                                 SET ret = CONCAT(ret,out_char);
  160.                              SET out_count = out_count - 1;
  161.                               SET accum_value = accum_value << 6 & 0xffffff;
  162.                       END;
  163.            END; END WHILE;
  164.  
  165.           CASE
  166.                         WHEN in_count = 2 THEN SET ret = CONCAT(ret,'=');
  167.                  WHEN in_count = 1 THEN SET ret = CONCAT(ret,'==');
  168.                         ELSE BEGIN END;
  169.              END CASE;
  170.        
  171.     END; END WHILE;
  172.  
  173.  RETURN ret;
  174. END |
  175.  

Posted by GT at 29 Jan 2014, 19:32:03 CET
Language: text