Re: Protection from SQL injection

On Tue, 2008-04-29 at 16:01 -0400, Aidan Van Dyk wrote:

> Most of my published applications *are* simple, and I tend to
> consolidate as much of my "business logic" in the database as possible
> and a "known" set of queries shared by all the related apps, relying
> heavily on view, triggers, and functions, so the queries in my web-side
> and C-side applications really are very simple and straight forward.

I a company I worked, we got ( almost ? ) the same result by doing all
access using functions and REVOKE-ing frontend app users all privileges
on anything else.

So almost all sql issued by apps looks like
"SELECT * FROM some_func(p1, p2, ..., pn)"

This has a lot of nice properties, among others ability to do lots of
database code fixing on live 27/4 apps without frontends never noticing.

> I purposely choose to have "simple static queries" in my apps. So a
> mode which "rejects" queries with literals/constants in them would catch
> "bugs" in my code.

Hmm - maybe a mode where functions accept only parameters would be
needed for enforcing this on current server code.

Anyway, with pl/proxy partitioning/loadbalancing running on data-empty
servers, code injection would be quite hard even without params-only
mode.

> Those "bugs" really could be cosmetic, and still
> "valid SQL" queries, but one of them could be a valid one which could be
> an injection vector.

Could we also get a mode, where PREPARE would only be allowed for
queries of the form "SELECT * FROM func(?,?,?,?,?); :)

---------------------
Hannu