Avoiding SQL injection in Dynamic Queries (in plpgsql)

Allan Kamau wrote:

When writing dynamic commands (those having "EXECUTE 'some SQL
query';), is there a way to prevent interpretation of input parameters
as pieces of SQL commands?

EXECUTE ... USING

--
Craig Ringer

On Wed, Mar 17, 2010 at 11:41 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

Allan Kamau wrote:

When writing dynamic commands (those having "EXECUTE 'some SQL
query';), is there a way to prevent interpretation of input parameters
as pieces of SQL commands?

EXECUTE ... USING

--
Craig Ringer

Thanks Craig, EXECUTE .. USING is what I had overlooked all this time.

2010/3/17 Allan Kamau <kamauallan@gmail.com>:

When writing dynamic commands (those having "EXECUTE 'some SQL
query';), is there a way to prevent interpretation of input parameters
as pieces of SQL commands? Does quote_literal() function implicitly
protect against this unwanted behaviour.

quote_literal, quote_identif are enough, but USING clause is better
and faster. Sometimes you have to use a combination:

execute 'select foo from ' || tabname::regclass || ' WHERE a = $1' USING value;

Pavel