Putting encrypted data into a SQLite3 db

Solar2D General Questions/Discussion
#1

I’m trying to put encrypted data into a sqlite3 database and I’m getting the dreaded 1 error:

“SQLITE_ERROR 1 /* SQL error or missing database */”

The data type on the field is undefined, which according to the documentation makes it BLOB.

The database writes fine if I don’t encrypt the data.

I’m using openSSL with aes to encrypt the data.

Any idea why it’s failing to write to the database?

Things I have tried so far:

I looked up field limits in sqlite and the maximum data length is :1,000,000,000

I’m guessing my string length is in the hundreds, so that doesn’t seem to be the problem.

​I tried putting the encrypted data in brackets and then encoding it with json again to simplify the character set, it didn’t fix the problem.

I’m just looking for some ideas why it might not write. In testing, I have some lines that are not encrypted and some that are encrypted and the non encrypted lines write just fine.

Any suggestions are appreciated.

#2

I did a string.len check on my strings and they are averaging 20,000 characters. But that is still well within 1,000,000,000. I only mention it because I’m wondering if the data length documentation I found doesn’t apply to sqlite3 as implemented by Corona. That is the problem with that error, it just says, “do not want” and gives you nothin’.

#3

Ok, update:

I used a really short test string, about 50 characters and tried to encrypt that and insert it.

It works if it’s just alphanumeric text that is encrypted but if for example I surround the text in [] and then encrypt it, it can’t be put in the database.

My best guess at this point is that the encrypted string is not being escaped properly. Even when I wrap the new string in brackets and run json on it again. I had thought json escaped characters for me, which is why I was using it on strings.

Any advice on how to escape an encrypted json table for insertion into a sqlite db?

#4

Can you post some of your code so I can see what’s wrong?

Based off what you said, this is what I would suggest:

local encryptedData = json.encode(data) local stmt= db:prepare[[UPDATE tableName SET data = ? WHERE id = ?]]; stmt:bind\_values(encryptedData, id); stmt:step();
#5

data[2] is the trouble maker that gets rejected and it is a lua table.

This is where I define the database table:

[lua]local tablesetup = [[CREATE TABLE IF NOT EXISTS]] … tableName … [[(id INTEGER PRIMARY KEY autoincrement, position INTEGER, type, name UNIQUE, def, subtype, folder);]]

scene.db:exec( tablesetup )[/lua]

“def” in the function below has two possible data types, sometimes it is an int and sometimes it is a table. When it is a table, I want to encrypt it. That is why I am checking to see how long def is before encrypting it.

[lua]P.addModEntry = function(dataType, name, data, key)

local position = data[1]

local def = json.encode(data[2])

if string.len(def) > 10 then

def = c:encrypt(def, “test”)

def = json.encode({def})

end

local subtype = data[3]

if not subtype then subtype = “NULL” else subtype = “’” … subtype … “’” end

local folder = data[4]

if not folder then folder = “NULL” else folder = “’” … folder … “’” end

local q = [[INSERT INTO]] … key … [[VALUES (NULL,]] … position … [[, ‘]] … dataType … [[’, ‘]] … json.encode({name}) … [[’, ‘]] … def … [[’,]] … subtype … [[,]] … folder … [[);]]

print("q: ", q)

print(scene.db:exec(q))

end[/lua]

Like I said earlier, it works as long as I don’t try to encrypt the data.

#6

Ok, I’m still testing but for the moment your solution appears to solve the problem. I will continue to test and when I am confident that all is well, I will check your solution as solved. Thank you!

#7

I did a string.len check on my strings and they are averaging 20,000 characters. But that is still well within 1,000,000,000. I only mention it because I’m wondering if the data length documentation I found doesn’t apply to sqlite3 as implemented by Corona. That is the problem with that error, it just says, “do not want” and gives you nothin’.

#8

Ok, update:

I used a really short test string, about 50 characters and tried to encrypt that and insert it.

It works if it’s just alphanumeric text that is encrypted but if for example I surround the text in [] and then encrypt it, it can’t be put in the database.

My best guess at this point is that the encrypted string is not being escaped properly. Even when I wrap the new string in brackets and run json on it again. I had thought json escaped characters for me, which is why I was using it on strings.

Any advice on how to escape an encrypted json table for insertion into a sqlite db?

#9

Can you post some of your code so I can see what’s wrong?

Based off what you said, this is what I would suggest:

local encryptedData = json.encode(data) local stmt= db:prepare[[UPDATE tableName SET data = ? WHERE id = ?]]; stmt:bind\_values(encryptedData, id); stmt:step();
#10

data[2] is the trouble maker that gets rejected and it is a lua table.

This is where I define the database table:

[lua]local tablesetup = [[CREATE TABLE IF NOT EXISTS]] … tableName … [[(id INTEGER PRIMARY KEY autoincrement, position INTEGER, type, name UNIQUE, def, subtype, folder);]]

scene.db:exec( tablesetup )[/lua]

“def” in the function below has two possible data types, sometimes it is an int and sometimes it is a table. When it is a table, I want to encrypt it. That is why I am checking to see how long def is before encrypting it.

[lua]P.addModEntry = function(dataType, name, data, key)

local position = data[1]

local def = json.encode(data[2])

if string.len(def) > 10 then

def = c:encrypt(def, “test”)

def = json.encode({def})

end

local subtype = data[3]

if not subtype then subtype = “NULL” else subtype = “’” … subtype … “’” end

local folder = data[4]

if not folder then folder = “NULL” else folder = “’” … folder … “’” end

local q = [[INSERT INTO]] … key … [[VALUES (NULL,]] … position … [[, ‘]] … dataType … [[’, ‘]] … json.encode({name}) … [[’, ‘]] … def … [[’,]] … subtype … [[,]] … folder … [[);]]

print("q: ", q)

print(scene.db:exec(q))

end[/lua]

Like I said earlier, it works as long as I don’t try to encrypt the data.

#11

Ok, I’m still testing but for the moment your solution appears to solve the problem. I will continue to test and when I am confident that all is well, I will check your solution as solved. Thank you!