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 );
}
//------------------------------------------------------------------------

Original Post : http://neodreamer-dev.tistory.com/196