· 7 years ago · Sep 26, 2018, 05:56 PM
1SET FOREIGN_KEY_CHECKS=0;
2
3-- ----------------------------
4-- Table structure for base64_data
5-- ----------------------------
6DROP TABLE IF EXISTS `base64_data`;
7CREATE TABLE `base64_data` (
8 `c` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
9 `val` tinyint(4) DEFAULT NULL
10) ENGINE=MyISAM DEFAULT CHARSET=utf8;
11
12-- ----------------------------
13-- Records of base64_data
14-- ----------------------------
15INSERT INTO `base64_data` VALUES ('A', '0');
16INSERT INTO `base64_data` VALUES ('B', '1');
17INSERT INTO `base64_data` VALUES ('C', '2');
18INSERT INTO `base64_data` VALUES ('D', '3');
19INSERT INTO `base64_data` VALUES ('E', '4');
20INSERT INTO `base64_data` VALUES ('F', '5');
21INSERT INTO `base64_data` VALUES ('G', '6');
22INSERT INTO `base64_data` VALUES ('H', '7');
23INSERT INTO `base64_data` VALUES ('I', '8');
24INSERT INTO `base64_data` VALUES ('J', '9');
25INSERT INTO `base64_data` VALUES ('K', '10');
26INSERT INTO `base64_data` VALUES ('L', '11');
27INSERT INTO `base64_data` VALUES ('M', '12');
28INSERT INTO `base64_data` VALUES ('N', '13');
29INSERT INTO `base64_data` VALUES ('O', '14');
30INSERT INTO `base64_data` VALUES ('P', '15');
31INSERT INTO `base64_data` VALUES ('Q', '16');
32INSERT INTO `base64_data` VALUES ('R', '17');
33INSERT INTO `base64_data` VALUES ('S', '18');
34INSERT INTO `base64_data` VALUES ('T', '19');
35INSERT INTO `base64_data` VALUES ('U', '20');
36INSERT INTO `base64_data` VALUES ('V', '21');
37INSERT INTO `base64_data` VALUES ('W', '22');
38INSERT INTO `base64_data` VALUES ('X', '23');
39INSERT INTO `base64_data` VALUES ('Y', '24');
40INSERT INTO `base64_data` VALUES ('Z', '25');
41INSERT INTO `base64_data` VALUES ('a', '26');
42INSERT INTO `base64_data` VALUES ('b', '27');
43INSERT INTO `base64_data` VALUES ('c', '28');
44INSERT INTO `base64_data` VALUES ('d', '29');
45INSERT INTO `base64_data` VALUES ('e', '30');
46INSERT INTO `base64_data` VALUES ('f', '31');
47INSERT INTO `base64_data` VALUES ('g', '32');
48INSERT INTO `base64_data` VALUES ('h', '33');
49INSERT INTO `base64_data` VALUES ('i', '34');
50INSERT INTO `base64_data` VALUES ('j', '35');
51INSERT INTO `base64_data` VALUES ('k', '36');
52INSERT INTO `base64_data` VALUES ('l', '37');
53INSERT INTO `base64_data` VALUES ('m', '38');
54INSERT INTO `base64_data` VALUES ('n', '39');
55INSERT INTO `base64_data` VALUES ('o', '40');
56INSERT INTO `base64_data` VALUES ('p', '41');
57INSERT INTO `base64_data` VALUES ('q', '42');
58INSERT INTO `base64_data` VALUES ('r', '43');
59INSERT INTO `base64_data` VALUES ('s', '44');
60INSERT INTO `base64_data` VALUES ('t', '45');
61INSERT INTO `base64_data` VALUES ('u', '46');
62INSERT INTO `base64_data` VALUES ('v', '47');
63INSERT INTO `base64_data` VALUES ('w', '48');
64INSERT INTO `base64_data` VALUES ('x', '49');
65INSERT INTO `base64_data` VALUES ('y', '50');
66INSERT INTO `base64_data` VALUES ('z', '51');
67INSERT INTO `base64_data` VALUES ('0', '52');
68INSERT INTO `base64_data` VALUES ('1', '53');
69INSERT INTO `base64_data` VALUES ('2', '54');
70INSERT INTO `base64_data` VALUES ('3', '55');
71INSERT INTO `base64_data` VALUES ('4', '56');
72INSERT INTO `base64_data` VALUES ('5', '57');
73INSERT INTO `base64_data` VALUES ('6', '58');
74INSERT INTO `base64_data` VALUES ('7', '59');
75INSERT INTO `base64_data` VALUES ('8', '60');
76INSERT INTO `base64_data` VALUES ('9', '61');
77INSERT INTO `base64_data` VALUES ('+', '62');
78INSERT INTO `base64_data` VALUES ('/', '63');
79INSERT INTO `base64_data` VALUES ('=', '0');
80
81-- ----------------------------
82-- Function structure for BASE64_DECODE
83-- ----------------------------
84DROP FUNCTION IF EXISTS `BASE64_DECODE`;
85DELIMITER ;;
86CREATE DEFINER=`root`@`localhost` FUNCTION `BASE64_DECODE`(input BLOB) RETURNS blob
87 DETERMINISTIC
88 SQL SECURITY INVOKER
89BEGIN
90 DECLARE ret BLOB DEFAULT '';
91 DECLARE done TINYINT DEFAULT 0;
92
93 IF input IS NULL THEN
94 RETURN NULL;
95 END IF;
96
97each_block:
98 WHILE NOT done DO BEGIN
99 DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
100 DECLARE in_count TINYINT DEFAULT 0;
101 DECLARE out_count TINYINT DEFAULT 3;
102
103each_input_char:
104 WHILE in_count < 4 DO BEGIN
105 DECLARE first_char CHAR(1);
106
107 IF LENGTH(input) = 0 THEN
108 RETURN ret;
109 END IF;
110
111 SET first_char = SUBSTRING(input,1,1);
112 SET input = SUBSTRING(input,2);
113
114 BEGIN
115 DECLARE tempval TINYINT UNSIGNED;
116 DECLARE error TINYINT DEFAULT 0;
117 DECLARE base64_getval CURSOR FOR SELECT val FROM base64_data WHERE c = first_char;
118 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET error = 1;
119
120 OPEN base64_getval;
121 FETCH base64_getval INTO tempval;
122 CLOSE base64_getval;
123
124 IF error THEN
125 ITERATE each_input_char;
126 END IF;
127
128 SET accum_value = (accum_value << 6) + tempval;
129 END;
130
131 SET in_count = in_count + 1;
132
133 IF first_char = '=' THEN
134 SET done = 1;
135 SET out_count = out_count - 1;
136 END IF;
137 END; END WHILE;
138
139 -- We've now accumulated 24 bits; deaccumulate into bytes
140
141 -- We have to work from the left, so use the third byte position and shift left
142 WHILE out_count > 0 DO BEGIN
143 SET ret = CONCAT(ret,CHAR((accum_value & 0xff0000) >> 16));
144 SET out_count = out_count - 1;
145 SET accum_value = (accum_value << 8) & 0xffffff;
146 END; END WHILE;
147
148 END; END WHILE;
149
150 RETURN ret;
151END
152;;
153DELIMITER ;
154
155-- ----------------------------
156-- Function structure for BASE64_ENCODE
157-- ----------------------------
158DROP FUNCTION IF EXISTS `BASE64_ENCODE`;
159DELIMITER ;;
160CREATE DEFINER=`YOUR DATABASE`@`%` FUNCTION `BASE64_ENCODE`(input BLOB) RETURNS blob
161 DETERMINISTIC
162 SQL SECURITY INVOKER
163BEGIN
164 DECLARE ret BLOB DEFAULT '';
165 DECLARE done TINYINT DEFAULT 0;
166 IF input IS NULL THEN
167 RETURN NULL;
168 END IF;
169each_block:
170 WHILE NOT done DO BEGIN
171 DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
172 DECLARE in_count TINYINT DEFAULT 0;
173 DECLARE out_count TINYINT;
174each_input_char:
175 WHILE in_count < 3 DO BEGIN
176 DECLARE first_char BLOB(1);
177
178 IF LENGTH(input) = 0 THEN
179 SET done = 1;
180 SET accum_value = accum_value << (8 * (3 - in_count));
181 LEAVE each_input_char;
182 END IF;
183
184 SET first_char = SUBSTRING(input,1,1);
185 SET input = SUBSTRING(input,2);
186
187 SET accum_value = (accum_value << 8) + ASCII(first_char);
188 SET in_count = in_count + 1;
189 END; END WHILE;
190
191 -- We've now accumulated 24 bits; deaccumulate into base64 characters
192 -- We have to work from the left, so use the third byte position and shift left
193 CASE
194 WHEN in_count = 3 THEN SET out_count = 4;
195 WHEN in_count = 2 THEN SET out_count = 3;
196 WHEN in_count = 1 THEN SET out_count = 2;
197 ELSE RETURN ret;
198 END CASE;
199
200 WHILE out_count > 0 DO BEGIN
201 BEGIN
202 DECLARE out_char CHAR(1);
203 DECLARE base64_getval CURSOR FOR SELECT c FROM core_base64_data WHERE val = (accum_value >> 18);
204 OPEN base64_getval;
205 FETCH base64_getval INTO out_char;
206 CLOSE base64_getval;
207 SET ret = CONCAT(ret,out_char);
208 SET out_count = out_count - 1;
209 SET accum_value = accum_value << 6 & 0xffffff;
210 END;
211 END; END WHILE;
212 CASE
213 WHEN in_count = 2 THEN SET ret = CONCAT(ret,'=');
214 WHEN in_count = 1 THEN SET ret = CONCAT(ret,'==');
215 ELSE BEGIN END;
216 END CASE;
217
218 END; END WHILE;
219 RETURN ret;
220END
221;;
222DELIMITER ;