SQLLite Insert Question...

Solar2D General Questions/Discussion
#1

Hi Guys,

             I have a text field in my app that the user can enter any text they want into including quotes and apostrophes.

Does anyone know if there is a way I can sanitize the input before inserting it into my database?

I thought maybe some sort of prepared statement but I haven’t been able to get it working.

I have tried many variations of the below statement including with and without quotes in the braces before and after …note…

coinID is an Int and note is String.

db:prepare[[INSERT INTO user_notes VALUES (]]…coinID…[[,]]…note…[[);]]

db:exec()

I tried this as well but I don’t quite know the syntax or even if it is supported:

db.prepare([[INSERT INTO user_notes VALUES (?), (?);]], coinID, notes)

I tried this too:

local s = db:prepare(“INSERT INTO user_notes (coinID, note) VALUES (:f, :s);”)
        s:bind(":f", row.id)
        s:bind(":s", row.notes)
        
        db:exec(s)

but it states bad argument #1 to ‘bind’ (number expected, got string).

I’m trying to execute this statement within a for row in nrows statement if that makes a difference.

I also tried doing gSubs on the notes string to add back spaces before " and ’ but again, couldn’t seem to get it working.

I want the user to add as many quotes or apostrophes as they want without it having an effect on the SQL.

If someone has any advice I’d love to heard it :).

Kind Regards,

Krivvenz.

#2

Hi,

Check if maybe one of these work for you:

local function sqlescape(str) if not str or type(str)~="string" then return str end str=str:gsub('["\'\\%z]',{['"']='\\"',['\0']='\\0',["'"]="\\'",['\\']='\\\\',}) return str end local function sqlite3escape(str) -- and also a little formatting if not str or type(str)~="string" then return str end str=str:gsub(" "," ") str=str:gsub("´","'") str=str:gsub("`","'") str=str:gsub("'","''") return str end
#3

Hi Anaqim,

                 Legendary!!! I can confirm the below is working fine!

local insertNote = [[
            INSERT INTO user_notes VALUES (]]
            …coin_id…[[, ‘]]
            …sqlite3escape(note)…[[’);
        ]]
       

db:exec(insertNote)

It only works with single quotes in the insert statement.

I couldn’t get the other function to work but this is perfect.!

Thank you, so much!

#4

The first one works great with MySQL and the second one is a hack i made to solve an issue i had with using SQLite3 on local device. From what I see it’s working but I haven’t “stress tested” it  :slight_smile:

To clarify, the first one is not created by me but one i received from develephant i believe. That syntax just makes my head hurt. The second also removes double spaces, convert ticks to a single type and as a final step removes all instances of double ticks, which is what helps clean up before sql insert.

#5

if you want to remove "illegal’ characters:

local function sqlEscape2(str) if not str or type(str)~="string" then return str end str=str:gsub("[^%w%-]","") return str end
#6

Hi,

Check if maybe one of these work for you:

local function sqlescape(str) if not str or type(str)~="string" then return str end str=str:gsub('["\'\\%z]',{['"']='\\"',['\0']='\\0',["'"]="\\'",['\\']='\\\\',}) return str end local function sqlite3escape(str) -- and also a little formatting if not str or type(str)~="string" then return str end str=str:gsub(" "," ") str=str:gsub("´","'") str=str:gsub("`","'") str=str:gsub("'","''") return str end
#7

Hi Anaqim,

                 Legendary!!! I can confirm the below is working fine!

local insertNote = [[
            INSERT INTO user_notes VALUES (]]
            …coin_id…[[, ‘]]
            …sqlite3escape(note)…[[’);
        ]]
       

db:exec(insertNote)

It only works with single quotes in the insert statement.

I couldn’t get the other function to work but this is perfect.!

Thank you, so much!

#8

The first one works great with MySQL and the second one is a hack i made to solve an issue i had with using SQLite3 on local device. From what I see it’s working but I haven’t “stress tested” it  :slight_smile:

To clarify, the first one is not created by me but one i received from develephant i believe. That syntax just makes my head hurt. The second also removes double spaces, convert ticks to a single type and as a final step removes all instances of double ticks, which is what helps clean up before sql insert.

#9

if you want to remove "illegal’ characters:

local function sqlEscape2(str) if not str or type(str)~="string" then return str end str=str:gsub("[^%w%-]","") return str end