Skip to content

Commit 92e4ad7

Browse files
sjlombardosjlombardo
committed
implementation of sqlcipher_export() function to copy schema and data between databases
1 parent 76c1e11 commit 92e4ad7

5 files changed

Lines changed: 328 additions & 37 deletions

File tree

‎src/crypto.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -129,6 +129,8 @@ int sqlcipher_codec_ctx_set_cipher(codec_ctx *, const char *, int);
129129

130130
void* sqlcipher_codec_ctx_get_data(codec_ctx *);
131131

132+
void sqlcipher_exportFunc(sqlite3_context *, int, sqlite3_value **);
133+
132134
/* end extensions defined in crypto_impl.c */
133135

134136
#endif

‎src/crypto_impl.c‎

Lines changed: 199 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -499,3 +499,202 @@ int sqlcipher_codec_key_copy(codec_ctx *ctx, int source) {
499499
}
500500

501501

502+
#ifndef OMIT_EXPORT
503+
504+
/*
505+
* Implementation of an "export" function that allows a caller
506+
* to duplicate the main database to an attached database. This is intended
507+
* as a conveneince for users who need to:
508+
*
509+
* 1. migrate from an non-encrypted database to an encrypted database
510+
* 2. move from an encrypted database to a non-encrypted database
511+
* 3. convert beween the various flavors of encrypted databases.
512+
*
513+
* This implementation is based heavily on the procedure and code used
514+
* in vacuum.c, but is exposed as a function that allows export to any
515+
* named attached database.
516+
*/
517+
518+
/*
519+
** Finalize a prepared statement. If there was an error, store the
520+
** text of the error message in *pzErrMsg. Return the result code.
521+
**
522+
** Based on vacuumFinalize from vacuum.c
523+
*/
524+
static int sqlcipher_finalize(sqlite3 *db, sqlite3_stmt *pStmt, char **pzErrMsg){
525+
int rc;
526+
rc = sqlite3VdbeFinalize((Vdbe*)pStmt);
527+
if( rc ){
528+
sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
529+
}
530+
return rc;
531+
}
532+
533+
/*
534+
** Execute zSql on database db. Return an error code.
535+
**
536+
** Based on execSql from vacuum.c
537+
*/
538+
static int sqlcipher_execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
539+
sqlite3_stmt *pStmt;
540+
VVA_ONLY( int rc; )
541+
if( !zSql ){
542+
return SQLITE_NOMEM;
543+
}
544+
if( SQLITE_OK!=sqlite3_prepare(db, zSql, -1, &pStmt, 0) ){
545+
sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
546+
return sqlite3_errcode(db);
547+
}
548+
VVA_ONLY( rc = ) sqlite3_step(pStmt);
549+
assert( rc!=SQLITE_ROW );
550+
return sqlcipher_finalize(db, pStmt, pzErrMsg);
551+
}
552+
553+
/*
554+
** Execute zSql on database db. The statement returns exactly
555+
** one column. Execute this as SQL on the same database.
556+
**
557+
** Based on execExecSql from vacuum.c
558+
*/
559+
static int sqlcipher_execExecSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
560+
sqlite3_stmt *pStmt;
561+
int rc;
562+
563+
rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
564+
if( rc!=SQLITE_OK ) return rc;
565+
566+
while( SQLITE_ROW==sqlite3_step(pStmt) ){
567+
rc = sqlcipher_execSql(db, pzErrMsg, (char*)sqlite3_column_text(pStmt, 0));
568+
if( rc!=SQLITE_OK ){
569+
sqlcipher_finalize(db, pStmt, pzErrMsg);
570+
return rc;
571+
}
572+
}
573+
574+
return sqlcipher_finalize(db, pStmt, pzErrMsg);
575+
}
576+
577+
/*
578+
* copy database and schema from the main database to an attached database
579+
*
580+
* Based on sqlite3RunVacuum from vacuum.c
581+
*/
582+
void sqlcipher_exportFunc(sqlite3_context *context, int argc, sqlite3_value **argv) {
583+
sqlite3 *db = sqlite3_context_db_handle(context);
584+
const char* attachedDb = (const char*) sqlite3_value_text(argv[0]);
585+
int saved_flags; /* Saved value of the db->flags */
586+
int saved_nChange; /* Saved value of db->nChange */
587+
int saved_nTotalChange; /* Saved value of db->nTotalChange */
588+
void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */
589+
int rc = SQLITE_OK; /* Return code from service routines */
590+
char *zSql = NULL; /* SQL statements */
591+
char *pzErrMsg = NULL;
592+
593+
saved_flags = db->flags;
594+
saved_nChange = db->nChange;
595+
saved_nTotalChange = db->nTotalChange;
596+
saved_xTrace = db->xTrace;
597+
db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks | SQLITE_PreferBuiltin;
598+
db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder);
599+
db->xTrace = 0;
600+
601+
/* Query the schema of the main database. Create a mirror schema
602+
** in the temporary database.
603+
*/
604+
zSql = sqlite3_mprintf(
605+
"SELECT 'CREATE TABLE %s.' || substr(sql,14) "
606+
" FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
607+
" AND rootpage>0"
608+
, attachedDb);
609+
rc = (zSql == NULL) ? SQLITE_NOMEM : sqlcipher_execExecSql(db, &pzErrMsg, zSql);
610+
if( rc!=SQLITE_OK ) goto end_of_export;
611+
sqlite3_free(zSql);
612+
613+
zSql = sqlite3_mprintf(
614+
"SELECT 'CREATE INDEX %s.' || substr(sql,14)"
615+
" FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %%' "
616+
, attachedDb);
617+
rc = (zSql == NULL) ? SQLITE_NOMEM : sqlcipher_execExecSql(db, &pzErrMsg, zSql);
618+
if( rc!=SQLITE_OK ) goto end_of_export;
619+
sqlite3_free(zSql);
620+
621+
zSql = sqlite3_mprintf(
622+
"SELECT 'CREATE UNIQUE INDEX %s.' || substr(sql,21) "
623+
" FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %%'"
624+
, attachedDb);
625+
rc = (zSql == NULL) ? SQLITE_NOMEM : sqlcipher_execExecSql(db, &pzErrMsg, zSql);
626+
if( rc!=SQLITE_OK ) goto end_of_export;
627+
sqlite3_free(zSql);
628+
629+
/* Loop through the tables in the main database. For each, do
630+
** an "INSERT INTO rekey_db.xxx SELECT * FROM main.xxx;" to copy
631+
** the contents to the temporary database.
632+
*/
633+
zSql = sqlite3_mprintf(
634+
"SELECT 'INSERT INTO %s.' || quote(name) "
635+
"|| ' SELECT * FROM main.' || quote(name) || ';'"
636+
"FROM main.sqlite_master "
637+
"WHERE type = 'table' AND name!='sqlite_sequence' "
638+
" AND rootpage>0"
639+
, attachedDb);
640+
rc = (zSql == NULL) ? SQLITE_NOMEM : sqlcipher_execExecSql(db, &pzErrMsg, zSql);
641+
if( rc!=SQLITE_OK ) goto end_of_export;
642+
sqlite3_free(zSql);
643+
644+
/* Copy over the sequence table
645+
*/
646+
zSql = sqlite3_mprintf(
647+
"SELECT 'DELETE FROM %s.' || quote(name) || ';' "
648+
"FROM %s.sqlite_master WHERE name='sqlite_sequence' "
649+
, attachedDb, attachedDb);
650+
rc = (zSql == NULL) ? SQLITE_NOMEM : sqlcipher_execExecSql(db, &pzErrMsg, zSql);
651+
if( rc!=SQLITE_OK ) goto end_of_export;
652+
sqlite3_free(zSql);
653+
654+
zSql = sqlite3_mprintf(
655+
"SELECT 'INSERT INTO %s.' || quote(name) "
656+
"|| ' SELECT * FROM main.' || quote(name) || ';' "
657+
"FROM %s.sqlite_master WHERE name=='sqlite_sequence';"
658+
, attachedDb, attachedDb);
659+
rc = (zSql == NULL) ? SQLITE_NOMEM : sqlcipher_execExecSql(db, &pzErrMsg, zSql);
660+
if( rc!=SQLITE_OK ) goto end_of_export;
661+
sqlite3_free(zSql);
662+
663+
/* Copy the triggers, views, and virtual tables from the main database
664+
** over to the temporary database. None of these objects has any
665+
** associated storage, so all we have to do is copy their entries
666+
** from the SQLITE_MASTER table.
667+
*/
668+
zSql = sqlite3_mprintf(
669+
"INSERT INTO %s.sqlite_master "
670+
" SELECT type, name, tbl_name, rootpage, sql"
671+
" FROM main.sqlite_master"
672+
" WHERE type='view' OR type='trigger'"
673+
" OR (type='table' AND rootpage=0)"
674+
, attachedDb);
675+
rc = (zSql == NULL) ? SQLITE_NOMEM : sqlcipher_execSql(db, &pzErrMsg, zSql);
676+
if( rc!=SQLITE_OK ) goto end_of_export;
677+
sqlite3_free(zSql);
678+
679+
zSql = NULL;
680+
end_of_export:
681+
db->flags = saved_flags;
682+
db->nChange = saved_nChange;
683+
db->nTotalChange = saved_nTotalChange;
684+
db->xTrace = saved_xTrace;
685+
686+
sqlite3_free(zSql);
687+
688+
if(rc) {
689+
if(pzErrMsg != NULL) {
690+
sqlite3_result_error(context, pzErrMsg, -1);
691+
sqlite3DbFree(db, pzErrMsg);
692+
} else {
693+
sqlite3_result_error(context, sqlite3ErrStr(rc), -1);
694+
}
695+
}
696+
697+
return rc;
698+
}
699+
700+
#endif

