Re: [Q] storing JSON, problem with 'escapes'

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'\\'.

( I am seeing what postgres is getting because I enabled logging of
all the statements to stderr)
--------

So I am not sure what to do. This has to be a common issue
(unless nobody before me stored JSON in postgresql :-) ).

The PDO does not provide facilities to 'not to escape' or escape
differently.

Ideally I do not want the second backslash. I do not undestand who
adds it and why. The first backslash is by json_encode -- which is
correct.

thanks in advance for any help.

--
V S P
toreason(at)fastmail(dot)fm

--
http://www.fastmail.fm - Access all of your messages and folders
wherever you are

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\") ;}"

Is magic_quotes_gpc (or magic_quotes_runtime) on for the php server?
Check with a phpinfo() page.

If so, you'll need to "undo" that, see http://www.php.net/stripslashes
(inc. the recursive function stripslashes_deep).

--
Postgresql & php tutorials
http://www.designmagick.com/

hi,
magic_quotes_gpc is on,
however, the data (the strings) do not come from HTTP protocol
or web pages, they come from STDIN (using popen) when invoking
another program.

So, at the end of the process I have

"if( js_iop_lt(a,b) ){ VLADIKVLADIKVLADIKVLADIK(\"b2122\") ;}"

and then I prepare an SQL statement and use bindParams
to bind the string like the above to the Insert statement
and I know 100% that the string has single backslash (which is
how I want it to be stored -- because I want to stored as valid
JSON to be read by non-php programs).

But I am guessing something adds another backslash slash.
after I bind the string to the insert (and I have no way to undo
that because there is mechanism to get into the internal PDO
structures to massage the values of the bound variables).

Now, when I check what's stored in the database -- the single slash
is stored -- which is correct (I think... I do not have the code to
read the data back in yet).

So it is almost like PosgreSQL ignores the second backslash, but
warns about it?...

> > 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\") ;}"
>
> Is magic_quotes_gpc (or magic_quotes_runtime) on for the php server?
> Check with a phpinfo() page.
>
> If so, you'll need to "undo" that, see http://www.php.net/stripslashes
> (inc. the recursive function stripslashes_deep).
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
--
V S P
toreason(at)fastmail(dot)fm

--
http://www.fastmail.fm - Faster than the air-speed velocity of an
unladen european swallow

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.
------------------------------------------------------------------------

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)

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(at)morphoss(dot)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(at)fastmail(dot)fm

--
http://www.fastmail.fm - Email service worth paying for. Try it for free

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(at)morphoss(dot)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(at)fastmail(dot)fm
>
------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
You too can wear a nose mitten.
------------------------------------------------------------------------