MySQL - Url - Base64 encode - decode function

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

DELIMITER ;
 
DROP FUNCTION IF EXISTS myschema.urlencode;
 
DELIMITER |
 
CREATE FUNCTION input_pentaho_bod.urlencode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
       DECLARE c VARCHAR(4096) DEFAULT '';
       DECLARE pointer INT DEFAULT 1;
       DECLARE s2 VARCHAR(4096) DEFAULT '';
 
       IF ISNULL(s) THEN
           RETURN NULL;
       ELSE
       SET s2 = '';
       WHILE pointer <= length(s) DO
          SET c = MID(s,pointer,1);
          IF c = ' ' THEN
             SET c = '+';
          ELSEIF NOT (ASCII(c) BETWEEN 48 AND 57 OR 
                ASCII(c) BETWEEN 65 AND 90 OR 
                ASCII(c) BETWEEN 97 AND 122) THEN
             SET c = concat("%",LPAD(CONV(ASCII(c),10,16),2,0));
          END IF;
          SET s2 = CONCAT(s2,c);
          SET pointer = pointer + 1;
       END while;
       END IF;
       RETURN s2;
END;
 
|
 
DELIMITER ;
 
DROP FUNCTION IF EXISTS myschema.urldecode;
 
DELIMITER |
 
CREATE FUNCTION input_pentaho_bod.urldecode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
       DECLARE c VARCHAR(4096) DEFAULT '';
       DECLARE pointer INT DEFAULT 1;
       DECLARE h CHAR(2);
       DECLARE h1 CHAR(1);
       DECLARE h2 CHAR(1);
       DECLARE s2 VARCHAR(4096) DEFAULT '';
 
       IF ISNULL(s) THEN
          RETURN NULL;
       ELSE
       SET s2 = '';
       WHILE pointer <= LENGTH(s) DO
          SET c = MID(s,pointer,1);
          IF c = '+' THEN
             SET c = ' ';
          ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN
             SET h1 = LOWER(MID(s,pointer+1,1));
             SET h2 = LOWER(MID(s,pointer+2,1));
             IF (h1 BETWEEN '0' AND '9' OR h1 BETWEEN 'a' AND 'f')
                 AND
                 (h2 BETWEEN '0' AND '9' OR h2 BETWEEN 'a' AND 'f') 
                 THEN
                   SET h = CONCAT(h1,h2);
                   SET pointer = pointer + 2;
                   SET c = CHAR(CONV(h,16,10));
              END IF;
          END IF;
          SET s2 = CONCAT(s2,c);
          SET pointer = pointer + 1;
       END while;
       END IF;
       RETURN s2;
END;
 
|
 
DELIMITER ;
 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

-- base64.sql - MySQL base64 encoding/decoding functions
-- Copyright (C) 2006 Ian Gulliver
-- 
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of version 2 of the GNU General Public License as
-- published by the Free Software Foundation.
-- 
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
-- 
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 
delimiter |
 
DROP TABLE IF EXISTS input_pentaho_bod.base64_data |
CREATE TABLE base64_data (c CHAR(1) BINARY, val TINYINT) |
INSERT INTO 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 input_pentaho_bod.BASE64_DECODE |
CREATE FUNCTION BASE64_DECODE (input BLOB)
	RETURNS BLOB
	CONTAINS SQL
	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 DEFAULT 3;
 
each_input_char:
		WHILE in_count < 4 DO BEGIN
			DECLARE first_char CHAR(1);
 
			IF LENGTH(input) = 0 THEN
				RETURN ret;
			END IF;
 
			SET first_char = SUBSTRING(input,1,1);
			SET input = SUBSTRING(input,2);
 
			BEGIN
				DECLARE tempval TINYINT UNSIGNED;
				DECLARE error TINYINT DEFAULT 0;
				DECLARE base64_getval CURSOR FOR SELECT val FROM base64_data WHERE c = first_char;
				DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET error = 1;
 
				OPEN base64_getval;
				FETCH base64_getval INTO tempval;
				CLOSE base64_getval;
 
				IF error THEN
					ITERATE each_input_char;
				END IF;
 
				SET accum_value = (accum_value << 6) + tempval;
			END;
 
			SET in_count = in_count + 1;
 
			IF first_char = '=' THEN
				SET done = 1;
				SET out_count = out_count - 1;
			END IF;
		END; END WHILE;
 
		-- We've now accumulated 24 bits; deaccumulate into bytes
 
		-- We have to work from the left, so use the third byte position and shift left
		WHILE out_count > 0 DO BEGIN
			SET ret = CONCAT(ret,CHAR((accum_value & 0xff0000) >> 16));
			SET out_count = out_count - 1;
			SET accum_value = (accum_value << 8) & 0xffffff;
		END; END WHILE;
 
	END; END WHILE;
 
	RETURN ret;
END |
 
DROP FUNCTION IF EXISTS input_pentaho_bod.BASE64_ENCODE |
CREATE FUNCTION BASE64_ENCODE (input BLOB)
	RETURNS BLOB
	CONTAINS SQL
	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 < 3 DO BEGIN
			DECLARE first_char CHAR(1);
 
			IF LENGTH(input) = 0 THEN
				SET done = 1;
				SET accum_value = accum_value << (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 << 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 > 0 DO BEGIN
			BEGIN
				DECLARE out_char CHAR(1);
				DECLARE base64_getval CURSOR FOR SELECT c FROM base64_data WHERE val = (accum_value >> 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 << 6 & 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 |