Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 85CF364FD69 for ; Fri, 21 Nov 2008 06:28:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17423-06 for ; Fri, 21 Nov 2008 06:28:55 -0400 (AST) 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 984AE64FD1A for ; Fri, 21 Nov 2008 06:28:55 -0400 (AST) Received: from [IPv6:2404:130:b055:0:215:afff:fe48:6c48] (unknown [IPv6:2404:130:b055:0:215:afff:fe48:6c48]) by mail.morphoss.com (Postfix) with ESMTPS id CC60C2C328; Fri, 21 Nov 2008 23:28:50 +1300 (NZDT) Subject: Re: [Q] storing JSON, problem with 'escapes' From: Andrew McMillan To: V S P Cc: pgsql-php@postgresql.org In-Reply-To: <1227250475.15555.1285991741@webmail.messagingengine.com> (sfid-20081121_195501_162654_AB030B34) References: <1227250475.15555.1285991741@webmail.messagingengine.com> (sfid-20081121_195501_162654_AB030B34) Content-Type: text/plain Date: Fri, 21 Nov 2008 23:28:46 +1300 Message-Id: <1227263326.16206.10.camel@happy.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.22.3.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: 200811/21 X-Sequence-Number: 3932 On Fri, 2008-11-21 at 01:54 -0500, V S P wrote: > Hi, > I am using PHP's json_encode function on > an array of strings > that gives me back a JSON encoded string. > > Some of the elements in the string arrays have > double quotes. So PHP's json_encode correctly > escapes them (according to JSON specifications) > with \. > > For example here is a an array element > > "if( js_iop_lt(a,b) ){ VLADIKVLADIKVLADIKVLADIK("b2122") ;}" > > would get encoded in JSON as > > "if( js_iop_lt(a,b) ){ VLADIKVLADIKVLADIKVLADIK(\"b2122\") ;}" > > And that's correct. > Now, the problem is that with PDO (or may be postgresql itself) > I get > > > "if( js_iop_lt(a,b) ){ VLADIKVLADIKVLADIKVLADIK(\\"b2122\\") ;}" > > > and I get then the POSTGRESQL warning > > WARNING: nonstandard use of \\ in a string literal at character 240 > HINT: Use the escape string syntax for backslashes, e.g., E'\\'. When you construct your SQL, if you are going to construct it as a string, like: INSERT INTO blah ( json_column ) VALUES ( 'VLADIKVLADIKVLADIKVLADIK(\\"b2122\\")' ) You need to instead insert as: INSERT INTO blah ( json_column ) VALUES ( E'VLADIKVLADIKVLADIKVLADIK(\\"b2122\\")' ) The "E" in front of the string is a special PostgreSQL thing which explains that the string is encoded with \ escaping. See: http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html particularly the box labelled 'caution' and the paragraph above it. Regards, Andrew McMillan. ------------------------------------------------------------------------ andrew (AT) morphoss (DOT) com +64(272)DEBIAN You have a truly strong individuality. ------------------------------------------------------------------------