Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 35FC162E9C1 for ; Mon, 5 Oct 2009 19:01:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 64978-03 for ; Mon, 5 Oct 2009 22:01:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.potentialtech.com (internet.potentialtech.com [66.167.251.6]) by mail.postgresql.org (Postfix) with ESMTP id 8DA27634280 for ; Mon, 5 Oct 2009 19:01:42 -0300 (ADT) Received: from localhost (pr40.pitbpa0.pub.collaborativefusion.com [206.210.89.202]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.potentialtech.com (Postfix) with ESMTPSA id A9BB3EBC0A; Mon, 5 Oct 2009 18:01:40 -0400 (EDT) Date: Mon, 5 Oct 2009 18:01:39 -0400 From: Bill Moran To: Marco Dieckhoff Cc: pgsql-php@postgresql.org Subject: Re: checking select query syntax and semantics via php without executing Message-Id: <20091005180139.25cc9511.wmoran@potentialtech.com> In-Reply-To: <4ACA6668.2000701@gmx.de> References: <4ACA6668.2000701@gmx.de> Organization: Bill Moran X-Mailer: Sylpheed 2.7.1 (GTK+ 2.16.5; i386-portbld-freebsd7.2) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.552 tagged_above=-10 required=5 tests=AWL=0.047, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200910/3 X-Sequence-Number: 4082 In response to Marco Dieckhoff : > > 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/