Windows Sqlite Encrypted Database
roni...@live.it
Our intent is to produce an encrypted database on a windows server and
then download it with
a web service call on an ipad platform.
I paste the code and I will also for the group.
vb.net snippet program
Private Sub CreateEncryptedDb(ByRef cnlite As SQLiteConnection)
Dim provider As DbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SQLite")
Using SQLconnect As DbConnection =
provider.CreateConnection()
SQLconnect.ConnectionString = "Data Source=" &
sqlLiteFilenameEncrypted & ";Journal
Mode=Off;Synchronous=Off;Pooling=false;"
Dim ss As String = "SELECT name FROM sqlite_master WHERE
type IN ('table') AND name NOT LIKE 'sqlite_%' "
Using cmr As New SQLite.SQLiteCommand(ss, cnlite)
Dim dr As SQLite.SQLiteDataReader = cmr.ExecuteReader()
Dim s As String = "test123"
'ConfigurationHelper.GetAppConfigValue("EncryptedPwd")
DirectCast(SQLconnect, SQLiteConnection).SetPassword(s)
SQLconnect.Open()
Dim command As IDbCommand = SQLconnect.CreateCommand
command.CommandText = "CREATE TABLE t1(a,b);"
command.ExecuteNonQuery()
SQLconnect.Close()
Dim cmda As SQLite.SQLiteCommand =
cnlite.CreateCommand()
cmda.CommandText = "ATTACH '" &
sqlLiteFilenameEncrypted & "' AS disk KEY '" & s & "'"
cmda.ExecuteNonQuery()
While dr.Read
Dim cmd1 As SQLite.SQLiteCommand =
cnlite.CreateCommand()
Dim sStr As String = "select sql from sqlite_master
where name ='" & dr(0).ToString & "'"
cmd1.CommandText = sStr
cmd1.Connection = cnlite
Dim sSql As String = CStr(cmd1.ExecuteScalar())
cmd1.Dispose()
Dim cmd2 As IDbCommand = cnlite.CreateCommand()
cmd2.CommandText = "Create Table disk." &
dr(0).ToString & sSql.Substring(sSql.IndexOf("("), sSql.Length -
sSql.IndexOf("("))
cmd2.Connection = cnlite
cmd2.ExecuteNonQuery()
cmd2.Dispose()
Dim cmd3 As IDbCommand = cnlite.CreateCommand()
cmd3.CommandText = "Insert INTO disk." &
dr(0).ToString & " Select * from " & dr(0).ToString
cmd3.Connection = cnlite
cmd3.ExecuteScalar()
cmd3.Dispose()
End While
Dim cmdD As SQLite.SQLiteCommand =
cnlite.CreateCommand()
cmdD.CommandText = "DETACH disk "
cmdD.Connection = cnlite
cmdD.ExecuteNonQuery()
End Using
End Using
End sub
The iPad call a web service that returns my
'sqlLiteFilenameEncrypted' (for example c:\pippo.db')
Objective Snippet Program
const char *key = [@"test123" UTF8String];
sqlite3_key(database, key, strlen(key));
… AND HERE “File opened is not a database file”
Best regards
Stephen Lombardo
Stephen Lombardo
Cheers,
Michael Stephenson
Mabye I’m missing something, but from the code excerpts it looks like the encrypted database created on Windows was done via System.Data.SQLite.
Last I checked, System.Data.SQLite does not use the same encryption process as SQLCipher (it uses a very simplistic encryption, does not use per-page salt values, etc. Basically, it does not use any reserved space on database pages for salt values, initialization vectors, etc.).
Stephen Lombardo
roni...@live.it
we followed your instructions.
Our ipad implementation was on xcode 3.2 tutorial. Now we have
implemented with
new tutorial and downloaded the new libraries like you wrote.
The result is good.
Thanks to you and also to Michael
On 28 Nov, 15:08, Stephen Lombardo <sjlomba...@zetetic.net> wrote:
> Hi Michael,
>
> I probably should have clarified that in the thread. We offer a binary
> distribution of System.Data.SQLite (http://sqlcipher.net/downloads/) that
> is built using SQLCipher instead of the striped-down encryption in the
> default build. Roni is using our distribution in his application.
>
> Sorry about the confusion!
>
> Cheers,
> Stephen
>
> On Mon, Nov 28, 2011 at 8:57 AM, Michael Stephenson
> <domehead...@gmail.com>wrote:
>
>
>
> > Mabye I’m missing something, but from the code excerpts it looks like the
> > encrypted database created on Windows was done via System.Data.SQLite.****
>
> > ** **
>
> > Last I checked, System.Data.SQLite does not use the same encryption
> > process as SQLCipher (it uses a very simplistic encryption, does not use
> > per-page salt values, etc. Basically, it does not use any reserved space on
> > database pages for salt values, initialization vectors, etc.).****
>
> > ** **
>
> > *From:* sqlc...@googlegroups.com [mailto:sqlc...@googlegroups.com] *On
> > Behalf Of *Stephen Lombardo
> > *Sent:* Friday, November 25, 2011 4:51 PM
> > *To:* sqlc...@googlegroups.com
> > *Subject:* Re: Windows Sqlite Encrypted Database****
>
> > ** **
>
> > Hi Roni****
>
> > ** **
>
> > Databases generated on windows are definitely usable on the iPad, so there
> > is likely a problem with the way SQLCipher is integrated on one side or the
> > other. In order to narrow this down, you should take the following steps:*
> > ***
>
> > ** **
>
> > 1. Generate a database using the method you described above via .NET****
>
> > 2. Open the database using a text editor - make sure that the first few
> > bytes of the file do not say "SQLite3 version 3"****
>
> > 2a. If the database is not encrypted, then there is a problem with the
> > way you are using the provider, and we'd need to see your app config, etc.
> > ****
>
> > 3. Provided the database appears encrypted, open the database up using the
> > SQLCipher command line tool (sqlite3.exe). Verify that when providing the
> > correct pragma key as the first operation you are able to execute
> > statements against the file.****
>
> > 3.a If the database is encrypted but you are not able to open the file
> > using the command like with pragma key, there may be an issue with the way
> > you are setting the key (perhaps due to encoding being used).****
>
> > 4. If both steps 2 and 3 are working properly, and you can open the file,
> > then the most likely cause is that the iPad application is not properly
> > built with SQLCipher support. There is a new tutorial out on our site which
> > walks through every facet of the iOS build setup - you could check your
> > current configuration against this and see.****
>
> > ** **
>
> > Please let us know what you find. ****
>
> > ** **
>
> > Cheers,****
>
> > Stephen****
>
> > ** **
>
> > ** **
>
> > On Thu, Nov 24, 2011 at 6:55 AM, roni....@live.it <roni....@live.it>
> > wrote:****
> > Best regards****
>
> > ** **