Encryption, Decryption

[Doug]

Hi, I'm new to sqlcipher, so I'm trying to work through some of the details in the doc. Please bear with me, I'm learning. I implemented the demoapp in android, which appears to be working, i.e., the db is encrypted. This is fine when the db is at rest, but I want to be able to decrypt the same db when the user is in the app so they have access to the data.

So, trying to do this, I used the sqlcipher export function, which isn't working for me. I'm able to attach to a plain text db, but when I issue the sqlcipher export command I get the error:
   Error: near '(" syntax error

Here are the statements:

First I issue the sqlite3 command:
sqlite3 plaintext.db

Next I attached to the encypted (which appears to it) db:
ATTACH DATABASE 'encrypted.db' AS encrypted 'testkey'

As I stated, this works fine.

Here is where the error occurs:

SELECT sqlcipher exprt('encrypted');
Error: near '(" syntax error

Now, I'm using Windows Xp, but I don't think that should make a difference, but maybe it does.

Also, will I have to worry about 2 db on the device, one plaintext and the other encrypted? Because, it that's so, wouldn't that defeat the purpose of encryption?

[Nick Parker]

Hi Doug,

I want to touch on two different items.  First, you can allow access to the encrypted database content without creating a secondary exported database.  The important part is that you have to provide the key to the database that was used to encrypt it.  An example:

sqlcipher encrypted.db

PRAGMA key = '[YourPasswordHere]';

SELECT * FROM SomeTableName;

Secondly, the error message you are getting is due to the format of the query being issued. The command function you want to call is 'sqlcipher_export', note the underscore.  So in the case that you have a plain text database and want to encrypt it with the command line tool, you might do something like:

sqlcipher plaintext.db

ATTACH DATABASE 'encrypted.db' as encrypted KEY 'SomePassword';

SELECT sqlcipher_export('encrypted');

DETACH DATABASE encrypted;

Finally, you might have noticed that the binary names for the shell (i.e., sqlcipher in my examples) are not called sqlite3.  We recently renamed the binaries in the latest 2.2.0 release.  sqlite3 as a command shell will work for you as long as you are using a SQLCipher build, pre 2.2.0.  Without having a SQLCipher build, the encryption will not exist, nor with the sqlcipher_export connivence function.

Nick Parker

Nick Parker

--
 
---
You received this message because you are subscribed to the Google Groups "SQLCipher Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlcipher+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Mark Murphy]

On Tue, Jul 2, 2013 at 10:28 AM, Doug <29d...@gmail.com> wrote:
sqlcipher_export> Here is where the error occurs:

>
> SELECT sqlcipher exprt('encrypted');
> Error: near '(" syntax error

That should be sqlcipher_export().

For example, here is a static Java method for an Android app that will
convert an unencrypted database into an encrypted one:

public static void encrypt(Context ctxt, String dbName,
String passphrase) throws IOException {
File originalFile=ctxt.getDatabasePath(dbName);

if (originalFile.exists()) {
File newFile=
File.createTempFile("sqlcipherutils", "tmp",
ctxt.getCacheDir());
SQLiteDatabase db=
SQLiteDatabase.openDatabase(originalFile.getAbsolutePath(),
"", null,
SQLiteDatabase.OPEN_READWRITE);

db.rawExecSQL(String.format("ATTACH DATABASE '%s' AS encrypted KEY '%s';",
newFile.getAbsolutePath(), passphrase));
db.rawExecSQL("SELECT sqlcipher_export('encrypted')");
db.rawExecSQL("DETACH DATABASE encrypted;");

int version=db.getVersion();

db.close();

db=
SQLiteDatabase.openDatabase(newFile.getAbsolutePath(),
passphrase, null,
SQLiteDatabase.OPEN_READWRITE);
db.setVersion(version);
db.close();

originalFile.delete();
newFile.renameTo(originalFile);
}
}

(taken from https://github.com/commonsguy/cwac-loaderex/blob/master/src/com/commonsware/cwac/loaderex/SQLCipherUtils.java)

> Also, will I have to worry about 2 db on the device, one plaintext and the
> other encrypted? Because, it that's so, wouldn't that defeat the purpose of
> encryption?

You would delete the unencrypted one and use the encrypted one going
forward, after the conversion.

--
Mark Murphy (a Commons Guy)
http://commonsware.com | http://github.com/commonsguy
http://commonsware.com/blog | http://twitter.com/commonsguy

Localized Android Question-and-Answer Sites: http://www.andglobe.com

[Doug]

>Secondly, the error message you are getting is due to the format of the query being issued. The command function you want to call is 'sqlcipher_export', note the underscore.  So in the case that you have a >plain text database and want to encrypt it with the command line tool, you might do something like:

Thanks for the help. I am now using the underscore, but getting the following error: No Function: sqlcipher_export. I suspect that I need to download more than the binaries Sqlcipher for Android on the site. Is that correct?

 

>Finally, you might have noticed that the binary names for the shell (i.e., sqlcipher in my examples) are not called sqlite3.  We recently renamed the binaries in the latest 2.2.0 release.  sqlite3 as a command >shell will work for you as long as you are using a SQLCipher build, pre 2.2.0.  Without having a SQLCipher build, the encryption will not exist, nor with the sqlcipher_export connivence function.

So, I suppose with this release the prompt will be sqlcipher on the command line? Also, I'm trying to get things working at the command prompt before I begin doing things in code, just to get a feel for sqlcipher.

Again, thanks for your help.

Doug

[Doug]

Mark,

Thanks for the code. It will help me after I finish trying things at the command prompt. This great.

Doug

[Nick Parker]

Hi Doug,

Yes, you will need a SQLCipher command line client.  Here [1] are instructions for building from source on a *nix or Mac machine.

1.  http://sqlcipher.net/introduction/

Nick Parker

[Doug Pettus]

Hi Nick,

I gather the client is either not available for Windows or is something arduous to set up. Is that correct? Since l've downloaded the android binaries (they appear to be working) l supposed it might be less time consuming to just work in eclipse. I was thinking it might be worth it to begin with the command line,. But maybe not. Your thoughts?

Doug

You received this message because you are subscribed to a topic in the Google Groups "SQLCipher Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlcipher/epNMFAXKZ3M/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlcipher+...@googlegroups.com.

[Stephen Lombardo]

Hi Doug,

There are a few good options on windows.

First, you can build SQLCipher for windows under MinGW or Cygwin in a fairly straightforward way. These tools provide a *nix compatible build chain for windows, so the command line instructions are quite similar. There is more information on the process and common issues in this thread:

https://groups.google.com/forum/#!topic/sqlcipher/VcYpa1a_RTM

Alternately, if you prefer Visual Studio, It's possible to generate the sqlcipher amalgamation on a compatible host (sqlite3.c and shell.c) and then compile them under VS, provided that you obtain an OpenSSL library for windows to build against.

Finally, you can purchase a pre-built commercial edition command line tool from the SQLCipher store, which is a quick and easy way to get the windows tool without having to build anything yourself:

http://sqlcipher.net/commercial-edition-ordering

Thanks!

Cheers,

Stephen