Re: SQL injection, php and queueing multiple statement

On Sun, 13 Apr 2008 16:02:35 +0800
Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:

> > I think this logic is already somewhere in the driver or the pg
> > engine. Whatever you write at the application level a) risk to be
> > a duplication of part of the parser b) risk to be less smart than
> > the parser itself and let slip something.

> ... in which case it sounds like you need to extend the Pg DB
> interface to do what you want. It might be worth hacking together a
> proof of concept and posting it to -hackers and the PHP interface
> maintainers, along with a rationale for its inclusion.

I wish I'd be so familiar with pg C code.
And it looks as if such a thing won't be that welcome.
Everybody seems happy with pg_prepare|execute and consider not using
them shameful enough not to be worth some form of "legacy support".

> > Why does it have to be "global", couldn't it be "by connection" or
> > "by call to pg_query"?

> Because you appear to be seeking something to protect against
> programmers who do not follow coding guidelines, and that should
> help even if code review processes fail to catch the problem. Were
> that not the case you'd be able to use some of the other
> suggestions made here. I quote:

Default 1 statement, switch to more than one have to be "voluntary"
and "conscious" and can be easily spotted with grep only.

> Also, as already noted there are ways to help you enforce the use
> of prepared statements. If you have a decent code review process in
> place then you probably have a `lint' script that's run as a first
> pass to spot possible problems in the code. You can extend that
> script, and also hook it into the revision control system so it
> emails you (or rejects the commit) if somebody tries to commit code
> that looks worrying, like non-prepared-query database interface
> calls. A simple annotation scheme should permit you to ignore the
> legit ones, with unauthorized addition of annotations punishable by
> cold coffee for a year.

These things are nice... and I do as much as I can to deal with
security from different angles but they require more effort than
switching a flag.

> There's probably an existing PHP lint script you can extend, so you
> don't have to do the boring bits. A quick search suggests so.

These will be further steps as soon as I'll have some free time.
That said php as a language doesn't make easy to deal with static
checking and I had (?) the feeling many tools are still in their
infancy (eg. automatic refactoring tools that will make easier to wrap
all the calls of pg_query). But this is definitively OT for the
Postgresql list.

> In any case, I agree with you that a "single statement only" flag
> would be nice in the DB interface, because as you say it's nice if
> all else fails and will block a many of the most flexible types of
> SQL injection attack. I just think that if it exists it needs to be
> opt-out, not opt-in, to be significantly effective as a defense
> against other programming errors.

thanks

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