Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 6CD569FB2FA for ; Mon, 5 Feb 2007 07:22:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 15854-02-2 for ; Mon, 5 Feb 2007 07:22:53 -0400 (AST) X-Greylist: delayed 00:20:58.95633 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id BE9CE9FB2F1 for ; Mon, 5 Feb 2007 07:22:53 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from vsxkg005.ocn.ad.jp (vsxkg005.ocn.ad.jp [211.129.17.144]) by svr4.postgresql.org (Postfix) with ESMTP id 1B1985AF9C0 for ; Mon, 5 Feb 2007 11:01:47 +0000 (GMT) Received: from sgc002.ocn.ne.jp (ov-orv-hkg002.ocn.ad.jp [211.129.17.155]) by vsxkg005.ocn.ad.jp (Postfix) with SMTP id 18EB0482 for ; Mon, 5 Feb 2007 20:01:41 +0900 (JST) Received: from [192.168.21.64] (dsl-51-161.dsl.netsource.ie [213.79.51.161]) by sgc002.ocn.ne.jp (Postfix) with ESMTP id EDF2C1B1C for ; Mon, 5 Feb 2007 20:01:39 +0900 (JST) From: Vincent de Phily Organization: BlueMetrix To: pgsql-php@postgresql.org Subject: Re: pg_[un]escape_bytea, pgsql 8.2.1, php 5.1.6, Linux Date: Mon, 5 Feb 2007 11:01:40 +0000 User-Agent: KMail/1.9.6 References: <302670f20702011827x28d9a689m388b2c8a703f31af@mail.gmail.com> <302670f20702022009x417a210fm5e0fa36d860a225@mail.gmail.com> In-Reply-To: <302670f20702022009x417a210fm5e0fa36d860a225@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200702051101.40315.vdephily@bluemetrix.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/7 X-Sequence-Number: 3690 On Saturday 03 February 2007, Gary Chambers wrote: > > you need for a non-parameterized query, like "INSERT INTO mytable (bd) > > VALUES ('$data')" where bd is a bytea column, and $data went through > > pg_escape_bytea(). > > Understood. I do not like for several reasons that method of > inserting data. It exposes me to SQL injection attacks, it's very > inefficient (in Oracle, anyway -- perhaps you can correct me where > Postgres is concerned), it seems uncharacteristic of a database with > the qualities of Postgres, I can't have all my queries in a single > source file, and I can't take advantage of the ease with which I can > handle binary data with a bytea field. This would be a problem related to php, not postgres. I'm handling binary data in parameterized and COPY queries just fine with c++. > > To me, this means that you should probably do non-parameterized queries > > instead, with pg_query() and pg_escape_bytea(), with your bytea data. > > Would there be any advantage to simply using a text field and base64 > encoding and decoding the binary data? I really don't want to use > non-parameterized queries. base64 would solve your binary problem, but it is costly (disk space and cpu). I think you can instead use prepared statements via SQL directly (as php probably does in the end) : // initialisation pg_query('PREPARE mystatement (bytea) AS INSERT INTO mytable (bd) VALUES ($1);'); // insert loop pg_query("EXECUTE mystatement (' . pg_escape_bytea($data) . "');"); Annoying to have to do all this yourself, but it should work (and it *is* a parameterized query). BTW, if you're doing bulk inserts, consider pg_copy_from() instead. -- Vincent de Phily