Crate sqlite_hashes

sqlite-hashes

Use this crate to add various hash functions to SQLite, including MD5, SHA1, SHA256, and SHA512. All functions support text and blob values. Functions support any number of parameters which will be hashed in order. NULL values will be ignored. The functions return a blob with the hash value unless all parameters are NULL, in which case NULL is returned.

There are also aggregate functions that compute combined hash over a set of values, e.g. sha256_concat. These functions are useful for computing hash over a set of values, e.g. a column in a table. The aggregate functions also support multiple values, so you can compute a hash over a set of columns, e.g. sha256_concat(col1, col2, col3) over a whole set.

This crate uses rusqlite to add user-defined functions using static linking. Eventually it would be good to build dynamically loadable extension binaries usable from other languages (PRs welcome).

Usage

use sqlite_hashes::{register_sha256_function, rusqlite::Connection};

fn main() {
  // Connect to SQLite DB and register needed hashing functions
  let db = Connection::open_in_memory().unwrap();
  register_sha256_function(&db).unwrap();

  // Hash 'password' using SHA-256, and dump it as a HEX string
  let sql = "SELECT hex(sha256('password'))";
  let hash: String = db.query_row_and_then(&sql, [], |r| r.get(0)).unwrap();
  assert_eq!(hash, "5E884898DA28047151D0E56F8DC6292773603D0D6AABBDD62A11EF721D1542D8");

  // Hash 'pass' (as text) and 'word' (as blob) using SHA-256, and dump it as a HEX string
  // The result is the same as the above 'password' example.
  let sql = "SELECT hex(sha256(cast('pass' as text), cast('word' as blob)))";
  let hash: String = db.query_row_and_then(&sql, [], |r| r.get(0)).unwrap();
  assert_eq!(hash, "5E884898DA28047151D0E56F8DC6292773603D0D6AABBDD62A11EF721D1542D8");

  // Iterate over a set of values and hash them together.
  // Make sure the value order is consistent.
  // This example creates a sequence of ints from 0 to 9.
  let sql = "
      WITH RECURSIVE sequence(value) AS (
        SELECT 0 UNION ALL SELECT value + 1 FROM sequence LIMIT 10
      )
      SELECT hex(sha256_concat(cast(value as text)))
      FROM sequence
      ORDER BY value";
  let hash: String = db.query_row_and_then(&sql, [], |r| r.get(0)).unwrap();
  assert_eq!(hash, "84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882");
  
  // The above sequence aggregation example is equivalent to this:
  let sql = "SELECT hex(sha256('0123456789'))";
  let hash: String = db.query_row_and_then(&sql, [], |r| r.get(0)).unwrap();
  assert_eq!(hash, "84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882");
}

Features

By default, this crate will compile with all hash functions. You can enable just the ones you need to reduce compile time.

[dependencies]
sqlite-hashes = { version = "0.3", default-features = false, features = ["sha256"] }

Development

• This project is easier to develop with just, a modern alternative to make. Install it with cargo install just.
• To get a list of available commands, run just.
• To run tests, use just test.
• On git push, it will run a few validations, including cargo fmt, cargo clippy, and cargo test. Use git push --no-verify to skip these checks.

License

Licensed under either of

• Apache License, Version 2.0 (LICENSE-APACHE or http://www.apache.org/licenses/LICENSE-2.0)
• MIT license (LICENSE-MIT or http://opensource.org/licenses/MIT) at your option.

Contribution

Unless you explicitly state otherwise, any contribution intentionally submitted for inclusion in the work by you, as defined in the Apache-2.0 license, shall be dual licensed as above, without any additional terms or conditions.

Re-exports

• pub use rusqlite;

Functions

• register_md5_function
  Register the md5 SQL function with the given SQLite connection. The function takes a single argument and returns the MD5 hash (blob) of that argument. The argument can be either a string or a blob. If the argument is NULL, the result is NULL.
• register_sha1_function
  Register the sha1 SQL function with the given SQLite connection. The function takes a single argument and returns the SHA1 hash (blob) of that argument. The argument can be either a string or a blob. If the argument is NULL, the result is NULL.
• register_sha256_function
  Register the sha256 SQL function with the given SQLite connection. The function takes a single argument and returns the SHA256 hash (blob) of that argument. The argument can be either a string or a blob. If the argument is NULL, the result is NULL.
• register_sha512_function
  Register the sha512 SQL function with the given SQLite connection. The function takes a single argument and returns the SHA512 hash (blob) of that argument. The argument can be either a string or a blob. If the argument is NULL, the result is NULL.