3

I need a way to escape data for mysql statements in lua. I'm used to doing something like mysql_real_escape_string() in php but can't find an equivalent in lua using mysql (con:escape() worked when I was using sqlite3). I've read that prepared statements are a solution but it doesn't seem to work for me. What am I doing wrong?

require "luasql.mysql"
env = assert (luasql.mysql())
con = env:connect("db_name", "user", "pass", "localhost")
local stmt = con:prepare([[
    SELECT * FROM `user` 
    WHERE `login` = :a AND `pass` = :b LIMIT 1
]])
stmt.a = "some_user"
stmt.b = "some_pass"

This errors with "attempt to call method 'prepare' (a nil value)".

If I try to run a straight SELECT * execute on con it works fine, so the connection is being made, but this prepare statement does not work (it's not even recognizing prepare as a valid method, it seems).

two13
  • 345
  • 1
  • 3
  • 11
  • Did the connect succeed? Use `assert(env:connect...` to fail if the connect fails. – BMitch Jun 11 '11 at 21:25
  • @B, the error message means that there is no 'prepare' method, not that conn is nil. – lhf Jun 11 '11 at 22:09
  • are you sure there is a 'prepare' method? I couldn't find one in http://www.keplerproject.org/luasql/manual.html#connection_object – lhf Jun 11 '11 at 22:11
  • I've seen it used in a few places (like here: http://lists.luaforge.net/pipermail/kepler-project/2006-April/000086.html ) and described as a way to prevent injection using luasql. I wouldn't mind ignoring prepare if escape worked with mysql but it doesn't seem to be (did with sqlite3 though). Ultimately I just want to do whatever the best way is to escape data using lua and mysql, whatever that is. – two13 Jun 12 '11 at 00:59

1 Answers1

5

It looks like the prepare functionality was added to LuaSQL within the last year or two, so maybe you version is a bit older?

Also, try con:escape(yourQuery) to do the escaping, maybe that will be sufficient for your needs.

John Zwinck
  • 239,568
  • 38
  • 324
  • 436
  • I just recently started learning Lua (within the last few weeks) and started with lua for windows install - LuaForWindows_v5.1.4-40. Do you know if that version has prepare functionality in LuaSQL? The newest is -43. As for con:escape, when I do: username = con:escape(username) - it throws the same "attempt to call method 'escape' (a nil value)" as calling prepare. :escape() WAS working with sqlite3 though, just not with mysql (though as a note, prepare wasn't working with sqlite3 either). – two13 Jun 12 '11 at 00:56
  • This page: http://code.google.com/p/luaforwindows/ says LuaSQL 2.1.1 is what they package, and this page: http://www.keplerproject.org/luasql/history.html says 2.1.1 is from 2007. So the solution is probably to update your LuaSQL to 2.2 using LuaRocks (which is fortunately part of LuaForWindows). Hopefully that upgrade isn't painful (I've never used Lua on Windows myself). – John Zwinck Jun 12 '11 at 01:02
  • Well, I've spent all day trying to upgrade luasql using luarocks and still haven't succeeded. First I had to install a standalone version of mysql and point MYSQL_DIR to a mysql.h header file. Then I had to install visual studio 2010 and run luarocks commands from the vs command prompt. And here I am with another vague error "failed compiling object src/ls_mysql.obj" after it errors a ton in mysql.h file. I'm so used to fixing php issues within minutes of a google search but no luck with lua so far. However, this answer is probably correct (needs upgrading) so marking it as such. – two13 Jun 12 '11 at 23:51