SQLite加密

SQLiteCrypt API

SQLiteCrypt is very easy to use. SQLiteCrypt is based on SQLite with all API functions remain unchanged. All encryption/ decryption routines are performed transparently. SQLiteCrypt uses three PRAGMA statements to work with encrypted database:

PRAGMA key = 'the passphrase' // passphrase 

PRAGMA rekey = 'new passphrase' // change passphrase 

PRAGMA lic = 'the license key' // the software key

The first PRAGMA statement is used to create/ access encrypted database. The second one will re-write database with new passphrase. The third one used to identify legal copy of SQLiteCrypt software.

Remark: Do not use rekey in middle of a transaction. This method decrypt whole database using old passphrase, then encrypt using new passphrase. You can continue to use SQLite API functions, no need of closing and re-opening database. This is time-consuming operation.

Example 1: Create/ open encrypted SQLite database

sqlite3_open_v2("data.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

sqlite3_stmt* stm;
const char *pzTail;

int res;

res = sqlite3_prepare(db, "PRAGMA key = 'ac23';", -1, &stm, &pzTail); //ac23 is database passphrase
res = sqlite3_step(stm);

res = sqlite3_prepare(db, "PRAGMA lic = '77523-009-0000007-72328';", -1, &stm, &pzTail); //software license key
res = sqlite3_step(stm);

//now you have all access to data.db

Example 2: Decrypt SQLite database (remove encryption, so any other SQLite application can open it)

sqlite3_open_v2("data.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

sqlite3_stmt* stm;
const char *pzTail;

int res;

res = sqlite3_prepare(db, "PRAGMA key = 'ac23';", -1, &stm, &pzTail); //ac23 is current passphrase
res = sqlite3_step(stm);

res = sqlite3_prepare(db, "PRAGMA lic = '77523-009-0000007-72328';", -1, &stm, &pzTail); //software license key
res = sqlite3_step(stm);

//now you have all access to encrypted data.db

res = sqlite3_prepare(db, "PRAGMA rekey = '';", -1, &stm, &pzTail); // new empty passphrase
res = sqlite3_step(stm);

//now data.db is NOT encrypted

Example 3: Change encryption key on-the-fly

sqlite3_open_v2("data.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

sqlite3_stmt* stm;
const char* pzTail;

int res;

res = sqlite3_prepare(db, "PRAGMA key = 'ac23';", -1, &stm, &pzTail); //ac23 is current passphrase
res = sqlite3_step(stm);

res = sqlite3_prepare(db, "PRAGMA lic = '77523-009-0000007-72328';", -1, &stm, &pzTail); //software license key
res = sqlite3_step(stm);

//now you have all access to encrypted data.db

res = sqlite3_prepare(db, "PRAGMA rekey = 'abc123';", -1, &stm, &pzTail); //abc123 is new passphrase
res = sqlite3_step(stm);

//now data.db re-written using new passphrase

Example 4: Encrypt SQLite database (add encryption to regular SQLite database)

sqlite3_open_v2("data.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

sqlite3_stmt* stm;
const char* pzTail;

int res;

res = sqlite3_prepare(db, "PRAGMA lic = '77523-009-0000007-72328';", -1, &stm, &pzTail); //software license key
res = sqlite3_step(stm);

//now you have all access to regular data.db

res = sqlite3_prepare(db, "PRAGMA rekey = 'abc123';", -1, &stm, &pzTail);// encrypt database using abc123 passphrase
res = sqlite3_step(stm);

//now data.db is encrypted

Example 5: Using SQLiteCrypt command line tool

Opening encrypted db without passphrase:

D:\>sqlite.exe data.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from _MapPropertyA;
Error: file is encrypted or is not a database

Querry on an encrypted database

D:\>sqlite.exe data.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA key = 'ac23';
sqlite> PRAGMA lic = '77523-009-0000007-72328';
sqlite> select * from _MapPropertyA;
3.0|8.0
3.0|8.0