DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets Manager

Snippets has posted 5883 posts at DZone. View Full User Profile

Urlencode/urldecode As MySQL Stored Functions

08.17.2009

| 14004 views |

        urlencode/urldecode as MySQL stored functions.

urlencode():

DELIMITER ;

DROP FUNCTION IF EXISTS urlencode;

DELIMITER |

CREATE FUNCTION 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 ;


urldecode():


DROP FUNCTION IF EXISTS urldecode;

DELIMITER |

CREATE FUNCTION 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 ;


And now a multiurldecode(). For cleaning up data that resulted from multiple urlencode() passes, this just calls urldecode() recursively until there's nothing to decode. Yeah, it has a problem if your original string was, say "x+y" - it will end up decoding it all the way to "x y". But this is good enough for what I need right now.


DELIMITER ;

DROP FUNCTION IF EXISTS multiurldecode;

DELIMITER |

CREATE FUNCTION multiurldecode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
       DECLARE pr VARCHAR(4096) DEFAULT '';
       IF ISNULL(s) THEN
          RETURN NULL;
       END IF;       
       REPEAT
          SET pr = s;
          SELECT urldecode(s) INTO s;
       UNTIL pr = s END REPEAT;
       RETURN s;
END;
 
|

DELIMITER ;

Tags:

Comments

Snippets Manager replied on Fri, 2010/08/13 - 11:23pm

I've tested a lot of methods to use this functions, a more efficient solution can be found here http://abusleem.net/2009/url-decoding-in-mysql/

Snippets Manager replied on Thu, 2009/12/31 - 7:36am

you can find the final version here, I've got some advice from our DBA http://abusleem.net/blog/?p=56 Cheers

Snippets Manager replied on Thu, 2009/12/31 - 7:36am

Actually it works if you set the client to use UTF-8, Have fun!

Snippets Manager replied on Thu, 2009/12/31 - 7:36am

I think there is a much simpler way to do this, here is the urldecode in a new version, I was trying to urldecode UTF characters thats why I wrote this function again, but the problem is it's still doesn't convert utf8 strings , anyone knows why?


DROP FUNCTION IF EXISTS urldecode;

DELIMITER |

CREATE FUNCTION urldecode (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 '';
       DECLARE h3 VARCHAR(4096) DEFAULT '';

       IF ISNULL(s) THEN
          RETURN NULL;
       ELSE
       SET s2 = '';
       WHILE pointer <= LENGTH(s) DO
          SET c = SUBSTR(s,pointer,1);
          IF c = '+' THEN
             SET h3 = '20';
          ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN
                   SET h3 = UPPER(SUBSTR(s,pointer+1,2));
                   SET pointer = pointer + 2;
          ELSE
                   SET h3 = HEX(c);
          END IF;
          SET s2 = CONCAT(s2,h3);
          SET pointer = pointer + 1;
       END while;
       END IF;
       RETURN UNHEX(s2);
END;
 
|

DELIMITER ;