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

Re: Protection from SQL injection

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Aidan Van Dyk" <aidan(at)highrise(dot)ca>
Cc: "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 18:19:51
Message-ID: 8763u04j7c.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"Aidan Van Dyk" <aidan(at)highrise(dot)ca> writes:

> That said, though *I* like the idea (and since I develop against
> PostgreSQL 1st and use params for my queries I would consider it a nice
> tool to "keep me honest"), I can easily see that the cost/benefit ratio
> on this could be quite low and make it not worth the code/support
> necessary.

Note that using parameters even for things which are actually constants is not
really very desirable. If you have a query like:

SELECT * FROM users WHERE userid = ? AND status = 'active'

a) It makes things a lot clearer to when you call Execute($userid) which
   values are actually the key user-provided data. In more complex queries it
   can be quite confusing to have lots of parameters especially if the query
   itself only makes sense if you know what values will be passed.

b) It allows the database to take advantage of statistics on "status" that
   might not otherwise be possible.

Parameters are definitely the way to go for dynamic user data but for
constants which are actually an integral part of the query and not parameters
you're passing different values for each time it's actually clearer to include
them directly in the query.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2008-04-29 18:39:09
Subject: Re: Protection from SQL injection
Previous:From: Tom LaneDate: 2008-04-29 18:12:23
Subject: Re: [COMMITTERS] pgsql: Remove typename from A_Const.

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