Encryption and SQLite

[lohill]

When my program starts, it checks to see it it's database is present. If the database is not found, it create 'SimplePass.db' in the Documents folder. In order to create this file it uses 3 custom variable in the stack. The custom variables are cDatabaseStructure, cRootSQL and cAccountSQL which are defined as follows:

cDatabaseStructure:

Code: Select all

CREATE TABLE root (
  rKey integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  cipher char(64),
  rootKey char(64),
  simplePW blob
);

CREATE TABLE account (
  aKey integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  accountname char(64) NOT NULL,
  accounttype char(64),
  username char(64),
  password blob,
  website nvarchar,
  note nvarchar(128)
);

cRootSQL

Code: Select all

INSERT INTO root (cipher, rootkey, simplePW)
VALUES ('aes-256-cbc', 'Heather', 'öÀ$Ùm∑÷de(æû3Ë©hàÓË>›¶l')

cAccountSQL

Code: Select all

INSERT INTO account (accountname,accounttype,website)
VALUES ('Amazon Marketplace','Login','https://www.amazon.com/ap/signin?openid.pape.max_auth_age=0&openid.return_to=https%3A%2F%2Fwww.amazon.com%2F%3Fref_%3Dnav_signin&openid.identity=http%3A%2F%2Fspecs.openid.net%2Fauth%2F2.0%2Fidentifier_select&openid.assoc_handle=usflex&openid.mode=checkid_setup&openid.claimed_id=http%3A%2F%2Fspecs.openid.net%2Fauth%2F2.0%2Fidentifier_select&openid.ns=http%3A%2F%2Fspecs.openid.net%2Fauth%2F2.0&')

Here is an excerpt of the code that create the database:

Code: Select all

         put the cDatabaseStructure of this stack into tSQL
         put revOpenDatabase("sqlite", tPath,"binary") into tDatabaseID 
         revExecuteSQL tDatabaseID, tSQL
         put the result into tResult1
         put the cRootSQL of this stack into tSQL
         revExecuteSQL tDatabaseID, tSQL
         put the result into tResult2
         put the cAccountSQL of this stack into tSQL
         revExecuteSQL tDatabaseID, tSQL
         put the result into tResult3

When I step through my code I see that tResult1=0, tResult2=1 and tResult3=1. If I then use the application SQLite Professional to examine the resulting SimplePass.db I see everything as expected except for one thing. The single record in the table root has a NULL value for the field simplePW. Originally I had used a field type of 'char' but that had resulted in a NULL value too. The interesting thing is that I can use the SQL for inserting into the root table while in SQLite Professional and inset a second record into the table that has those missing characters.

It is like LiveCode doesn't want to write those encrypted characters. Any ideas?
Larry

[lohill]

This message has been out here almost a week and been read my over 200 people. No one has made a comment on my thesis that encrypted data and SQLite databases do not appear to work well together. I know there are people on this forum qualified to make a comment so I must not have expressed myself very well. I will try again by attaching a sample stack that shows why I have confusion about what I see.

If you bring up the stack you will see on the left a series of fields and buttons where you can test encryption and encoding for whatever you type in the field "Source". You can encrypt (or encode) it and then immediately decrypt (or decode) it to get back the original piece of source data. You will notice that if you encode some source data, the next time you try to encode the sane source you get exactly the same result. Conversely, if you encrypt the same source data multiple times you get something different each time.

Any encryption you do is also affected by the contents of the Cipher field and the CipherKey field. For these first experiments I suggest you leave these fixed and just change the source. Each test you do gets recorded in the datagrid at the upper right of the screen where the type (Encrypt or Encode), the source and the result are shown. Behind the scenes those three pieces of data are also being recorded in a database. That database can be found in your "Documents" folder and is called 'dbTest.db'. That database was created when you first opened the stack.

After you have encrypted and/or encoded a number of source words and before you have worried about what is in the database, get some 3rd party program that opens a SQLite databases and take a look at the data in .../documents/dbTest.db. You will see both source and results wherever the type is Encode but in all cases of type Encrypt you will see that the 'results' are NULL. Something seems to get lost when saving encrypted data to SQLite.

Now press the button called 'Load From Database' which is just above "Datagrid 2". This will read in the data from dbTest.db. This is where I got another surprise. All the records, Encrypt and Encode, have data in the results column. In the case of type Encrypt, however, the 'result' values do not agree with those in DataGrid 1. You can go one step further and use your 3rd party SQLite application to INSERT some encrypted data into dbTest.db. The data seems to go in OK.

This only seems explainable to be as a bug in Livecode or a bug in SQLite . Do you have any other explanation?

Larry

[bangkok]

Hello.
A few comments, after using your stack.

