search

yiskang / csharp-sqlite

Automatically exported from code.google.com/p/csharp-sqlite
Other
0 stars 0 forks source link

csharp-sqlite created encrypted database not compatible with 3rd party SQLite admin programs? #143

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
What steps will reproduce the problem?
1. Create an encrypted database (through SQLiteClient, C# code):

SqliteConnection dbCon = new SqliteConnection();
dbCon.ConnectionString = Version=3,Compress=True,uri=file:Database.db";

DbCommand cmd = dbCon.CreateCommand();
cmd.CommandText = "PRAGMA hexkey=\"0x54657374696E672070617373776F7264\";";
cmd.ExecuteNonQuery();

cmd.CommandText = "CREATE TABLE [Test] ([ID] INTEGER NOT NULL PRIMARY KEY 
AUTOINCREMENT, [Name] VARCHAR(40) NOT NULL);";
cmd.ExecuteNonQuery();

dbCon.Close();

2. Open the database using a 3rd party administration tool setting the 
encryption key (either hex or ASCII).

What is the expected output? What do you see instead?

It is expected to be able to author the database, open and access it correctly; 
instead it fails stating that the key is invalid. Tested 3rd party 
administration programs: SQLite Expert Pro, SQLite Maestro and SQLite Developer 
(hence I think I should have something to do with the library).

What version of the product are you using? On what operating system?

Latest csharp-sqlite 3.7.7.1.
OS doesn't factor, but XP and 7 could be used if required.

Original issue reported on code.google.com by j.russel...@gmail.com on 8 Mar 2012 at 9:15

GoogleCodeExporter commented 8 years ago 
2 questions: 
1) Are you using the source repository or the zip file?
2) Can you reproduce with the shell, rather than the client modules?

Original comment by noah.hart@gmail.com on 8 Mar 2012 at 11:19

GoogleCodeExporter commented 8 years ago 
Right now I did a fresh checkout from the repository to perform the tests, I 
believe I was using a checkout of the repository (about 1 month old) because of 
the warning displayed when downloading the zip file.

I'm unable to get the shell to work, as far as I know "C#-SQLite3.exe Test.db" 
should open or create a file called Test.db but when in the shell executing 
.databases shows "main" pointing to nothing. Trying to open an existing 
database doesn't seem to work either.

Compiling the Zip version instead of the current state of the repository leads 
to a working shell, able to create databases; but the outcome is the same as 
using the client.

Attached there are the binary files I'm using right now, debug versions 
compiled for any CPU architecture. There were some warnings during compilation 
regarding some unreachable code in shell and some tests in the latest trunk, 
there're attached in the text file.

A test database along with the commands issued in the shell to create it is 
attached too.

PS. I'm using an up to date VS2010 by the way.

Original comment by j.russel...@gmail.com on 9 Mar 2012 at 4:32

Attachments:

GoogleCodeExporter commented 8 years ago 
Shell expect extra argument, new issue # 145 entered;  
Work around is add a dummy argument for the command line: C#-SQLite3 foo 
test1.db

Encryption does work from the shell as follows:
W:\temp\C#-SQLite>C#-SQLite3
SQLite version 3.7.7(C#)
(source 2011-06-23 19:49:22)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA hexkey="0x54657374696E672070617373776F7264";
sqlite> ATTACH "ENCRYPT.DB" AS E;
sqlite> create table e.Table1 (Column1 text);
sqlite> .quit

W:\temp\C#-SQLite>C#-SQLite3
SQLite version 3.7.7(C#)
(source 2011-06-23 19:49:22)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> ATTACH "ENCRYPT.DB" AS E;
Error: file is encrypted or is not a database
sqlite> PRAGMA hexkey="0x54657374696E672070617373776F7264";
sqlite> ATTACH "ENCRYPT.DB" AS E;
sqlite> .quit

Tested 3rd party administration programs: SQLite Expert Pro, SQLite Maestro and 
SQLite Developer use the native SQLite library which does not support 
encryption;

Original comment by noah.hart@gmail.com on 9 Mar 2012 at 5:14