luasql.mysql Why is there no prepare?

[2731381914]

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.

[tomasguisasola]

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 <#94>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAIA7ezFVqNNcrfNHtmdeYicvTpYjad5ks5uOqs1gaJpZM4Vzjs3> .

[blumf]

I've implemented prepared statements and parametrised queries for ODBC and Firebird.
Had a look at MySQL but have no experience with that API.

[tomasguisasola]

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 <#94 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAIA7Wg00FuGxkWO_1wv7CoGfvjNuvy0ks5uPGPDgaJpZM4Vzjs3> .

[blumf]

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"})

[blumf]

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.

[fcr--]

@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.

[andyvanee]

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!

lsqlite3 docs
lsqlite3 usage example

[Leos-Khai]

was prepare statements implemented? i dont know where to get the dll where luasql.mysql has parameter support.