1

There is little documentation on prepared statements in luaSQL. So i tried to put together some code to use prepared statements in LuaSQL. Unfortunately it's not working.
(I'm using a mysql database)

luasql = require "luasql.mysql"
env = assert (luasql.mysql())
con = assert (env:connect("database","user","password","localhost"))
name = "insert_sql_injection_code_here"
query= "INSERT INTO `table`(`text`) VALUES ('"..name.."')"
cur = assert (con:execute(query))
-- As you can see, query is vulnerable to SQLinjection. Fix: use prepared statements:
smtp = assert(con:prepare("insert into settings (text) values(:p_name)"))
con:bind_names({p_name=name})
cur = assert (con:execute())

Gives the output:

lua: test.lua:8: attempt to call method 'prepare' (a nil value)

Has anyone a working example of prepared statements in Lua with LuaSQL?

  • LuaSQL in itself doesn't have support for `prepare`d statements. It has been one of suggested projects for GSoC program: http://www.lua.inf.puc-rio.br/gsoc/ideas2015.html#luasql – hjpotter92 Sep 19 '15 at 17:04
  • @hjpotter92, thanks for clearing that out. From a security perspective this is really a must so I am surprised it's not implemented yet. In the meantime:do you have suggestions to sanitise variables before sending them in a sql query? –  Sep 19 '15 at 17:20
  • If it is being executed in linux environment, there's an undocumented function: `con:escape()` – hjpotter92 Sep 19 '15 at 17:21
  • 1
    @hjpotter92, thanks! I have also an idea; Would a mysql c library solve this? I can be wrong, but that came in my mind. –  Sep 19 '15 at 17:31
  • 1
    You'd still need to bind functions to lua library. – hjpotter92 Sep 19 '15 at 17:32
  • You can try other library lua-dbi or lua-odbc they support prepared query. – moteus Sep 19 '15 at 18:44
  • @moteus, Thanks for the idea! How to use prepared statements in lua-dbi? Because there is little information available as far I can see –  Sep 20 '15 at 09:20
  • I use only odbc. But you can checkout examples here https://code.google.com/p/luadbi/wiki/DBDDriverStatement. `local insert = assert(dbh:prepare('insert into tab(str,num,bool) values(?,?,?)')) insert:execute('test', 1234, true)` – moteus Sep 20 '15 at 09:40
  • Thanks! But how do you connect to the database? –  Sep 20 '15 at 09:42

0 Answers0