Dynamic SQL in Lua

[Marc Balmer <marc@xxxxxxx>]

The PostgreSQL Lua binding found on https://github.com/mbalmer/luapgsql
has been extended to make it a bit easier to create SQL commands
dynamically and still be able to use execParams().

Imagine a table with user information that contains usernames,
hostnames, locations (e.g. building a user works in) and more data.  In
a web application, a HTML form field could be set to a location name or
'all', indicating that we want information for all locations.

The simple approach would then be to create the SQL on the fly and pass
it to the db:exec() function:

sql = {
	string.format('select * from userinfo where name = '%s' and host = %s',
name, host }
}

if location ~= 'all' then
	sql[#sql + 1] = string.format(' and location = '%s', location)
end

sql = table.concat(sql, '\n')
db:exec(sql)

Constructing SQL this way is bad thing (SQL injection...), so
db:execParams() is what should be used.  As it is now possible to pass
tables as parameter values to the db:execParams() function, this can now
be done in a halway sane form by using a table for the parameters and
creating the placeholders ('$1', '$2' etc.) based on the current size of
the parameter array:

p = { 'mbalmer', 'localhost' } -- parameter array

sql = { 'select * from userinfo where name = $1 and host = $2' }

if location ~= 'all' then
	p[#p + 1] = location
	sql[#sql + 1] = string.format(' and location = $%d', #p)
end

sql = table.concat(sql, '\n')
db:execParams(sql, p)


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general