From: Andrew McMillan <andrew@morphoss.com>
To: V S P <toreason@fastmail.fm>
Cc: pgsql-php@postgresql.org
Subject: Re: [Q] storing JSON, problem with 'escapes'
Date: Sat, 22 Nov 2008 11:44:02 +1300
Message-ID: <1227307442.16206.17.camel@happy.mcmillan.net.nz> (raw)
In-Reply-To: <1227302331.14831.1286107469@webmail.messagingengine.com>
References: <1227250475.15555.1285991741@webmail.messagingengine.com>
<1227263326.16206.10.camel@happy.mcmillan.net.nz>
<1227302331.14831.1286107469@webmail.messagingengine.com>
On Fri, 2008-11-21 at 16:18 -0500, V S P wrote:
> Hi Andrew
>
> that's the thing: when I construct my SQL nowhere do I have
> double-backslashes -- I only have one backslash (generated by
> PHP json_encode to escape the doublequote)
The extra backslashes were there because you had the PHP magic
backslashes option turned on. In my experience that option will do the
wrong thing at some point and almost always ends up being the cause of
strange inserts with additional backslashes in them. I turn it off
everywhere I see it.
Cheers,
Andrew.
> So I never have
> > INSERT INTO blah ( json_column )
> > VALUES ( 'VLADIKVLADIKVLADIKVLADIK(\\"b2122\\")' )
>
> I do see it however when logging SQL statements from within PG. So my
> thought now is that it is PDO that's doing it
> (PDO documentation states that it is doing its own escaping
> for all the drivers)
>
>
> However, I followed your advice and
> set standard_conforming_strings to 'On'
>
> Now, without modifying a single line of my PHP code
> a) the warnings are gone
> b) Postgresql shows only single backslash (which is how I am sending
> it).
>
> So now, I am more confused, as I do not know what was putting
> the second backslash -- may be it was postgresql?
>
> But it works... I think and the behaviour is now how I am expecting it:
> the JSON strings are stored exactly as they are coming out
> of PHP's json_encode (with single backslash in front of a doublequote)
>
>
> thank you
>
>
>
>
>
>
>
>
> On Fri, 21 Nov 2008 23:28:46 +1300, "Andrew McMillan"
> <andrew@morphoss.com> said:
> > 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.
> >
> --
> V S P
> toreason@fastmail.fm
>
------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
You too can wear a nose mitten.
------------------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: andrew@morphoss.com, toreason@fastmail.fm
Subject: Re: [Q] storing JSON, problem with 'escapes'
In-Reply-To: <1227307442.16206.17.camel@happy.mcmillan.net.nz>
References: <1227250475.15555.1285991741@webmail.messagingengine.com> <1227263326.16206.10.camel@happy.mcmillan.net.nz> <1227302331.14831.1286107469@webmail.messagingengine.com> <1227307442.16206.17.camel@happy.mcmillan.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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