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?

score 8 · Answer 1 · answered Jun 16 '11 at 15:56

8

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.

answered Jun 16 '11 at 15:56

Tim Tessier

81
1
1

score 5 · Answer 2 · edited May 23 '17 at 12:17

5

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!

edited May 23 '17 at 12:17

Community

1
1

answered Mar 02 '11 at 18:45

Martin Buberl

45,844
25
100
144

Adobe · Answer 3 · 2020-12-19T05:44:54.143

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 -

Had this error: sqlite3ext.h:20:10: fatal error: sqlite3.h: No such file or directory #include "sqlite3.h" When I fixed by copying the file to cp ./build/sqlite3.h ~/sqlite-compilation/sqlite/ext/sqlite-md5-master/ it worked. — shinguz, Feb 09 '21 at 17:23
@shinguz strange, I just copy-pasted that code to my other computer, and it worked with no modification. — Adobe, Feb 09 '21 at 20:08
I set-up the system from scratch. Can it be that you have the .h file somewhere in the default paths? It is also possible that I was just tired and did it wrong... — shinguz, Feb 10 '21 at 21:38

score 0 · Answer 4 · answered Jul 27 '23 at 16:51

For Rust developers, there is now sqlite-hashes crate that adds MD5, SHA1, SHA256, and SHA512 functions. Currently it targets statically linked usecases, but eventually I hope that project will also generate dynamically linked libs for non-Rust users for various platforms.

(Disclaimer: I'm the author)

MD5 function in SQLite

4 Answers4