Page 3 of 11
Re: SQLCipher ported to Harbour (Windows)
Posted: Thu Dec 06, 2012 8:45 pm
by mol
I know sqlite. Bur I need sample how to use sqlcipher.
Re: SQLCipher ported to Harbour (Windows)
Posted: Fri Dec 07, 2012 4:15 am
by 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
Re: SQLCipher ported to Harbour (Windows)
Posted: Fri Dec 07, 2012 6:25 am
by 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
Re: SQLCipher ported to Harbour (Windows)
Posted: Fri Dec 07, 2012 6:35 am
by Rathinagiri
Yes.
Re: SQLCipher ported to Harbour (Windows)
Posted: Tue Mar 19, 2013 7:18 pm
by Hazael
rathinagiri wrote: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).
Re: SQLCipher ported to Harbour (Windows)
Posted: Tue Mar 19, 2013 7:28 pm
by Rathinagiri
That is so cool. Thanks and I will update.
Re: SQLCipher ported to Harbour (Windows)
Posted: Wed Mar 20, 2013 1:13 pm
by 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
Re: SQLCipher ported to Harbour (Windows)
Posted: Wed Mar 20, 2013 2:38 pm
by Rathinagiri
SQLCipher is already having the sqlite code. So you have to remove the line and try
Re: SQLCipher ported to Harbour (Windows)
Posted: Wed Mar 20, 2013 3:10 pm
by 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?
Re: SQLCipher ported to Harbour (Windows)
Posted: Wed Mar 20, 2013 4:37 pm
by 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.