MD5 function in SQLite

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 recom开发者_高级运维pile the sqlite installed by my package manager, is it possible to have md5 without doing this?

I have created an extension for sqlite using openssl functions. You may check it out here!

Be forewarned, you need a compiler and some knowledge of how to use one. I can provide some assistance if you need - just post a comment.

SQLite doesn't have any built-in hashing functionality. But as you correctly said you could define a user function. See this SO answer for more details:

• SHA1 hashing in SQLite: how?

Hope that helps!

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 -