So I can definitely not take credit for this trick. The credit goes to Augusto Bott of the Pythian Group for this. I have a table in my DB that has columns of encoded URIs. The list of URI encoding character translations is available here.
To accomplish this, the MySQL REPLACE function was used.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> SET @test:='%20%27%7C%26%5E%2B%2D%25'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @test as original, -> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> @test,'%20',' '), -> '%27','\''), -> '%7C','|'), -- REPLACE() is case sensitive -> '%7c','|'), -- so we have -> '%26','&'), -> '%5E','^'), -> '%5e','^'), -- to repeat -> '%2D','-'), -> '%2d','-'), -- ourselves -> '%2B','+'), -> '%2b','+'), -- sometimes -> '%25','%') as replaced; +--------------------------+----------+ | original | replaced | +--------------------------+----------+ | %20%27%7C%26%5E%2B%2D%25 | '|&^+-% | +--------------------------+----------+ 1 row in set (0.01 sec) |
To make our lives easier, let’s create a function from that.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> CREATE FUNCTION url_replace -> (url VARCHAR(1024)) -> RETURNS VARCHAR(1025) -> BEGIN -> RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( -> @test,'%20',' '), -> '%27','\''), -> '%7C','|'), -> '%7c','|'), -> '%26','&'), -> '%5E','^'), -> '%5e','^'), -> '%2D','-'), -> '%2d','-'), -> '%2B','+'), -> '%2b','+'), -> '%25','%'); -> END$$ Query OK, 0 rows affected (0.02 sec) |
And to wrap it up, let’s use it in an actual query. This query finds all instances of a % and replaces it with its unencoded character equivalent.
1 | mysql> UPDATE website_page SET uri = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( uri,'%20',' '), '%27','\''), '%7C','|'), '%7c','|'), '%26','&'), '%5E','^'), '%5e','^'), '%2D','-'), '%2d','-'), '%2B','+'), '%2b','+'), '%25','%'), '%2C',',') WHERE uri LIKE '%\%%'; |
