MS CryptoAPI 를 이용한 SQLite 암호화 사용자 함수 만들기

SQLite 에는 기본적으로 지원하는 암호화 기능이 없다. 별도의 비용을 지불 해야만 암호화 기능을 사용할 수 있다.

그래서 이전에 포스팅한 자료(2008/10/30 - [Dev Story/Tips] - Microsoft CryptoAPI 를 이용한 데이터 암호화
)를 바탕으로 데이터를 암호화 하는 사용자 함수를 만들어 보았다.

//------------------------------------------------------------------------ #include <vcl.h> #pragma hdrstop #include "uMain.h" //------------------------------------------------------------------------ #pragma package(smart_init) #pragma resource "*.dfm" TForm1 *Form1; //------------------------------------------------------------------------ #define ENCRYPT_ALGORITHM CALG_RC4 #define KEYLENGTH 0x00800000 static void EncryptFunc(sqlite3_context *context, int argc, sqlite3_value **argv) { if (argc == 0) sqlite3_result_error(context, "Error: No Data)", -1); else if (argc == 1) sqlite3_result_error(context, "Error: No Password", -1); else if (argc != 2) sqlite3_result_error(context, "Error: Invalid param count", -1); else { const unsigned char *pwd; pwd = (const unsigned char *)sqlite3_value_text(argv[1]); const unsigned char *src; src = (const unsigned char *)sqlite3_value_blob(argv[0]); if ( strlen( pwd ) > 0 ) { HCRYPTPROV hCryptProv = NULL; HCRYPTKEY hKey = NULL; HCRYPTHASH hHash = NULL; // Get the Handle to the default provider if ( !CryptAcquireContext( &hCryptProv, NULL, MS_ENHANCED_PROV, PROV_RSA_FULL, 0 ) ) { if ( GetLastError() == NTE_BAD_KEYSET ) { if (!CryptAcquireContext(&hCryptProv, NULL, MS_DEF_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET)) { // CryptAcquireContext() failed. sqlite3_result_error( context, "Error: CryptAcquireContext()", -1); return; } } else { // CryptAcquireContext() failed. sqlite3_result_error( context, "Error: CryptAcquireContext()", -1); return; } } // Create Hash object if ( !CryptCreateHash( hCryptProv, CALG_MD5, 0, 0, &hHash ) ) { // Error during CryptCreateHash() sqlite3_result_error( context, "Error: CryptCreateHash()", -1); return; } // Hash the password if ( !CryptHashData( hHash, pwd, strlen(pwd), 0 ) ) { // Error during CryptHashData sqlite3_result_error( context, "Error: CryptHashData()", -1); return; } // Derive a session key from the hash object if ( !CryptDeriveKey( hCryptProv, ENCRYPT_ALGORITHM, hHash, KEYLENGTH, &hKey ) ) { // Error during CryptDeriveKey sqlite3_result_error( context, "Error: CryptDeriveKey()", -1); return; } // Encrypt unsigned long length = strlen(src); unsigned char *dst = (unsigned char *)malloc(length + 1); memcpy(dst, src, length + 1 ); if ( !CryptEncrypt( hKey, NULL, TRUE, 0, dst, &length, length ) ) { // Error during CryptEncrypt sqlite3_result_error( context, "Error: CryptEncrypt()", -1); free( dst ); return; } sqlite3_result_blob( context, dst, strlen( dst ), SQLITE_TRANSIENT ); free( dst ); } } } //------------------------------------------------------------------------ static void DecryptFunc(sqlite3_context *context, int argc, sqlite3_value **argv) { if (argc == 0) sqlite3_result_error(context, "Error: No Data)", -1); else if (argc == 1) sqlite3_result_error(context, "Error: No Password", -1); else if (argc != 2) sqlite3_result_error(context, "Error: Invalid param count", -1); else { const unsigned char *src; src = (const unsigned char *)sqlite3_value_blob(argv[0]); const unsigned char *pwd; pwd = (const unsigned char *)sqlite3_value_text(argv[1]); if ( strlen( pwd ) > 0 ) { HCRYPTPROV hCryptProv = NULL; HCRYPTKEY hKey = NULL; HCRYPTHASH hHash = NULL; // Get the Handle to the default provider if ( !CryptAcquireContext( &hCryptProv, NULL, MS_ENHANCED_PROV, PROV_RSA_FULL, 0 ) ) { if ( GetLastError() == NTE_BAD_KEYSET ) { if (!CryptAcquireContext(&hCryptProv, NULL, MS_DEF_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET)) { // CryptAcquireContext() failed. sqlite3_result_error( context, "Error: CryptAcquireContext()", -1); return; } } else { // CryptAcquireContext() failed. sqlite3_result_error( context, "Error: CryptAcquireContext())", -1); return; } } // Create Hash object if ( !CryptCreateHash( hCryptProv, CALG_MD5, 0, 0, &hHash ) ) { // Error during CryptCreateHash() sqlite3_result_error( context, "Error: CryptCreateHash()", -1); return; } // Hash the password if ( !CryptHashData( hHash, pwd, strlen( pwd ), 0 ) ) { // Error during CryptHashData sqlite3_result_error( context, "Error: CryptHashData()", -1); return; } // Derive a session key from the hash object if ( !CryptDeriveKey( hCryptProv, ENCRYPT_ALGORITHM, hHash, KEYLENGTH, &hKey ) ) { // Error during CryptDeriveKey sqlite3_result_error( context, "Error: CryptDeriveKey()", -1); return; } // Decrypt unsigned long length = strlen(src); unsigned char *dst = (unsigned char *)malloc(length + 1); memcpy(dst, src, length + 1 ); if ( !CryptDecrypt( hKey, NULL, TRUE, 0, dst, &length ) ) { // Error during CryptEncrypt sqlite3_result_error( context, "Error: CryptDecrypt()", -1); return; } sqlite3_result_blob( context, dst, strlen( dst ), SQLITE_TRANSIENT ); free( dst ); } } } //------------------------------------------------------------------------ __fastcall TForm1::TForm1(TComponent* Owner) : TForm(Owner) { m_pDB = NULL; } //------------------------------------------------------------------------ void __fastcall TForm1::FormDestroy(TObject *Sender) { if ( m_pDB ) sqlite3_close( m_pDB ); } //------------------------------------------------------------------------ void __fastcall TForm1::FormCreate(TObject *Sender) { int nRst = sqlite3_open( "test.db", &m_pDB ); if ( nRst != SQLITE_OK ) { ShowMessage("Cannot open database."); return; } int nResult; nResult = sqlite3_create_function(m_pDB, "Encrypt", -1, SQLITE_UTF8, NULL, &EncryptFunc, NULL, NULL); nResult = sqlite3_create_function(m_pDB, "Decrypt", -1, SQLITE_UTF8, NULL, &DecryptFunc, NULL, NULL); sqlite3_stmt* pStmt = NULL; const char* szChar; String strTemp; String strQuery; strQuery = "SELECT Encrypt('test', '123')"; if ( sqlite3_prepare( m_pDB, strQuery.c_str(), strQuery.Length(), &pStmt, &szChar ) == SQLITE_OK ) { int nRow = sqlite3_data_count( pStmt ); int nCol = sqlite3_column_count( pStmt ); const char* col1name = sqlite3_column_name( pStmt, 0 ); int nRowCnt = 0; if ( sqlite3_step( pStmt ) == SQLITE_ROW ) { ++nRowCnt; const unsigned char* col1 = sqlite3_column_text( pStmt, 0 ); String strMsg; strMsg.sprintf( "ROW %d: %s => %s", nRowCnt, col1name, col1 ); strTemp.sprintf( "%s", col1 ); ShowMessage( strMsg ); } } sqlite3_finalize( pStmt ); strQuery = "SELECT Decrypt('" + strTemp + "', '123')"; if ( sqlite3_prepare( m_pDB, strQuery.c_str(), strQuery.Length(), &pStmt, &szChar ) == SQLITE_OK ) { int nRow = sqlite3_data_count( pStmt ); int nCol = sqlite3_column_count( pStmt ); const char* col1name = sqlite3_column_name( pStmt, 0 ); int nRowCnt = 0; if ( sqlite3_step( pStmt ) == SQLITE_ROW ) { ++nRowCnt; const unsigned char* col1 = sqlite3_column_text( pStmt, 0 ); String strMsg; strMsg.sprintf( "ROW %d: %s => %s", nRowCnt, col1name, col1 ); ShowMessage( strMsg ); } } sqlite3_finalize( pStmt ); } //------------------------------------------------------------------------