Crucial Web Hosting

urlencode() and 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 ;

multiurlencode()

And now a multiurlencode() function for cleaning up data that resulted from multiple urlencode() passes. This just calls urldecode() recursively until there's nothing to decode.

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 ;