SQLCipher ported to Harbour (Windows)

[mol]

I know sqlite. Bur I need sample how to use sqlcipher.

[Rathinagiri]

In that case here I have attached an utility to encrypt/decrypt the sqlite files. Hope that is self sufficient. If you want a full project, I can give that too.

For encryption of an ordinary plain database:

Please see the encryption function of the above utility. The procedure is you have to attach the database to a newly encrypted database and copy all the structure and data from the plain database to the encrypted database and finally detach the database.

For Opening and using the database in the code:

You have to open the database as usual like an ordinary un-encrypted database.

After opening the file successfully, pass this query,

'pragma key = ' + c2sql( cPassword )

After running this query, the database will be decrypted for the next queries until the database connection is closed. If the password is not correct, then next queries will give errors.

For changing the database pass phrase:

For changing the password for a database file, after successful opening of the file with the key, we have to give the following query,

'pragma rekey = ' + c2sql( cNewPassword )

Example HMG Code:

Code: Select all

   dbo := connect2db( fname, .f. )
   if dbo == nil
      passok := .f.
      return nil
   endif
   miscsql( dbo, 'pragma key = ' + c2sql( key1 ) )
   aTable := sql( dbo, 'select * from sqlite_master' )
   if valtype( aTable ) == 'A'
      if len( aTable ) > 0
         passok := .t.
      else
         passok := .f.
      endif
   else
      passok := .f.
   endif      

[mol]

So, if I want to prepare new database, I need to:
1. connect2db(cDBName,.t.)
2. set pragma key
3. Use database in normal way?

Regards, Marek

[Rathinagiri]

Yes.

[Hazael]

This is HMGSQL library source code for bridging SQLite/MySQL/PostgreSQL.

Hello Rathinagiri,

I was using your hmgsql (very useful by the way) and I had a need to convert DATETIME (TIMESTAMP) values to SQLite so I added the following to your code:

Code: Select all

...
case Valtype(Value) == "T"
      cValue := DTOS( hb_TTOD( Value ) )   
otherwise
      cValue := "''"       // NOTE: Here we lose values we cannot convert
...

Maybe it could be added to your next releases, if you think it is good (or you can make it even better if you want).

[Rathinagiri]

That is so cool. Thanks and I will update.

[Hazael]

Hello Rathinagiri,

This is my first try with sqlcipher and I need some help.

Consider the following code:

Code: Select all

...
    nDb := connect2db( 'basecep.db', .T. )
    IF nDb == NIL
       ? "Connection error..."
       INKEY(0)
       Fim()
    ENDIF
	   
    IF miscsql( nDb, "PRAGMA key = 'test'" ) 
       ? "Encryption key is set"
    ELSE
	    ? "Error on pragma key command..."
		 INKEY(0)
	 	 QUIT
	 ENDIF
	 ////////////////////////////////////////////////////////////////////////////
    IF ! miscsql( nDb, 'CREATE TABLE IF NOT EXISTS CONTROLE' +;
                                '( VERSAO TEXT(4), '          +;
                                  'DATA   TEXT(8) );' )
        RETURN NIL
    ENDIF
...

And this is my test.hbp file:

Code: Select all

-lsddodbc 
-lodbc32 
-lrddsql 
-lhbsqlit3 
-lsqlite3

-lsqlcipher
-leay32

-L.

-run

test.prg

And I have the following files on the same directory:

Code: Select all

test.prg
test.hbp
libeay32.a
libeay32.dll
libsqlcipher.a
libssl32.dll

The problem is that the program is generated, works well but the file it creates is not encrypted... I do not understand why?
Any hints? Ideas? I am using Win 7 32bits and I am using Harbour nightly (not the one that comes with HMG) Maybe that's the problem but I would like to ask someone that has experience and could point something I am missing.

Any help is welcome

[Rathinagiri]

SQLCipher is already having the sqlite code. So you have to remove the line and try

Code: Select all

-lsqlite3 

[Hazael]

Hello Rathinagiri,

Now I understand (I hope)!
The problem is that I am using Harbour Nightly and mixing it with your sqlcipher lib...
The result is that they are *incompatible*
Look this Harbour Changelog:

2012-12-16 00:06 UTC+0100 Viktor Szakats (harbour syenar.net)
* contrib/3rd/sqlite3/sqlite3.hbp
* contrib/3rd/sqlite3/*
* updated to 3.7.15

* contrib/hbsqlit3/core.c
* contrib/hbsqlit3/hbsqlit3.hbx
+ added sqlite3_errstr(), new in 3.7.15
* minor cleanups

As you can see your lib will not have it 'sqlite3_errstr()' because it is based on some previous version.
This is the error I get: "undefined reference to 'sqlite3_errstr'"
One solution could be to downgrade to use only what is supplied with HMG but even so I will not be sure your lib is in the same version as HMG 3.1.1 (Test 2012-12-02) updated to Harbour Nightly Build (18706 2012-11-29).

I am using Nightly Build (18915 2013-03-12).

Other solution would be that I would have to build sqlcipher lib locally but for that I would have to download and configure some tools and I would prefer to avoid doing so for other reasons not to mention it is not that easy process.

Or you could help rebuilding sqlcipher based on the latest Harbour Nightly Build and make it available somewhere with information saying which Harbour version you used to build it. I would suggest this solution as the best and I suggest you to make it available here: site/ so anyone could have access to a centralized place although you could announce it on the HMG forum as many times as you want always pointing to the same place. Maybe sqlcipher lib deserves a "sub-project" inside HMG...

Of course it is up to you to do it or not. It is just my humble suggestion to make what is good even better.

What do you think?

[Rathinagiri]

Yes.

I am awaiting for the new SQLCipher sourcecode being updated for the latest version of SQLite. Until then, have to use the old sqlciper + hbsqlite library.