Re: text column constraint, newbie question

On Mon, Mar 23, 2009 at 03:30:09AM -0600, Scott Marlowe wrote:
> On Mon, Mar 23, 2009 at 2:33 AM, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:
> > On Mon, 23 Mar 2009 01:07:18 -0600 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> >> Are you saying pg_quer_params is MORE effective than
> >> pg_escape_string at deflecting SQL injection attacks?
> >
> > I didn't follow the thread from the beginning but I'd say yes.
> > It should avoid queueing multiple statements and it is a more
> > "general" method that let you pass parameters in one shot in spite
> > of building the string a bit at a time for every parameter you
> > insert (string, float, integer...).
> >
> > Of course if you correctly escape/cast/whatever everything injecting
> > 2 statements shouldn't be possible... but if you don't you give more
> > freedom to the attacker.
>
> So, what are the performance implications? Do both methods get
> planned / perform the same on the db side?

Isn't the main point that it's just easier to get things right if you
use something that ends up calling PGexecParams under the hood rather
than doing your own string interpolation?

The frequency of SQL injection attacks[1,2,3,4] of people who really
should know better suggests that we're (i.e. developers en masse) not
very reliable at doing things properly and hence APIs that default to
safety are "a good thing". It's always easy as a developer to say
"oops, didn't think about that" when you're debugging, but if that oops
has just resulted in the compromise of details of a hundred thousand
credit-cards then it becomes a somewhat more serious issue.

Of course there are reasons for doing things differently, it's just that
those should be special cases (i.e. performance hacks) and not the norm.

Admittedly, using something like PGexecParams is a more awkward; but
there are efforts to get decent string interpolation libraries going
that "just work". For example, the caja project has developed something
they call "Secure String Interpolation"[5] which looks very neat and
tidy. It would be cool if things like this appeared in other languages.

--
Sam http://samason.me.uk/

[1] http://www.theregister.co.uk/2009/03/09/telegraph_hack_attack/
[2] http://www.theregister.co.uk/2009/02/27/lottery_website_security_probed/
[3] http://www.theregister.co.uk/2009/02/16/bitdefender_website_breach/
[4] http://www.theregister.co.uk/2009/02/13/f_secure_hack_attack/
[5] http://google-caja.googlecode.com/svn/changes/mikesamuel/string-interpolation-29-Jan-2008/trunk/src/js/com/google/caja/interp/index.html