pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Moran <wmoran@potentialtech.com>
To: Marco Dieckhoff <dieck@gmx.de>
Cc: pgsql-php@postgresql.org
Subject: Re: checking select query syntax and semantics via php without executing
Date: Mon, 5 Oct 2009 18:01:39 -0400
Message-ID: <20091005180139.25cc9511.wmoran@potentialtech.com> (raw)
In-Reply-To: <4ACA6668.2000701@gmx.de>
References: <4ACA6668.2000701@gmx.de>

In response to Marco Dieckhoff <dieck@gmx.de>:
> 
> Hi there!
> 
> Is it possible in PHP to give a sql (select) query to Postgres via pg_* 
> so that it is NOT executed but merely checked for syntax including 
> correct, existing field and table names, and data types?
> 
> 
> I'm working on a system where users may assemble their own query in a 
> construction kit, and want that to be checked, so that they don't e.g. 
> compare UUID "<" datetime or something like this.
> 
> 
> If I try to execute the generated query, bad queries are instantly found 
> by pg_query.
> 
> 
> But good ones are fully executed, which can take a long time depending 
> on joins and aggregations.
> 
> It seems that pg_prepare doesn't return errors on bad queries, 
> pg_last_error() is empty.
> 
> The asynchronous pg_send_query (and pg_cancel_query) seem not to return 
> any errors, either.
> 
> I tried with three queries:
> 
> "SELECT * FROM tableok"
> "SELECT nonexistingfield FROM nonexistingtable"
> "SELECT complete bad / sql query FROM nonsense"
> 
> 
> Any ideas?

Off the top of my head, you could prefix the query with EXPLAIN, which
will cause it to be planned (thus checking syntax) and return an
execution plan, but not actually run the query.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/



view thread (5+ messages)  latest in thread

Message-ID: <20091005180139.25cc9511.wmoran@potentialtech.com>
Permalink:  ../20091005180139.25cc9511.wmoran@potentialtech.com/
Also on:    postgresql.org/message-id/20091005180139.25cc9511.wmoran@potentialtech.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: wmoran@potentialtech.com, dieck@gmx.de
  Subject: Re: checking select query syntax and semantics via php without executing
  In-Reply-To: <20091005180139.25cc9511.wmoran@potentialtech.com>
  References: <4ACA6668.2000701@gmx.de> <20091005180139.25cc9511.wmoran@potentialtech.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox