File is encrypted or is not a database

[jchristof]

This appeared to be working last week, but queries no longer work against the newly encrypted database.

    string result = db.ExecuteScalar<String>("select * from multikey");

produces the error in the title. The file does exist and is encrypted.

    public void Encrypt(string newpassword) {
        string encryptedDb = _dbConnection + "_";

        File.Delete(encryptedDb);

        using (var db = new SQLiteConnection(_dbConnection, "")) {

            db.Execute(string.Format("ATTACH DATABASE '{0}' AS encrypted KEY '{1}'", encryptedDb, newpassword));
            db.ExecuteScalar<String>("select sqlcipher_export('encrypted')");
            db.Execute("DETACH DATABASE encrypted");
        }

        File.Copy(encryptedDb, _dbConnection, true);
        File.Delete(encryptedDb);

        using (var db = new SQLiteConnection(_dbConnection, newpassword)) {
            //fails
            string result = db.ExecuteScalar<String>("select * from multikey");
        }
    }

[developernotes]

Hi @jchristof

What is the result code of performing your ATTACH DATABASE … command? Also, it would be advised to close the database connection before you perform any File I/O operations. Can you pull the database file off the device and perform a hexdump -C yourDatabaseFile.db to verify the content of the database? Finally, the query of:

string result = db.ExecuteScalar<String>("select * from multikey");

will always fail as it will return a result set or 0 to N rows, so it can not be cast to a single string scalar value. You might try executing the following instead:

int records = db.ExecuteScalar<int>("select count(*) from sqlite_master;");

[jchristof]

    public void Encrypt(string newpassword) {
        string encryptedDb = _dbConnection + "_";

        File.Delete(encryptedDb);

        using (var db = new SQLiteConnection(_dbConnection, _dbPassword)) {
            //attachResult == 0
            int attachResult = db.Execute(string.Format("ATTACH DATABASE '{0}' AS encrypted KEY '{1}'", encryptedDb, newpassword));
            //exportResult == null
            string exportResult = db.ExecuteScalar<String>("select sqlcipher_export('encrypted')");
            //detachResult == 0
            int detachResult = db.Execute("DETACH DATABASE encrypted");
            db.Close();
        }

        //confirmed in storage 2 files db.s3db (unencrypted 25.0K) and db.s3db_(encrupted 27.0K)
        File.Copy(encryptedDb, _dbConnection, true);
        File.Delete(encryptedDb);

        //one remaining file db.s3db (encrypted with 'newpassword')

        using (var db = new SQLiteConnection(_dbConnection, newpassword)) {
            //throws "file is not a database or is encrypted"
            int records = db.ExecuteScalar<int>("select count(*) from sqlite_master;");
            db.Close();
        }
    }

If I comment out everything but the following code the execute scalar succeeds when performed against the unencrypted db file:

    public void Encrypt(string newpassword) {
        using (var db = new SQLiteConnection(_dbConnection, "")) {
            //succeeds
            int records = db.ExecuteScalar<int>("select count(*) from sqlite_master;");
            db.Close();
        }
    }

Picture of the first bytes of the encrypted file:

[jchristof]

Also, if I just skip the file copy operations once the db has been encrypted and address the file without changing it's name the same problems are there:

        using (var db = new SQLiteConnection(encryptedDb, newpassword)) {
            //throws "file is not a database or is encrypted"
            int records = db.ExecuteScalar<int>("select count(*) from sqlite_master;");
            db.Close();
        }

[developernotes]

Hello @jchristof

Are you starting off with a plain text SQLite database to begin with? Are you able to query any data from the database before you attempt to migrate the database to an encrypted SQLCipher database?

[jchristof]

Yes, the db is plain text - here's a hex of the file prior to being encrypted:

Also, yes I can query this file successfully as described in the prior post:

