lua-users home
lua-l archive

[Date Prev][Date Next][Thread Prev][Thread Next] [Date Index] [Thread Index]


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