if you use an online SQL editor such as :

https://sqliteonline.com/

You will see that the field "result" has indeed content.... Binary (but the word "salted" is clearly visible).
Screen 1

If you use DB Browser for SQLite (software) "result" looks "empty", but it's not.
Screen 2

Furthermore, with your button "load from database"... the data are indeed loaded !
Another proof, that "result" is not NULL.

So it seems that... everything is normal.

i think you have a problem with the "third party SQLite editor" you use (that is unable to display properly.... binary data)

[lohill]

Bangkok,

Thanks for taking a look and thanks for the reference to sqliteonline.com. If everything 'is normal' as you say then can I expect to be able to retrieve encrypted data from the database and, as long as I know the cipher and the cipherKey, I should be able to decrypt it back to the original source?

Larry

[bangkok]

Thanks for taking a look and thanks for the reference to sqliteonline.com. If everything 'is normal' as you say then can I expect to be able to retrieve encrypted data from the database and, as long as I know the cipher and the cipherKey, I should be able to decrypt it back to the original source?

I'm not sure to understand.

Your problem was that, according to a third party tool, a field seemed to be NULL in your SQLlite DB, despite that the fact that your were able, in Livecode, to read it, and to display its content in a datagrid, right ?

So, if the data are stored correctly, and if you know the key, so yes you should be able to decrypt the data.

