urldecode for mysql

It is possible to get the urldecode() or urlencode function in MySql, the same way it exist in PHP as url_decode and url_encode.

You just have to install the User Defined Function : UDF collection for MySQL created by Michael Wallner from IWorks.

There are just a few points to help the installation :

  • Decompress the sources : tar xf libmysqludf-0.3.tar.gz
  • Go into the directory cd libmysqludf-0.3 and read the INSTALL file !
  • If you don’t have them get MySql sources as well : yum install mysql-devel
  • On a RPM style MySql install do a ./configure --prefix=/usr/lib/mysql --includedir=/usr/include/mysql --libdir=/usr/lib (not sure if it is all really needed but it worked on a Fedora Core 5).
  • make
  • sudo make install
  • sudo /etc/init.d/mysqld restart (maybe not needed)
  • Open a mysql client as root if you don’t have the INSERT privilege for the mysql database (CREATE FUNCTION Syntax) : mysql -u root -p
  • There is the trick : CREATE FUNCTION urldecode RETURNS STRING SONAME 'libmysqludf_urldecode.so'; Notice the function name is lower case !!!
  • If you want to remove the function use DROP and then sudo make uninstall

Then you are free to url decode or url encode right in you SQL query ! It is very helpfull when you work on Apache log files !
I search for a Linux command line tool to urldecode, but I couldn’t find one. Still here are some good ressources :

Maybe a solution would be (from Perl FAQ ) :

The best source of detailed information on URI encoding is RFC 2396. Basically, the following substitutions do it :

    s/([^\w()'*~!.-])/sprintf '%%%02x', ord $1/eg;   # encode

    s/%([A-Fa-f\d]{2})/chr hex $1/eg;                # decode
	s/%([[:xdigit:]]{2})/chr hex $1/eg;          # same thing

This entry was posted on Wednesday, April 11th, 2007 at 10:21 am and is filed under dev, linux. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a comment