‎src/func.c‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1424,6 +1424,11 @@ void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
14241424
if( rc==SQLITE_NOMEM ){
14251425
db->mallocFailed = 1;
14261426
}
1427+
1428+
#ifndef OMIT_EXPORT
1429+
extern void sqlcipher_exportFunc(sqlite3_context *, int, sqlite3_value **);
1430+
sqlite3CreateFunc(db, "sqlcipher_export", 1, SQLITE_TEXT, 0, sqlcipher_exportFunc, 0, 0, 0);
1431+
#endif
14271432
}
14281433

14291434
/*

‎src/pragma.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1489,15 +1489,15 @@ void sqlite3Pragma(
14891489
if( sqlite3StrICmp(zLeft,"cipher_page_size")==0 ){
14901490
extern int codec_set_page_size(sqlite3*, int, int);
14911491
codec_set_page_size(db, iDb, atoi(zRight)); // change page size
1492-
}
1492+
}else
14931493
if( sqlite3StrICmp(zLeft,"cipher_use_hmac")==0 ){
14941494
extern int codec_set_use_hmac(sqlite3*, int, int);
14951495
if(getBoolean(zRight)) {
14961496
codec_set_use_hmac(db, iDb, 1);
14971497
} else {
14981498
codec_set_use_hmac(db, iDb, 0);
14991499
}
1500-
}
1500+
}else
15011501
/** END CRYPTO **/
15021502
#endif
15031503
#if defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD)

0 commit comments

Comments
 (0)