Re: checking SQL statement/subexpression validity

Mike Nolan <nolan(at)gw(dot)tssi(dot)com> writes:
>> I need to check whether a SQL subexpression (to be used in WHERE
>> clause), e.g.:

> I've never tested it from Perl, but could you use 'explain select....'
> to see if it parses? It won't actually execute it if it does.

Consider input along the line of

"SELECT true; DELETE FROM critical_table WHERE true"

The EXPLAIN nullifies the first part and then the second part
destroys your table.

I think that if you allow random possibly-hostile input to be sent to
your SQL engine then you are going to get burnt :-(

The V3 extended-query protocol allows only one SQL command per message
--- so using that would prevent the more obvious possibilities for SQL
command injection. But I'd still not have a lot of faith in it. The
appropriately paranoid way to look at this is to allow through only the
stuff you are sure is OK, not to try to filter out the stuff you are
sure isn't OK.

regards, tom lane