Robert Weiss wrote:
Here's a snippet of code I've been using that satisfies some, but not all, of
the problems that people have mentioned:
...
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.
All it does is automate string cats - which is the fundamental problem.
The building of the SQL query isn't inevitable - it's actually something
you want to avoid.. ;-)
As an aside, there may be other reasons beyond this that you want to use
parameters to queries instead of string building. Some databases
actually use the idea of commonality between queries for performance.
Oracle, for example, keeps a cache of queries so it might not have to
optimize every query that comes through over and over again. If you use
proper parameterization, it can find like queries in its cache and save
considerable effort. If you string cat everything, you defeat that
cache and any benefits it can have.
-P
-----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/
_______________________________________________
Kepler-Project mailing list
Kepler-Project@lists.luaforge.net
http://lists.luaforge.net/cgi-bin/mailman/listinfo/kepler-project
http://www.keplerproject.org/
