Re: Running untrusted sql safely?

On Sun, Feb 15, 2009 at 3:09 PM, Stuart McGraw <smcg2297(at)frii(dot)com> wrote:
> John R Pierce wrote:
>>
>> Stuart McGraw wrote:
>>>
>>> What is the best way to run an arbitrary query received from an untrusted
>>> source, safely?
>>> (I want a web page form with a textbox that
>>> a user can enter an arbitrary sql statement,
>>> then run it .....
>>
>> just keep http://xkcd.com/327/ in mind.
>
> Yes, exactly what I would like some advice on avoiding! :-)

Your first idea, to allow it to connect via a read only user is a good
start. Another thing you can do is explain the query, then see what
the cost is according to first line in the explain output that has it.
explain select * from a;
QUERY PLAN
------------------------------------------------------
Seq Scan on a (cost=0.00..29.40 rows=1940 width=12)

Grep out that first line, look for the number on the right of the ..
and if it's over some predetermined threshold then refuse to run it.

It's like herding cats. There's only so much you can do to prevent
someone who's running sql on your database from DOSing the server.