Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id BC33B633539 for ; Mon, 27 Apr 2009 17:20:03 -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 95989-09 for ; Mon, 27 Apr 2009 17:19:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.morphoss.com (hoppy.mcmillan.net.nz [202.78.240.82]) by mail.postgresql.org (Postfix) with ESMTP id 9E23F632E1A for ; Mon, 27 Apr 2009 17:19:58 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.morphoss.com (Postfix) with ESMTP id E8D9823153; Tue, 28 Apr 2009 08:19:54 +1200 (NZST) X-Virus-Scanned: Debian amavisd-new at mail.morphoss.com Received: from mail.morphoss.com ([127.0.0.1]) by localhost (hoppy.mcmillan.net.nz [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 1QyW2XoRLqHo; Tue, 28 Apr 2009 08:19:49 +1200 (NZST) Received: from [192.168.55.193] (218-101-8-114.xtreme.net.nz [218.101.8.114]) (Authenticated sender: andrew@mail.morphoss.com) by mail.morphoss.com (Postfix) with ESMTPSA id B8CDA2314E; Tue, 28 Apr 2009 08:19:48 +1200 (NZST) Subject: Re: Newbie Question-Quoting SQL From: Andrew McMillan To: Thomas Hart Cc: pgsql-php@postgresql.org In-Reply-To: (sfid-20090428_075716_343019_206B2240) References: (sfid-20090428_075716_343019_206B2240) Content-Type: text/plain Date: Tue, 28 Apr 2009 08:19:47 +1200 Message-Id: <1240863587.12115.105.camel@happy.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.26.1.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200904/15 X-Sequence-Number: 4020 On Mon, 2009-04-27 at 15:56 -0400, Thomas Hart wrote: > I have some old forms that I used for MySQL a few years ago, and I'm > trying to use the with postgres, but I keep on getting a message that > the query failed. > > The connection string seems to be okay: > > $link = pg_connect("host=localhost port=5432 dbname=toms_stuff > user=postgres password=********"); > > The query string, however, seems to be messed up. > > $result = pg_query ($link, "Select * from Author"); > if (!$result) { > echo "Query failed.\n"; > exit; > } > > I assume the fault is in how the Select statement is quoted, but I > can't see what I'm doing wrong. > > Any suggestions will be appreciated. Also can anyone give me a general > idea of the rules for quoting postgreSQL in PHP? They don't apply so much to PHP, as to PostgreSQL which follows the SQL standard for quoting. Identifiers should be quoted with " and character strings should be quoted with '. And then within PHP if you want to have " in a string it should be escaped like: "SELECT * FROM \"Author\"" or 'SELECT * FROM "Author"' or, in the case of using a keyword as a column name: 'select "DATE" from mytable' You can also escape characters in the PostgreSQL string, using \, but of course the \ itself may need to be escaped in PHP... In this string the ' is backslashed to PostgreSQL: "INSERT INTO \"Author\" (name) VALUES( E'O\\'Connor' )" Note that the PostgreSQL string needs to be preceded by an "E" if it has escapes in it (in recent versions of PostgreSQL, at least). All of this should be in the PostgreSQL manual: http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Cheers, Andrew McMillan. ------------------------------------------------------------------------ andrew (AT) morphoss (DOT) com +64(272)DEBIAN Building more free and open source software for New Zealanders ------------------------------------------------------------------------