Обсуждение: checking SQL statement/subexpression validity

I need to check whether a SQL subexpression (to be used in WHERE
clause), e.g.:

  colname > 200

or an entire SELECT statement, e.g.:

  SELECT * FROM t1, t2 WHERE colname > 200

is syntactically valid. Is there a quick (and also safe) way to do this?
I'm thinking of doing "SELECT ... FROM ... WHERE <SQLexpr> LIMIT 0" for
#1, but I'm not sure if it's 100% safe; and I don't know what to do with
#2. AFAIK, in, say, Perl's DBI, $dbh->prepare() doesn't check SQL syntax
and $sth->execute() actually executes the statement.

--
dave

> 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.
--
Mike Nolan

Mike Nolan <nolan@gw.tssi.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