Here's a snippet of code I've been using that satisfies some, but not all, of
the problems that people have mentioned:
local l_null = {}
local function dbfs(s)
local x
if s==nil or s==l_null then return 'NULL' end
if type(s)=='string' then return "'"..string.gsub(s,"'","''").."'" end
if type(s)=='boolean' then return s and 1 or 0 end
return s
end
local function l_format(s,...)
local x={s}
local arg = {...}
for i=1,table.maxn(arg) do table.insert(x,dbfs(arg[i])) end
return string.format(unpack(x))
end
local function l_insert(tblname,data)
local l1,l2 = {},{}
for k,v in next,data do
l1[#l1+1] = k
l2[#l2+1] = dbfs(v)
end
return table.concat{
"insert into ",
tblname,
" (",
table.concat(l1,','),
") values (",
table.concat(l2,','),
");"
}
end
module'sqlf'
null=l_null
format = l_format
insert = l_insert
(end of snippet)
Now you can generate an insert statement by:
sqlf.insert("mytable",{name="O'Brien",age=35,married=false,job=sqlf.null})
as
insert into mytable (job,age,name,married) values (null,35,'O''Brien',0);
A simple extension provides for update statements.
Data seems reasonably separated from code up until the (inevitable) SQL
statement is generated. All data strings are properly escaped.
In the version I actually use, sqlf.format formats tables as SQL set literals:
('Joe','Jim','T''Shana'), which I left out for clarity here.
-----Original Message-----
From: kepler-project-boun...@lists.luaforge.net
[mailto:kepler-project-boun...@lists.luaforge.net] On Behalf Of Petite Abeille
Sent: Friday, December 11, 2009 2:04 PM
To: Kepler Project mailing list
Subject: Re: [Kepler-Project] Parameterized queries
On Dec 11, 2009, at 10:33 PM, Romulo wrote:
> It is not that simple. Whilst you can enforce the restriction of not
> being able to change the database schema, a malicious user would still
> be able to forge an attack seeking an otherwise inaccessible data.
> Consider:
>
> --*--
> local sql = 'update users set some_attribute = "%s" where username="%s"'
> local user_input = {}
> user_input.username = 'malicious_user'
> user_input.some_attribute =[[" || (select list(email) from users) || "]]
>
> db:execute( string.format( sql, user_input.some_attribute,
> user_input.username ) )
> --*--
>
> And there you go: full access to the system data.
Hmmm... I guess it's getting late for me, but... I'm not quite sure how trying
to address any of these issues on the client side is going to help you out.
(1) Use proper database level security (schemes, grants, etc)
(2) Shared accounts (aka application accounts) are a recipe for disaster
In general, use the database level mechanism which already address these
issues. Reimplementing pseudo access control on the client side is not going to
be of much help.
> Now imagine what you
> can do with the where clause (delete from ... where 1=1)
Well, if the authenticated database session doesn't have grants to
delete/update/insert/execute anything, then... the DDL generated by the client
is rather irrelevant, no?
For example, Exploits of a Mom, is self inflicted pain:
http://xkcd.com/327/
And yes, securing a database if more than simply "sanitizing database input".
While database centric development is not very glamourous those days, there is
a lot to learn from it:
http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-1.html
In any case, we are getting carried away from LuaSQL proper, so lets move on :)
_______________________________________________
Kepler-Project mailing list
Kepler-Project@lists.luaforge.net
http://lists.luaforge.net/cgi-bin/mailman/listinfo/kepler-project
http://www.keplerproject.org/
Technical data included in this e-mail may be Export-Controlled and subject to
the Arms Export Control Act (Title 22, U.S.C., sec 2751 et seq.) or the Export
Administration Act of 1979, as amended seq.
This information or element thereof, in any form, shall not be disclosed to a
foreign person (including foreign person employees), entity, or exported from
the United States without U.S. Government authority and the express written
authorization of DCX-CHOL Enterprises, Inc. This document may contain DCX-CHOL
Enterprises, Inc. Proprietary Information and is to be used only for the
purposes for which it has been supplied and is not to be duplicated or
disclosed
in whole or in part without written permission from a duly authorized
representative of DCX-CHOL Enterprises, Inc.
If you feel you have received this email in error please contact the DCX-CHOL
IT Team at 310-516-1692 ext 454.
_______________________________________________
Kepler-Project mailing list
Kepler-Project@lists.luaforge.net
http://lists.luaforge.net/cgi-bin/mailman/listinfo/kepler-project
http://www.keplerproject.org/
