Skip site navigation (1) Skip section navigation (2)

Re: Protection from SQL injection

From: Hannu Krosing <hannu(at)krosing(dot)net>
To: Aidan Van Dyk <aidan(at)highrise(dot)ca>
Cc: Gregory Stark <stark(at)enterprisedb(dot)com>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tom Dunstan <pgsql(at)tomd(dot)cc>,Thomas Mueller <thomas(dot)tom(dot)mueller(at)gmail(dot)com>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Protection from SQL injection
Date: 2008-04-29 20:21:10
Message-ID: 1209500470.6636.11.camel@huvostro (view raw or flat)
Thread:
Lists: pgsql-hackers
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





In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2008-04-29 20:33:01
Subject: Re: Protection from SQL injection
Previous:From: Thomas MuellerDate: 2008-04-29 20:18:48
Subject: Re: Protection from SQL injection

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group