public void Encrypt(string newpassword) {
    using (var db = new SQLiteConnection(_dbConnection, "")) {
        //succeeds
        int records = db.ExecuteScalar<int>("select count(*) from sqlite_master;");
        db.Close();
    }
}

[sjlombardo]

@jchristof A couple more questions:

1. Could you try opening the resulting encrypted file with the sqlcipher command line tool? I would be interested to see whether the command line tool opens the file correctly.
2. Is the value of newpassword complex, containing any sequences that might need to be escaped in the rekey, i.e. single quote?

[jchristof]

Hi Stephan,

I'd like to attach the plain text and encrypted versions of the file for you to have access.

The encrypted version (.s3db_) is keyed with password '12345'

Thanks.

[jchristof]

Sorry for the urgency I'm getting down to the wire - any luck with the information? @sjlombardo I forwarded these files to you're email.

Thanks.

[developernotes]

Hello @jchristof

The links to your attached files are invalid, could you try resubmitting your plain-text database?

[jchristof]

I think the uploader accepts images only - I did attach and send them to Stephen and I can do the same for you if I can get you email.

[jchristof]

Also just tried this against the newly downloaded unencrypted db file:

        using (var db = new SQLiteConnection(_dbConnection, "")) {

            int attachResult = db.Execute("ATTACH DATABASE 'plaintext.db' AS plaintext KEY ''");

            string exportResult = db.ExecuteScalar<String>("SELECT sqlcipher_export('plaintext')");

            int detachResult = db.Execute("DETACH DATABASE plaintext");
            db.Close();
        }

No errors and no plaintext.db created.

[developernotes]

Hello @jchristof

I believe the issue you are seeing is due to your ATTACH statement not being properly parameterized. Could you try adjusting your attach statement to use the ? instead of a String.Format function. Something like this instead:

int result = db.Execute("ATTACH DATABASE ? as encrypted KEY ?;", encryptedDb, "12345");

[jchristof]

Code now:

    public void Encrypt(string newpassword) {
        string encryptedDb = _dbConnection + "_";
        try {
            File.Delete(encryptedDb);

            using (var db = new SQLiteConnection(_dbConnection, _dbPassword /*""*/)) {

                int attachRows = db.Execute("ATTACH DATABASE ? as encrypted KEY ?;", encryptedDb, newpassword);
                string scalar = db.ExecuteScalar<String>("select sqlcipher_export('encrypted')");
                int detachRows = db.Execute("DETACH DATABASE encrypted");
            }

            File.Copy(encryptedDb, _dbConnection, true);
        } catch (Exception e) {
            File.Delete(_dbConnection);
            throw (e);
        } finally {
            File.Delete(encryptedDb);
        }
    }

The very next time a connection is opened with the new key - fails at Prepare() - 'file is encrypted or is not a database'

Confirmed the first unencrypted file is present and confirmed the result encrypted file - all the files are being produced and the original is deleted as expected.

Is there confirmation of another Windows 8 Phone project where this is confirmed working?

[developernotes]

Hello @jchristof

We were able to open both your plain text database file as well as the encrypted database file with the SQLCipher command line tool. We were also able to open the plain text database file, attach, export and then open the encrypted database file, properly querying the count of the sqlite_master table. A couple of questions. What is the full path value of _dbConnection? What is the value of _dbPassword in your example? Here is an example test scenario that you could go through testing the export process:

