How to close SQLite Connection/File before closure of application?
rathinagiri subbiah
I am using HBSQLite for a single user application.
At the end of the application, I wish to encrypt the database and
delete the original database file. I could not delete the file from
inside the application because, the file is in use.
Actually when the application exits the database file is also closed
automatically.
How to close this file either automatically or forcefully? Or, can I
run a module just after the closure of the database and before closure
of the application? (SQLite3_close() wrapper function is called via
HB_Garbage function.)
Can anybody help me?
S. Rathinagiri
Gmail
I am not sure but try it:
db:CLOSE()
BTW, are you using SQLCypher? I am interested in it but didn't get it to
work yet. I just want to know other's experiences about it, please.
Regards,
Qatan
> --
> You received this message because you are subscribed to the Google
> Groups "Harbour Users" group.
> Unsubscribe: harbour-user...@googlegroups.com
> Web: http://groups.google.com/group/harbour-users
rathinagiri subbiah
Also I am not using object oriented SQLite connection. I am using the
direct c wrapper functions. I will try object:close() today and let
you know.
Regarding encryption, this is my way of doing. My DB is initially AES
encrypted. When the user gives the password, the DB file in decrypted
to a random directory location. When the application is closed, the
updated DB is encrypted again to the original location and I wish to
remove the decrypted database in the random directory location.
Giri.
Paola Bruccoleri
To close the database within your application, simply do this.
This works well
db := sqlite3_open(cBase, .t.)
.........
// close db....
db:=""
byeeeeeeee
rathinagiri subbiah
I have used 'db := Nil' already. I have tried db := "" now.
But even then the database file is not closed. ie., I could not delete
the database file from within the application.
Giri.
On Wed, Feb 1, 2012 at 7:43 PM, Paola Bruccoleri
<pbruc...@adinet.com.uy> wrote:
Gmail
Anyway why not use SQLCypher?
Qatan
----- Original Message -----
From: "rathinagiri subbiah" <srathi...@gmail.com>
To: <harbou...@googlegroups.com>
Sent: Wednesday, February 01, 2012 12:21
Subject: Re: [harbour-users] How to close SQLite Connection/File before
closure of application?
Hi,
I have used 'db := Nil' already. I have tried db := "" now.
But even then the database file is not closed. ie., I could not delete
the database file from within the application.
Giri.
On Wed, Feb 1, 2012 at 7:43 PM, Paola Bruccoleri
<pbruc...@adinet.com.uy> wrote:
> El 31/01/2012 07:25 a.m., rathinagiri subbiah escribi�:
rathinagiri subbiah
compatible version for mingw?
On Wed, Feb 1, 2012 at 8:33 PM, Gmail <wanst...@gmail.com> wrote:
> Maybe it is not possible but it would be interesting to know.
> Anyway why not use SQLCypher?
>
>
> Qatan
>
> ----- Original Message ----- From: "rathinagiri subbiah"
> <srathi...@gmail.com>
> To: <harbou...@googlegroups.com>
> Sent: Wednesday, February 01, 2012 12:21
> Subject: Re: [harbour-users] How to close SQLite Connection/File before
> closure of application?
>
>
>
> Hi,
>
> I have used 'db := Nil' already. I have tried db := "" now.
>
> But even then the database file is not closed. ie., I could not delete
> the database file from within the application.
>
> Giri.
>
>
>
> On Wed, Feb 1, 2012 at 7:43 PM, Paola Bruccoleri
> <pbruc...@adinet.com.uy> wrote:
>>
Gmail
First, I wrote it wrong. The correct is SQLCipher: http://sqlcipher.net/
I never tried it but I want to (someday...) In case you get it to work
let me know how!
What I have are Petr Chornyj's instructions. Here is what he wrote to
me: (never tested it)
----- Original Message -----
From: "P. Chornyj" <myo...@mail.ru>
To: <harbou...@googlegroups.com>
Sent: Thursday, December 09, 2010 09:57
Subject: Re[2]: [harbour] SF.net SVN: harbour-project:[15925] trunk/harbour
Hello Qatan,
> Do you use SQLite on Harbour under Windows?
Yes
> It seems that it is kind of difficult to create a lib for security based
> on SQLCIPHER but I am not sure. I belive it would be very interesting if
> we
> could find a way to have it working in a easy way.
> On their website I found this statement: "Depending on your environment,
> particularly for Windows developers, getting the build process right can
> be
> tricky"
Very easy to build SQLCIPHER with MinGW + MSYS
So all what you need to do it's:
1) Download mingw-get installer from http://sourceforge.net/projects/mingw/
to the C:\MinGW directory.
Add C:\MinGW\bin to the PATH environment variable.
Install MinGW Compiler Suite (C, C++) and MinGW Developer ToolKit.
2) Download and install Tcl (I use ActiveTcl from
http://www.activestate.com/activetcl/downloads)
3) Download SQLCIPHER and extract the files to the
C:\MinGW\msys\1.0\home\__YourUserName__\sqlcipher
4) Download OpenSSL for Windows binary (direct link is
http://www.slproweb.com/download/Win32OpenSSL-1_0_0c.exe).
and install to (f.e.) C:\OpenSSL
5) Run msys.bat. and execute the command (you can use copy/paste)
cd sqlcipher
./configure --disable-tcl --disable-amalgamation
CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_OMIT_DEPRECATED
-I/c/OpenSSL/include /c/OpenSSL/libeay32.dll"
make
make dll
make install
6) Rebuild hbsqlit3 with -cflag=-DSQLITE_OMIT_LOAD_EXTENSION
7) With hbmk2 create import library for OpenSSL dll's
If you use Harbour with HB_WITH_OPENSSL you already have OpenSSL binary and
import libraries.
Regards,
Petr
>> El 31/01/2012 07:25 a.m., rathinagiri subbiah escribi�:
rathinagiri subbiah
Here too, sqlite file is not closed when oConn:close() is called or oConn := nil
Here is the code:
oConn := hdbcSQLTConnection():New( "test.db", .T. )
cSql := 'CREATE TABLE test('
cSql += ' Code integer not null primary key, '
cSql += ' dept Integer, '
cSql += ' Name Varchar(40), '
cSql += ' Sales boolean, '
cSql += ' Tax Float4, '
cSql += ' Salary Double Precision, '
cSql += ' Budget Numeric(12,2), '
cSql += ' Discount Numeric (5,2), '
cSql += ' Creation Date, '
cSql += ' Description text ) '
oStmt := oConn:createStatement()
oStmt:executeUpdate( cSql )
oStmt:Close()
oConn:close()
x := ferase( 'test.db' )
? str( x ) )
Regarding SQLCipher, I have not tried. Let me go through your steps
and try too. Thanks.
Gmail
oSmt := NIL
oConn := NIL
Just to test it after oSmt:CLOSE() and oConn:CLOSE() and before FERASE()
I hope SQLCipher will be a much nicer option (if it works...)
Qatan
Here is the code:
oConn:close()
>>> El 31/01/2012 07:25 a.m., rathinagiri subbiah escribi�:
rathinagiri subbiah
Yes, before this, I have used truecrypt. (from truecrypt.org) which is
a perfect virtual file/disk encryption system. However, in windows 7
it always asks for User Authentication to mount/dismount a local
virtual drive which is very much annoying.
Gmail
I don't use Windows 7 but I believe there is a way to go around this
problem although I still think SQLCipher is worth of a try.
Regards,
Qatan
>>>> El 31/01/2012 07:25 a.m., rathinagiri subbiah escribi�:
francesco perillo
I went to contrib/hbsqlit3/core.c source code and found that there is no "close" function at prg level.
At c level it is used to recover form an erroneous open and in the destructor.
hbsqlite3 layer uses a "deferred deletion" of objects, like hbQt does. So you may do at prg level:
oConn := NIL or "" or 2....
you will "detach" the c object from oConn but IT IS NOT DELETED at c level. And close() not called.
When GC runs, it will scan all these areas, asking the hbsqlite library: is this block of ram still used (function hb_sqlite3_mark) ? inside this block there are references to other harbour objects (so I understand from the code) and these objects must be free from prg code before closing...
It is also reported on the sqlite web site:
http://www.sqlite.org/c3ref/close.html
One of the nested object is pointer ->db... this is set but never set to 0, so memory can't be deallocated....
I'll open a new thread in developers mailing list....
francesco perillo
One of the nested object is pointer ->db... this is set but never set to 0, so memory can't be deallocated....
Rathi, I was wrong on this, db pointer is not checked at mark time so should not be the fault.....
I followed your sample code and it seems to me that there may be objects references still around after
oStmt:Close()
oConn:close()
Infact object oStmt has a reference to ::pDB set at createStatement time and not reset at close() time.
So you may start with trying:
oStmt:Close() //should release all the references
oConn:close() // idem
oStmt := NIL
oConn := NIL
hb_gcall( .T. ) <- force GC run
It this still doesn't work you must add traces into core.c mark and release functions to see what blocks harbour GC to call close() and release the object...
Francesco
rathinagiri subbiah
You had hit the nail on the head! Great man!
hb_gcall( .T. )
Thanks a LOT! The above statement works like a charm!
With thanks and regards,
Giri.
Gmail
I am glad you found the expected results.
It suggests that the code should be fixed somehow to avoid such problem.
Is there anyway that hb_gcall(.T.) could be automatically executed from
inside of the sources of the SQLite wrapper?
I don't know the answer but I believe it is perfectly possible to be
done
by anyone that knows what he is doing. (unfortunately I don't).
Regards,
Qatan
----- Original Message -----
From: "rathinagiri subbiah" <srathi...@gmail.com>
To: <harbou...@googlegroups.com>
Sent: Thursday, February 02, 2012 01:47
Subject: Re: [harbour-users] How to close SQLite Connection/File before
closure of application?
rathinagiri subbiah
Thanks to all. This helped me in creating an encrypted database with
password protection.
This is my way of encryption/decryption of a SQLite database kept in
secret. If there is any flaw in this please guide me.
1. We have an AES encrypted database.
2. When the password is given the encrypted database is decrypted
inside a random directory which is created at the runtime.
3. All operations are done to this decrypted database.
4. Once the application is closed, the database is encrypted again to
the original location.
5. Now, the decrypted database is deleted and the random directory is
removed from the system.
Gmail
I belive this way may avoid someone curious but it is not safe at all.
I could use a free utility (I do not remember the name) to see on the
fly where you are decrypting the database and make a shadow copy of it when
it is still decrypted. Do you understand it?
I could also use a recovery program to get the deleted database that was
decrypted. So you have at least two simple alternatives to go around your
securty system. Just to point what flaw I can see at this moment.
So I still belive SQLCipher would be a much safer solution.
rathinagiri subbiah
francesco perillo
Rathi,
did you try with only:
> oStmt := NIL
> oConn := NIL
rathinagiri subbiah
Actually I have not FINALIZED and prepared statement in my program.
So, the BUG is mine only. Sorry for any disturbance caused. :(
Gmail
We all learned from that.
Qatan
----- Original Message -----
From: "rathinagiri subbiah" <srathi...@gmail.com>
To: <harbou...@googlegroups.com>
Sent: Thursday, February 02, 2012 07:28
Subject: Re: [harbour-users] How to close SQLite Connection/File before
closure of application?
> You are just right Francesco. How I have missed it before, I don't know!
>
> Actually I have not FINALIZED and prepared statement in my program.
>
> So, the BUG is mine only. Sorry for any disturbance caused. :(
>