Open 2731381914 opened 5 years ago
Hi
There is no prepare method. No one ever implemented it, although it is in my plans :-)
Regards, Tomás
2018-08-08 6:11 GMT-03:00 2731381914 notifications@github.com:
I looked through the documentation and found that no conn:prepare(select * from table where id=?) was provided. Why? What should I do about SQL injection.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/keplerproject/luasql/issues/94, or mute the thread https://github.com/notifications/unsubscribe-auth/AAIA7ezFVqNNcrfNHtmdeYicvTpYjad5ks5uOqs1gaJpZM4Vzjs3 .
I've implemented prepared statements and parametrised queries for ODBC and Firebird. Had a look at MySQL but have no experience with that API.
Hi
Would you mind send me your code? I could add it to LuaSQL...
Regards, Tomás
2018-08-09 13:30 GMT-03:00 blumf notifications@github.com:
I've implemented prepared statements and parametrised queries for ODBC and Firebird. Had a look at MySQL but have no experience with that API.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/keplerproject/luasql/issues/94#issuecomment-411818473, or mute the thread https://github.com/notifications/unsubscribe-auth/AAIA7Wg00FuGxkWO_1wv7CoGfvjNuvy0ks5uPGPDgaJpZM4Vzjs3 .
It's a bit awkward, the changes are in the middle of a bunch of other features and refactoring. I'll have a try at making a clean patch against the LuaSQL master branch and send a pull-req in a while.
You basically end up being able to do the following:
sql = "INSERT INTO FOO(COL1,COL2) VALUES(?,?)"
stmt = conn:prepare(sql)
stmt:execute{123, "abc"}
-- also, directly
conn:execute(sql, {321,"xyz"})
Okay, check out the #95 PR (I wouldn't recommend actually pulling it as-is) Contains a patch to the ODBC driver supporting parametrized queries.
Bit of a mess to look at the diff as so many bits and pieces have been altered, but the overall concept is pretty straight forward.
@blumf, @tomasguisasola: I've added a simpler interface in the PR #99, even though it doesn't yet support reuse of prepared statements, it could in the future. Example:
local sql = "SELECT * FROM passwords WHERE user=? and pass=?"
local cur = conn:execute(sql, user, pass)
...
The issue with passing the arguments in a table is that you could have {1, 2, nil} never knowing that it had 3 and not 2 values, which would trigger the invalid paramater count error on some databases. With arguments it's different since you could unpack an exact number of parameters by using: conn:execute(sql, table.unpack(myvalues, 1, 3))
; or even conn:execute(sql, 1, 2, nil)
; and still be able to obtain the exact parameter count by using select('#', ...)
in lua (if you ever need a proxy) or lua_gettop
in C.
I am curious about this too. I am looking at lsqlite3 as well, but LuaSQL seems to have a more familiar interface to me. I would like to see prepared statements though :)
Perhaps something like the following interface would be possible?
-- Bind with named parameters
local statement = conn:prepare('SELECT * FROM mytable WHERE id = :id')
local cur = statement:execute({id = 1})
-- Bind with values
local statement = conn:prepare('SELECT * FROM mytable WHERE name = ?')
local cur = statement:execute({"example"})
There are a few more parameter types that sqlite allows, but these two cover the common cases that I've seen.
I've reviewed #100, which seems to address ?
parameters, but is there any plan to support named parameters as well? Thanks!
was prepare statements implemented? i dont know where to get the dll where luasql.mysql has parameter support.
I looked through the documentation and found that no conn:prepare(select * from table where id=?) was provided. Why? What should I do about SQL injection.