[Date Prev][Date Next][Thread Prev][Thread Next]
[Date Index]
[Thread Index]
- Subject: Re: string dollar literals (for luamacro or tokenf)
- From: Sean Conner <sean@...>
- Date: Mon, 23 Jan 2012 18:06:11 -0500
It was thus said that the Great Petite Abeille once stated:
>
> On Jan 23, 2012, at 4:50 AM, Jay Carlson wrote:
>
> > Somebody else can write the SQL prepared statement parser ("SELECT *
> > FROM $foo" -> {"SELECT * FROM ?", {foo}} etc)
>
> Sadly, prepared SQL statement are not meant to substitute object names
> (e.g. table names), but rather just bind variables. To add insult to
> injury, bind variable placeholders vary from database to database. Oh,
> well...
>
> So perhaps:
>
> select * from foo where bar = $baz
>
> Into:
>
> select * from foo where bar = ?
>
> Which could simply be done as string.format if one doesn't have support
> for proper prepared statements (e.g. luasql):
>
> ( 'select * from foo where bar = %s' ):format( escape( baz ) )
>
> Usage example:
>
> local aDB = DB( 'sqlite3://localhost/test.db' )
>
> for aRow in aDB( 'select * from foo where bar = %s', 'baz' ) do
> print( aRow.bar )
> end
For SQL, I played around with the following syntax:
conn = db.connect {
type = "mysql',
name = 'wpdb',
user = 'wp',
pass = '1234',
host = 'localhost',
prefix = "wp_"
}
for row in conn:query([[
SELECT *
FROM {{posts}}
WHERE
post_name='%{ptype}%'
AND post_author=%{author}%
]],
{
ptype = 'about',
author = 1
})
magic_routine_to_dump_rable(row)
end
conn:query() is responsible for the interpolation. Items in {{}} will be
sanitized and have the prefix prepended (popular in MySQL setups), and items
in %{}% will be pulled from the supplied table and sanitized according to
Lua type (strings will be passed through mysql_escape() in this example).
It's straightforward and can be done in straight Lua, in this case. I
don't currently have an opinion one way or the other about a native Lua
string interpolation implementation.
-spc