pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
checking select query syntax and semantics via php without executing
5+ messages / 4 participants
[nested] [flat]

* checking select query syntax and semantics via php without executing
@ 2009-10-05 21:34 Marco Dieckhoff <dieck@gmx.de>
  2009-10-05 21:50 ` Re: checking select query syntax and semantics via php without executing Raymond O'Donnell <rod@iol.ie>
  2009-10-05 22:01 ` Re: checking select query syntax and semantics via php without executing Bill Moran <wmoran@potentialtech.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Marco Dieckhoff @ 2009-10-05 21:34 UTC (permalink / raw)
  To: pgsql-php


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?



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: checking select query syntax and semantics via php without executing
  2009-10-05 21:34 checking select query syntax and semantics via php without executing Marco Dieckhoff <dieck@gmx.de>
@ 2009-10-05 21:50 ` Raymond O'Donnell <rod@iol.ie>
  1 sibling, 0 replies; 5+ messages in thread

From: Raymond O'Donnell @ 2009-10-05 21:50 UTC (permalink / raw)
  To: Marco Dieckhoff <dieck@gmx.de>; +Cc: pgsql-php

On 05/10/2009 22:34, Marco Dieckhoff wrote:

> 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?

How about prepending "explain" to the query? Just a wild notion.

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: checking select query syntax and semantics via php without executing
  2009-10-05 21:34 checking select query syntax and semantics via php without executing Marco Dieckhoff <dieck@gmx.de>
@ 2009-10-05 22:01 ` Bill Moran <wmoran@potentialtech.com>
  2009-10-05 22:03   ` Re: checking select query syntax and semantics via php without executing Marco Dieckhoff <dieck@gmx.de>
  1 sibling, 1 reply; 5+ messages in thread

From: Bill Moran @ 2009-10-05 22:01 UTC (permalink / raw)
  To: Marco Dieckhoff <dieck@gmx.de>; +Cc: pgsql-php

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/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: checking select query syntax and semantics via php without executing
  2009-10-05 21:34 checking select query syntax and semantics via php without executing Marco Dieckhoff <dieck@gmx.de>
  2009-10-05 22:01 ` Re: checking select query syntax and semantics via php without executing Bill Moran <wmoran@potentialtech.com>
@ 2009-10-05 22:03   ` Marco Dieckhoff <dieck@gmx.de>
  2009-10-05 22:06     ` Re: checking select query syntax and semantics via php without executing Christophe Pettus <xof@thebuild.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Marco Dieckhoff @ 2009-10-05 22:03 UTC (permalink / raw)
  To: pgsql-php

Bill Moran schrieb:
> 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?

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

Works as a charm, thanks to both of you.

Must be one of the quickest "case solved, thread closed" I have ever seen :)





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: checking select query syntax and semantics via php without executing
  2009-10-05 21:34 checking select query syntax and semantics via php without executing Marco Dieckhoff <dieck@gmx.de>
  2009-10-05 22:01 ` Re: checking select query syntax and semantics via php without executing Bill Moran <wmoran@potentialtech.com>
  2009-10-05 22:03   ` Re: checking select query syntax and semantics via php without executing Marco Dieckhoff <dieck@gmx.de>
@ 2009-10-05 22:06     ` Christophe Pettus <xof@thebuild.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Christophe Pettus @ 2009-10-05 22:06 UTC (permalink / raw)
  To: pgsql-php


On Oct 5, 2009, at 3:03 PM, Marco Dieckhoff wrote:
> Must be one of the quickest "case solved, thread closed" I have ever  
> seen :)

And you can present the user with the query plan, so they can tell if  
they are about to do something horrible to the database. :)

--
-- Christophe Pettus
    xof@thebuild.com




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2009-10-05 22:06 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-05 21:34 checking select query syntax and semantics via php without executing Marco Dieckhoff <dieck@gmx.de>
2009-10-05 21:50 ` Raymond O'Donnell <rod@iol.ie>
2009-10-05 22:01 ` Bill Moran <wmoran@potentialtech.com>
2009-10-05 22:03   ` Marco Dieckhoff <dieck@gmx.de>
2009-10-05 22:06     ` Christophe Pettus <xof@thebuild.com>

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