Unless there is some kind of confusion regarding the format of the data stored in the database (binary or not then, base64encode or not etc.)
(it's late now, i will look at your stack again tomorrow)

*******************

OK here is a lead : after encrypt, do a base64encode (convert binary to text)
write this text into your DB

And when you read the DB, do a base64decode on the data, before to decrypt it.

[lohill]

Good morning Bangkok,

My ultimate goal in all this testing is to convince myself that I can encrypt some data and store it in a SQLite database and that at some point i can retrieve the data from the database and decrypt back to its original self.

I have added a bit to my experiment. You should easily be able to replicate it. Here are my steps:
1. Press the 'Delete ALL' button (above "DataGrid 2" and delete the data in both grids and the database,
2. Put whatever values you want in the Source field and the CipherKey field but stick with them for the whole experiment
3. Use the Inspector to change "DataGrid 2" so that it does NOT allow editing.
4. Press the "Encrypt" button 5 times to produce five rows of of data in DataGrid 1. Notice that the same source has produced 5 different encryptions.
5. Add the following code to the group "DataGrid 2"

Code: Select all

on mousedoubleUp
   put the dgHilitedIndex of me into tIndex
   put the dgDataOfIndex[tIndex]  of me into tArray
   put tArray["result"] into tResult
   put field "Cipher" into tCipher
   put field "CipherKey" into tCipherKey
   decrypt tResult using tCipher with tCipherKey
   put it into tUnscrambled
   answer tUnscrambled  
   pass mouseDoubleUp
end mousedoubleUp

6. Now press the button "Load From Database" (above dg2) to show what the database has recorded. Notice the dissimilarities between what you thought was being recorded in the database..
7. Now double=click any row of "DataGrid 2". I expect to decrypt was was previously encrypted and get back the source value. Why does that not happen? Is my programming bad or are my expectations wrong?

Larry

Larry

[bwmilby]

Larry,

I've just now had the opportunity to download your test stack. Here are my observations:

• Since you do not provide a salt, a random salt is included whenever you encrypt. This salt value is combined with the key when performing the encryption. This is why each encrypted value that you see is different. The salt isn't a secret, so including it with the encrypted data is how it is designed to be used (with a unique salt for each encrypt operation).
• The encrypted data is binary. You are storing it in a CHAR field. The data should first be encoded (i.e. base64encode).
• You decode data from the database as UTF-8. Since you didn't encode as UTF-8, this is why you are getting different data back. I added this before the database store and it seemed to work, but would be cautious about using it (I don't know enough about UTF-8 encoding to really say).

  Code: Select all

  put textEncode(tScrambled, "UTF-8") into tScrambled

If I change the "Encode" button to base64encode the encrypted data and do the same on the decode side, it works as it should as well.

Thanks,
Brian

Edited to add the below code that I used:

Code: Select all

on doScramble tType
  put empty into field "Unscrambled"
  put field "Source" into tSource
  put field "Cipher" into tCipher
  put field "CipherKey" into tCipherKey
  encrypt tSource using tCipher with tCipherKey
  put it into tScrambled
  if tType = "Encode" then
    put base64Encode(tScrambled) into tScrambled
  end if
  put tScrambled into field "Scrambled"
  ...

Code: Select all

on doUnScramble tType
  put field "Scrambled" into tScrambled
  put field "Scrambled" into tSource
  put field "Cipher" into tCipher
  put field "CipherKey" into tCipherKey  
  if tType = "Encode" then
    put base64Decode(tScrambled) into tSource
  end if
  decrypt tSource using tCipher with tCipherKey
  put it into tUnscrambled
  put tUnScrambled into field "UnScrambled"
end doUnScramble

[lohill]

Thanks Brian,

This looks like it is exactly what I needed. I'll see how it works in the real situation where I want to try it. I had to read your points 2 and 3 about five times before the distinction started to sink in.

Larry

[lohill]

I have been struggling with this for a long time and I have convinced myself that there is a bug somewhere. I just don't know whether it is in LiveCode or SQLite or my understanding of the concepts involved. It may well be in my programming so I have included a simple stack to demonstrate what I see.

First of all my assumption is that if I encrypt some text and store it in a SQLite database, I should be able to recover that encrypted data and decrypt it back to its original state as long as I have the proper encryption keys. The stack included below demonstrates that that is not always the case. Occasionally the encrypted data cannot be returned to its original state. In the SQLite database I have tried the datatype of blob as well as nvarchar. Here are the steps:

1. Unzip the stack "Encryption Test".
2. When you open this stack in the IDE a file called encTest.db will be created in your Documents folder.
3. You may put whatever you want in the field "Source" but leave the fields "Cipher" and "CipherKey" as they are.
4. Using that same source data, click the "Encrypt" button 10 or 12 times. This will record in the datagrid the source and the encryption in columns 2 and 3. You can observe that even though the source is the same the encryptions are very different. (This is a good thing.) As each line gets generated in the datagrid that same data is being added to the SQLite database encrust.db.
5. Now that you have multiple lines of the same source encrypted, press the button "Retrieve Encryptions". This will go to the database and, for each row, read out the encrypted data and decrypt it using the proper cipher and cipher key. That encrypted data from the database is put in column 4 of the datagrid and its decrypted value is put in column 5 of the datagrid.
6. An examination of the datagrid should show that sometimes the data retrieved is not able to be decrypted and column 5 is blank.
7. The button "Reset Data" will delete all the data in the database and the datagrid. Try this a few times with different sources and or cipher keys.

Is this a LiveCode bug? It makes encryption useless.

Larry

[bwmilby]

You need to use base64encode for the binary data instead of textEncode. The data isn't always valid UTF-16 which is why it doesn't always decode properly. If you change the enc database field to always use base64encode/base64decode then it will work as you need.

line 47:

Code: Select all

    put base64Encode(tEncrypted) into tReturn

lines 69-73:

Code: Select all

         if tName is "enc" then 
            put base64Decode(tColumnValue) into tData[tCount][tName]
         else
            put textDecode(tColumnValue, "UTF-8") into tData[tCount][tName]
         end if

line 93 (after the insert above):

Code: Select all

   put base64Encode(tEnc) into tEncrypted

[lohill]

Thanks Brian,

I have been able to incorporate your suggestions in my larger project as well as the test project. I am still trying to work this out in my mind. At the risk of demonstrating that I still don't understand, is this generalization accurate: If you want to save encrypted data in a database you need to convert it with base64encode first. When you want to bring it back out of the database you need to use base64Decode to get it back out before you decrypt it.

Regards,

Larry

[bwmilby]

If using the VARCHAR type, then your generalization is correct. Using base64encode/decode is a safe way to get binary data in/out of a VARCHAR field in the database. It isn't the only way to store the data though. You could use the BLOB type to avoid needing to convert, but I've not experimented with it to provide specifics.

[LCMark]

@lohill: I took a quick look at your example stack - there are three things you need to do to make this work correctly (I think, at least).

The first is to ensure you open the sqlite db in "binary" mode - pass "binary" as the third parameter when opening the db - this ensures that you can put in and pull out binary data in a way which makes it compatible (viewable) in third-party tools.

The second thing to do is to use "*b" as a prefix to the vars you use when using ExecuteSQL (e.g. "*btEncrypted") *if* the var is binary data rather than text. This inserts the value of the var as an SQLite blob literal, rather than a string literal.

The final thing to do is to only textEncode/Decode as utf-8 when you are putting in and pulling out strings. You shouldn't need to do that with binary data - if you use the *b prefix when inserting that data, and then pass the output of ColumnNamed for your binary columns through your decryption functions it should work fine.

[ N.B. You don't need to prefix the var name you pass to ColumnNamed with *b - SQLite knows whether a value in a column is binary or text, so you don't have to - the sqlite driver just cannot tell whether you want to treat the values in variables passed to ExecuteSQL as binary or text! ]

[bwmilby]

Here's an updated stack that uses BLOB.
I updated the file to include the "binary" opening of the database.