Crypto, strings, and SQLite

General
1

(1) If I do something like:

dim hash as string = Crypto.PBKDF2(salt, pw, 500, 64, Crypto.HashAlgorithms.SHA-512)

is the string just acting like a bucket of bytes, or is it getting encoded as UTF-8 (thus modifying the bytes)?

(2) If I subsequently save the string in a TEXT column of a SQLite database, is it getting modified by being encoded to UTF-8?

(3) Will either of these issues mean that comparing the string retrieved from the database to the generated hash will fail to show equality?

2

  1. no - PBKDF2 returns a memory block = “bucket o’ bytes”

  2. no - not unless you encode it

  3. without knowing how you retrieve it that one is hard to say but if you insert it as “plain bucket o’ bytes” and retrieve it without defining and encoding then its a column that is a bucket of bytes and it should compare fine

Be aware that depending on how you insert it (hand crated sql vs prepared statement) you may need to base 64 encode it

3

Thanks very much for your reply, Norman! With regard to (3), I am always retrieving the data using a prepared statement, and then getting the stringvalue from the recordset. Depending on whether I am modifying existing data or inserting a new record, I am either using “recordset.Edit”, setting the stringvalue, and then using “recordset.Update” or I am creating a DataRecord, filling its values using “DataRecord.Column” and then doing a “Database.InsertRecord”.

With regard to (2), I got worried because the Xojo “SQLiteDatabase” documentation says “SQLite converts all text in TEXT columns to UTF-8 encoding. If you want to preserve the original encoding, use a BLOB column type instead.” But it was unclear under what circumstances this conversion happens.

4

Any guesses on whether I will get into trouble given the specific notes in the previous message?

5

SQLite is more or less “typeless” - columns have affinities
But , if you’re concerned, then dont use a TEXT column for a BINARY object like this key

6

I appreciate your help very much, Norman.

In the meantime, I realized it would be pretty easy to test this, so I wrote a quick app that generated a string array of 256 strings containing 16 random bytes, inserted them in a SQLite database using DatabaseRecords, and then retrieved them using a RecordSet and compared the retrieved values with the original array values. There were no differences. I then generated a new array, changed each value in the database using a Recordset, and after changing them, retrieved them all using a Recordset, and again compared with the updated array. Again, there were no differences.

The bottom line is that if a SQLite database ever actually imposes UTF-8 encoding on TEXT Fields, it does not appear to do so when one uses only the Xojo DatabaseRecord and RecordSet commands to set and retrieve this TEXT data. I am reporting this just in case anyone else has this question.