public void Encrypt(string newpassword) {
    string encryptedDb = _dbConnection + "_";
    try {
        File.Delete(encryptedDb);

        using (var db = new SQLiteConnection(_dbConnection, "")) {

            int attachRows = db.Execute("ATTACH DATABASE ? as encrypted KEY ?;", encryptedDb, newpassword);
            string scalar = db.ExecuteScalar<String>("select sqlcipher_export('encrypted')");
            int detachRows = db.Execute("DETACH DATABASE encrypted");
        }

        using (var db2 = new SQLiteConnection(encryptedDb, newpassword) {
            var scalar = db2.ExecuteScalar<int>("select count(*) from sqlite_master;");
            if(scalar == 18) {
              // if the encrypted export worked, this should be true
            }
        }

        File.Copy(encryptedDb, _dbConnection, true);

        using (var db3 = new SQLiteConnection(_dbConnection, string.Empty) {
            try {
              scalar = db3.ExecuteScalar<int>("select count(*) from sqlite_master;");
              if(scalar == 18) {
                // if this works, then the file copy failed and the database at _dbConnection is actually unencrypted
              }
            } catch (Exception e) {
              // if an exception is thrown here then the database was likely encrypted and copied
            }

        }

    } catch (Exception e) {
        File.Delete(_dbConnection);
        throw (e);
    } finally {
        File.Delete(encryptedDb);
    }
}

[jchristof]

I changed the variables to better document their values. Error thrown again at

var scalar = db2.ExecuteScalar("select count(*) from sqlite_master;");

    public void Encrypt(string newpassword) {
        //confirmed, DB_WN4CHO1803_ERPDevelop.s3db exists at this path 25.0K should be the same file you guys already have same format etc.
        string _dbConnection = "C:\\Data\\Users\\DefApps\\AppData\\{F78E4A8A-95F8-49DF-A496-F3A0F6BF0DCF}\\Local\\DSI\\Mobile Client\\DB_WN4CHO1803_ERPDevelop.s3db";
        string encryptedDb = _dbConnection + "_";
        try {
            File.Delete(encryptedDb);

            using (var db = new SQLiteConnection(_dbConnection, "")) {

                int attachRows = db.Execute("ATTACH DATABASE ? as encrypted KEY ?;", encryptedDb, "12345");
                string scalar = db.ExecuteScalar<String>("select sqlcipher_export('encrypted')");
                int detachRows = db.Execute("DETACH DATABASE encrypted");
            }

            //confirmed, DB_WN4CHO1803_ERPDevelop.s3db_ now exists in the same directory is 27.0K
            using (var db2 = new SQLiteConnection(encryptedDb, "12345")) {
                //fails by throwing - 'file is encrypted or is not a database'
                var scalar = db2.ExecuteScalar<int>("select count(*) from sqlite_master;");
                if (scalar == 18) {
                    // if the encrypted export worked, this should be true
                }
            }

            File.Copy(encryptedDb, _dbConnection, true);

            using (var db3 = new SQLiteConnection(_dbConnection, string.Empty)) {
                try {
                    var scalar = db3.ExecuteScalar<int>("select count(*) from sqlite_master;");
                    if (scalar == 18) {
                        // if this works, then the file copy failed and the database at _dbConnection is actually unencrypted
                    }
                } catch (Exception e) {
                    // if an exception is thrown here then the database was likely encrypted and copied
                }

            }

        } catch (Exception e) {
            File.Delete(_dbConnection);
            throw (e);
        } finally {
            File.Delete(encryptedDb);
        }
    }

[developernotes]

Hello @jchristof

Could you submit a small sample application that only represents this particular issue you are seeing.

[jchristof]

Wow, the function worked in your demo - so I copied the contents of SQLite.cs from yours into ours - I find this in the diff:

    public static int sqlite3_key(Sqlite3DatabaseHandle handle, byte[] key, int length)
    {
        //return Sqlite3.sqlite3_key(handle, key, length);
                    return 1;
    }

    public static int sqlite3_rekey(Sqlite3DatabaseHandle handle, byte[] key, int length)
    {
        //return Sqlite3.sqlite3_rekey(handle, key, length);
                    return 1;
    }

One of our guys was unable to compile against rekey (he might still have the issue when grabs tomorrow he wouldn't have done this otherwise and probably accidentally checked this in) and removed the reference.

Works now.
Thanks a ton!

[developernotes]

Hi @jchristof

Thanks for getting back to us, we are glad to hear your issue is resolved.