Lua string.format escaping

Question

I want to escape for example single quotation marks in lua:

My script to get some data of a mysql table using sqlite so far:

local sqlCmd = string.format([[INSERT INTO temp_storage
SET     gID = '%s'
]], gID)

the problem is the gID can also contain single quotation marks ' but then the sql statement contains errors.

How to prevent this? I'm not really looking for a way to search and replace - more for a builtin function which replaces so that the sql is still valid.

This is called query parameterization and you would look to whatever SQL client you are using, E.g. http://stackoverflow.com/questions/1224806/how-to-quote-values-for-luasql — Alex K., Feb 29 '16 at 16:43

score 2 · Accepted Answer · answered Mar 01 '16 at 00:43

We are missing some information about how do you execute queries on the sqlite3 db, which library do you use?

secondly, this is not safe to concatenate 'strings' which will be executed on the db, this can cause sql_injection. the safe way to do this its to bind the parameters, that 'tells' the db to treat the input as param only, contrary to previous way.

from the libraries that i know for this purpose, LuaSQLite3 will do the job (represented as "lsqlite3" in the code).

the basic api for your usage is:

local sqlite3 = require("lsqlite3")
local db = sqlite3.open_memory()

gID = 6

local insert_stmt = assert( db:prepare("INSERT INTO temp_storage SET gID = ?") )
insert_stmt:bind_values(gID)
insert_stmt:step()
insert_stmt:reset()

I've modified one of the official examples ("statement.lua").

Lua string.format escaping

1 Answers1