Re: design, plpgsql and sql injection in dynamically generated sql - Mailing list pgsql-general

From Ivan Sergio Borgonovo
Subject Re: design, plpgsql and sql injection in dynamically generated sql
Date
Msg-id 20090818121402.342f9377@dawn.webthatworks.it
Whole thread Raw
In response to Re: design, plpgsql and sql injection in dynamically generated sql  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: design, plpgsql and sql injection in dynamically generated sql  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-general
On Mon, 17 Aug 2009 12:48:21 +0200
Pavel Stehule <pavel.stehule@gmail.com> wrote:

> Hello
>
> I am not sure, if it's possible for you. PostgreSQL 8.4 has EXECUTE
> USING clause, it is 100% safe.

Sorry I don't get it.

How can I use USING safely when the substitution involves a table
name?

The examples I've seen just involve column values.

Where is the corresponding fine manual page?

Still I don't get how USING could make safer plpgsql functions...
well... I'm going to check some prejudices I have on pg functions
firts...

I thought that if you passed eg. text to
create or replace function typetest(a int) returns text as
$$
begin
raise notice 'is this an int? %', a;
-- don't do anything else with a

and calling

select * from typetest('tonno');

was going to raise an error anyway.

So somehow I find the example here
http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html
not really helpful in understanding what's going on.

Maybe an example with text comparing a version using quote_literal
and one using USING could be clearer...

or am I completely missing the point?

far from an optimal solution I've built a "client side" array of
permitted table, key to dynamically build the query on the client
side.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it


pgsql-general by date:

Previous
From: Jasen Betts
Date:
Subject: Re: psql command line editor
Next
From: Terry Lee Tucker
Date:
Subject: Configuration Question