MD5 function in SQLite

Question

I am trying to port some sql from MySQL to SQLite, however we use mysql's md5() function, which doesn't exist in sqlite.

I've seen references to people recompiling sqlite to include this function, and i think it's possible to include user defined functions in sqlite (right?). So how do I go about adding md5() to sqlite? I'd rather not have to recompile the sqlite installed by my package manager, is it possible to have md5 without doing this?

Answer 1

The following builds latest sqlite with dynamic library support, and compiles md5 extension. It also assumes debian-based linux distributive:

sudo apt build-dep sqlite3 # fetches dependencies to compile sqlite3

mkdir sqlite-compilation
cd    sqlite-compilation

wget -O sqlite.tar.gz https://www.sqlite.org/src/tarball/sqlite.tar.gz?r=release

tar xzf sqlite.tar.gz

mkdir build
cd    build
  ../sqlite/configure
  make OPTS='-DSQLITE_ENABLE_LOAD_EXTENSION'
  ./sqlite3 -cmd 'pragma compile_options;' <<< .exit
cd -

# https://github.com/moisseev/sqlite-md5
cd sqlite/ext
  wget -O sqlite-md5-master.zip https://github.com/moisseev/sqlite-md5/archive/master.zip
  unzip   sqlite-md5-master.zip
  cd      sqlite-md5-master
    gcc -lm -fPIC -shared md5.c -o libSqlite3Md5.so
    cp libSqlite3Md5.so ../../../build/
  cd -
cd ../../

In result you will have:

build/sqlite3          # sqlite3 binary
build/libSqlite3Md5.so # md5 extension

Test:

cd build
  sqlite3 <<< '
.load ./libSqlite3Md5
select hex(md5(1));
.exit
  '
  # compare output with:
  echo -n 1 | md5sum
cd -