SQLite Security

silvercover · Post by **silvercover** » Sat Oct 18, 2008 10:51 pm

Hi,

I planned to use SQLite in my application. my question is how can I prevent unwanted access to my database and generally how can we protect our databases while they are running locally?

Thanks in advance.

blueznl · Post by **blueznl** » Sun Oct 19, 2008 3:16 pm

AFAIK not... perhaps thru the SQLite API but not via a PureBasic directly.

pdwyer · Post by **pdwyer** » Mon Oct 20, 2008 12:33 am

Only normal file permissions for the db file itself

dige · Post by **dige** » Tue Oct 21, 2008 7:31 am

Or you encrypt / decrypt the whole db file and use a :memorydb: during runtime..

silvercover · Post by **silvercover** » Tue Oct 21, 2008 12:21 pm

Thank you guys.

dige,

where should I use this :memorydb:? in my query or what?

Thanks again.

pdwyer · Post by **pdwyer** » Tue Oct 21, 2008 12:34 pm

You'd have to put the decrypted file back on the disk though temporarily to read it into a :memory: db though wouldn't you?

@Silvercover, search the forums for ":memory:" and you should find it

dige · Post by **dige** » Tue Oct 21, 2008 4:13 pm

yes, like pdwyer told you... decrypt the dbfile, open a memory database and
copy the content.
its very easy, just read the sqlite_master table, to build a general solution.

if you need it once more safer, do not save the memory database content into a sqlite file, but rather into a memory block, cryp and save the memory afterwards..

silvercover · Post by **silvercover** » Mon Oct 27, 2008 7:17 am

When we have encrypted our date and save them, how can we show encrypted data when needed. I mean as far as I know there must be 2 way encryption/decryption method.

I know how to shape SELECT query with encrypted data but what to do with encrypted results?

Thank you guys.

dige · Post by **dige** » Mon Oct 27, 2008 8:46 am

If you save encrypted data into the database, you can only use SELECT * , bcoz the data are not readable for database engine and lose a lot of SQL power

What we mean is, encrypt / decrypt the whole datafile...

pdwyer · Post by **pdwyer** » Mon Oct 27, 2008 10:28 am

I think you mean you can't use LIKE %%, not select * which is just columns

dige · Post by **dige** » Mon Oct 27, 2008 12:59 pm

pdwyer wrote:I think you mean you can't use LIKE %%, not select * which is just columns

Yes, you're right. I mean you can't use the WHERE clause

pdwyer · Post by **pdwyer** » Mon Oct 27, 2008 1:06 pm

I suppose (thinking off the top of my head here) if you used a byte for byte simple encryption like xor, you might be able to match the search string with the encryption... for xor to be any good though you'd need a very long random key, which would make searching on it a problem

there might be some way to do it though.

hmmm, maybe not, unless the encryption was almost useless

silvercover · Post by **silvercover** » Mon Oct 27, 2008 3:24 pm

@pdwyer

No I have not used byte level encryption.

if you need it once more safer, do not save the memory database content into a sqlite file, but rather into a memory block, cryp and save the memory afterwards..

I don't know how to do this.

pdwyer · Post by **pdwyer** » Tue Oct 28, 2008 12:41 am

SQLite web page implies that there is an encryption feature, but I'm not sure if it's an add on for purchase though.

Looks like they are 3rd party tools like http://sqlite-crypt.com/

How secure does this need to be?

silvercover · Post by **silvercover** » Tue Oct 28, 2008 4:43 am

Mainly SQLite engine does not have native security protections. SQLite owners only offer a component for commercial purposes to be set on basic engine and that will be cost 2000 $. on the other side we can implement our security methods such as encryption.

At least basic protection would be OK.