$connection->quote() converts JSON into String #5933

New Issue

admin · 2026-01-22T15:22:28+01:00

admin commented

2026-01-22 15:22:28 +01:00

Originally created by @yurtesen on GitHub (Mar 26, 2018).

Originally assigned to: @Ocramius on GitHub.

I am trying to use executeUpdate() on PostgreSQL 9.6 with Doctrine 2.6 with Symfony3 on a jsonb column.

The problem is that when I use $connection->quote() function, with Type::JSON it seems to convert whole JSON to a string. Documentation is not very clear on what it should be doing . I understood from documentation that it should result in valid JSON, no? is this a bug?

$connection = $this->_em->getConnection();
$connection->quote('{"name": "product"}', Type::JSON);

Results:
"{\"name\": \"product\"}"

Originally created by @yurtesen on GitHub (Mar 26, 2018). Originally assigned to: @Ocramius on GitHub. I am trying to use `executeUpdate()` on PostgreSQL 9.6 with Doctrine 2.6 with Symfony3 on a jsonb column. The problem is that when I use `$connection->quote()` function, with `Type::JSON` it seems to convert whole JSON to a string. [Documentation is not very clear on what it should be doing ](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#quote). I understood from documentation that it should result in valid JSON, no? is this a bug? ``` $connection = $this->_em->getConnection(); $connection->quote('{"name": "product"}', Type::JSON); ``` Results: `"{\"name\": \"product\"}"`

admin added the Invalid Question labels 2026-01-22 15:22:28 +01:00

admin closed this issue

2026-01-22 15:22:28 +01:00

admin commented

2026-01-22 15:22:29 +01:00

@Ocramius commented on GitHub (Mar 26, 2018):

Pass in ['name'=>'product']

On Mon, 26 Mar 2018, 18:42 yurtesen, notifications@github.com wrote:

I am trying to use executeUpdate() on PostgreSQL 9.6 with Doctrine 2.6
with Symfony3 on a jsonb column.

The problem is that when I use $connection->quote() function, with
Type::JSON it seems to convert whole JSON to a string. Documentation is
not very clear on what it should be doing
http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#quote.
I understood from documentation that it should result in valid JSON, no? is
this a bug?

$connection = $this->_em->getConnection();
$connection->quote('{"name": "product"}', Type::JSON);

Results:
"{"name": "product"}"

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/7159, or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakCq7MYcT1raTmEPycCa64d4mjVTMks5tiRp0gaJpZM4S7dsk
.

@Ocramius commented on GitHub (Mar 26, 2018): Pass in ['name'=>'product'] On Mon, 26 Mar 2018, 18:42 yurtesen, <notifications@github.com> wrote: > I am trying to use executeUpdate() on PostgreSQL 9.6 with Doctrine 2.6 > with Symfony3 on a jsonb column. > > The problem is that when I use $connection->quote() function, with > Type::JSON it seems to convert whole JSON to a string. Documentation is > not very clear on what it should be doing > <http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#quote>. > I understood from documentation that it should result in valid JSON, no? is > this a bug? > > $connection = $this->_em->getConnection(); > $connection->quote('{"name": "product"}', Type::JSON); > > Results: > "{\"name\": \"product\"}" > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <https://github.com/doctrine/doctrine2/issues/7159>, or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJakCq7MYcT1raTmEPycCa64d4mjVTMks5tiRp0gaJpZM4S7dsk> > . >

admin commented

2026-01-22 15:22:30 +01:00

@yurtesen commented on GitHub (Mar 26, 2018):

But that is not JSON? Isn't the type should represent the input type?
I tried this print $connection->quote("['name'=>'product']",Type::STRING); which prints '[''name''=>''product'']'

@yurtesen commented on GitHub (Mar 26, 2018): But that is not JSON? Isn't the type should represent the input type? I tried this `print $connection->quote("['name'=>'product']",Type::STRING);` which prints `'[''name''=>''product'']'`

admin commented

2026-01-22 15:22:30 +01:00

@Ocramius commented on GitHub (Mar 26, 2018):

Ah, I see you are using quoting: missed that.

You really want parameter binding.

The conversion of the value is done by DBAL, so if you pass in a PHP array you get a json array (or hashmap), if you pass in a string then a json string and so on.

@Ocramius commented on GitHub (Mar 26, 2018): Ah, I see you are using quoting: missed that. You really want parameter binding. The conversion of the value is done by DBAL, so if you pass in a PHP array you get a json array (or hashmap), if you pass in a string then a json string and so on.

admin commented

2026-01-22 15:22:30 +01:00

@yurtesen commented on GitHub (Mar 26, 2018):

I wanted to use parameter binding but I am using jsonb_set in PostgreSQL and trying to build the query ends up with an error Error: Expected known function, got 'jsonb_set'" at

If I use native query then doctrine/postgresql does not recognize all parameters. I have a query like this UPDATE cart c SET data = jsonb_set( coalesce(data,'{"product": {}}'), '{"product",":id"}','{"name":":name"}') WHERE cart.id = :cart'; it does not recognize :id and :name are parameters, only the :cart parameter is replaced in final query.

So as a last resort, I had to go totally low level. Now I figured out this works with json_decode()
$connection->quote(json_decode('{"name": "product"}'), Type::JSON);
Not sure if this makes sense at all or improve security (eg. against sql injection) at all in cases where name "product" is given online by users?

Also shouldn't the quote() function work with JSON as string?

@yurtesen commented on GitHub (Mar 26, 2018): I wanted to use parameter binding but I am using [jsonb_set](https://www.postgresql.org/docs/9.6/static/functions-json.html) in PostgreSQL and trying to build the query ends up with an error `Error: Expected known function, got 'jsonb_set'" at` If I use native query then doctrine/postgresql does not recognize all parameters. I have a query like this `UPDATE cart c SET data = jsonb_set( coalesce(data,'{"product": {}}'), '{"product",":id"}','{"name":":name"}') WHERE cart.id = :cart';` it does not recognize `:id` and `:name` are parameters, only the `:cart` parameter is replaced in final query. So as a last resort, I had to go totally low level. Now I figured out this works with `json_decode()` `$connection->quote(json_decode('{"name": "product"}'), Type::JSON);` Not sure if this makes sense at all or improve security (eg. against sql injection) at all in cases where name `"product"` is given online by users? Also shouldn't the `quote()` function work with JSON as string?

admin commented

2026-01-22 15:22:32 +01:00

@Ocramius commented on GitHub (Mar 27, 2018):

Quoting will do the type conversion. A string and a hashmap are very different things, so you need to pass in the correct data structure to be quoted as a value.

@Ocramius commented on GitHub (Mar 27, 2018): Quoting will do the type conversion. A string and a hashmap are very different things, so you need to pass in the correct data structure to be quoted as a value.

admin commented

2026-01-22 15:22:32 +01:00

@yurtesen commented on GitHub (Mar 27, 2018):

I didn't quote understand exactly what you mean. I was also not sure of what you meant earlier when you said "you are using quoting". You mean the quote() function?

I am giving in a standard json object. Isn't a json object technically a string and also consist of key/value pairs so a hash map?

Let me ask it in a more direct way. I feel like I am doing something wrong. Lets say I have the json object { "name":"John", "age":30, "car":null } and I am about to assign it to a jsonb field. What is the correct way to pass this to $connection->quote() function for quoting?

@yurtesen commented on GitHub (Mar 27, 2018): I didn't quote understand exactly what you mean. I was also not sure of what you meant earlier when you said "you are using quoting". You mean the `quote()` function? I am giving in a [standard json object](https://www.w3schools.com/js/js_json_objects.asp). Isn't a json object technically a string and also consist of key/value pairs so a hash map? Let me ask it in a more direct way. I feel like I am doing something wrong. Lets say I have the json object `{ "name":"John", "age":30, "car":null }` and I am about to assign it to a `jsonb` field. What is the correct way to pass this to `$connection->quote()` function for quoting?

admin commented

2026-01-22 15:22:32 +01:00

@Ocramius commented on GitHub (Mar 27, 2018):

A string is a string, a key/value map is a key/value map. Doctrine converts what you have in memory. If what you have in memory is a string, you will store a JSON string, not a JSON object.

@Ocramius commented on GitHub (Mar 27, 2018): A string is a string, a key/value map is a key/value map. Doctrine converts what you have in memory. If what you have in memory is a string, you will store a JSON string, not a JSON object.

admin commented

2026-01-22 15:22:33 +01:00

@yurtesen commented on GitHub (Mar 27, 2018):

Sorry, but that really did not make things clear at all :( also did not quite answer my question.

The definition of JSON. -> JavaScript Object Notation (JSON) is a lightweight, text-based, language-independent data interchange format <- notice text-based, if it is not text, it is not JSON.

Can you give an example to a JSON object, with PHP code, which is NOT a string? Keep in mind, it is not a JSON object if it is a PHP object or associative array. I am trying to understand what you mean, perhaps the most direct way is with a code example?

@yurtesen commented on GitHub (Mar 27, 2018): Sorry, but that really did not make things clear at all :( also did not quite answer my question. The [definition of JSON](https://tools.ietf.org/html/rfc7159). -> `JavaScript Object Notation (JSON) is a lightweight, text-based, language-independent data interchange format` <- notice **text-based**, if it is not text, it is not JSON. Can you give an example to a JSON object, with PHP code, which is NOT a string? Keep in mind, it is not a JSON object if it is a PHP object or associative array. I am trying to understand what you mean, perhaps the most direct way is with a code example?

admin commented

2026-01-22 15:22:34 +01:00

@Ocramius commented on GitHub (Mar 27, 2018):

Can you give an example to a JSON object, with PHP code, which is NOT a string?

Yes:

$jsonString = '{"foo":"bar"}'; // this is a string. It might look like json, but it is a string
$jsonObject = json_decode($jsonString); // this an actual object
var_dump(gettype($jsonString));
var_dump(gettype($jsonObject));

This is the difference here. If you pass around a string, PHP (and DBAL) will interpret it as a string. If you pass around an object (or anything compatible with json_serialize()) then you will get a JSON-object saved in your DB value.

Doctrine DBAL performs a json_encode() on its own.

@Ocramius commented on GitHub (Mar 27, 2018): > Can you give an example to a JSON object, with PHP code, which is NOT a string? Yes: ```php $jsonString = '{"foo":"bar"}'; // this is a string. It might look like json, but it is a string $jsonObject = json_decode($jsonString); // this an actual object var_dump(gettype($jsonString)); var_dump(gettype($jsonObject)); ``` This is the difference here. If you pass around a `string`, PHP (and DBAL) will interpret it as a `string`. If you pass around an object (or anything compatible with `json_serialize()`) then you will get a JSON-object saved in your DB value. Doctrine DBAL performs a `json_encode()` on its own.

admin commented

2026-01-22 15:22:34 +01:00

@yurtesen commented on GitHub (Mar 27, 2018):

@Ocramius your $jsonObject is a PHP object. json_decode() manual Takes a JSON encoded string and converts it into a PHP variable. also says Returns the value encoded in json in appropriate PHP type..

PHP converts JSON Object into PHP stdClass() object. So your $jsonObject variable has nothing to do with JSON. Yes, your code prints out object as type, but it is the PHP object type. Same result will be from:

$stdObject = new stdClass();
var_dump(gettype($stdObject));

The $stdObject is also NOT a JSON object.

When I pass the JSON encoded string to quote() I clearly tell it that it is JSON $connection->quote('{"name": "product"}', Type::JSON); yet, it screws it up. Actually, on the contrary, if I tell it is Type::STRING then it actually returns '{"product","name"}' which is correct. I would say this is a bug.

Also from Doctrine json type manual entry. If you know that the data to be stored always is in a valid UTF-8 encoded JSON format string, you should consider using this type. Well my data is a valid JSON string!?

Anyway, thanks for your time. But I won't dwell on this further...time constraints... :(

@yurtesen commented on GitHub (Mar 27, 2018): @Ocramius your `$jsonObject` is a PHP object. [json_decode() manual](http://php.net/manual/en/function.json-decode.php) `Takes a JSON encoded string and converts it into a PHP variable.` also says `Returns the value encoded in json in appropriate PHP type.`. PHP converts JSON Object into PHP stdClass() object. So your `$jsonObject` variable has nothing to do with JSON. Yes, your code prints out `object` as type, but it is the PHP object type. Same result will be from: ``` $stdObject = new stdClass(); var_dump(gettype($stdObject)); ``` The `$stdObject` is also NOT a JSON object. When I pass the `JSON encoded string` to `quote()` I clearly tell it that it is JSON `$connection->quote('{"name": "product"}', Type::JSON);` yet, it screws it up. Actually, on the contrary, if I tell it is `Type::STRING` then it actually returns `'{"product","name"}'` which is correct. I would say this is a bug. Also from [Doctrine json type manual entry](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#json). ` If you know that the data to be stored always is in a valid UTF-8 encoded JSON format string, you should consider using this type.` Well my data is a valid JSON string!? Anyway, thanks for your time. But I won't dwell on this further...time constraints... :(

admin commented

2026-01-22 15:22:34 +01:00

@Ocramius commented on GitHub (Mar 27, 2018):

@yurtesen I've already explained it in the best way I can. Strings are strings: objects are objects. A json string is still a string: trying to encode it as json leads to double-encoding.

@Ocramius commented on GitHub (Mar 27, 2018): @yurtesen I've already explained it in the best way I can. Strings are strings: objects are objects. A json string is still a string: trying to encode it as json leads to double-encoding.

admin referenced this issue

2026-01-22 16:16:01 +01:00

[PR #11477] Port d05e8e8285a052ad715cd6fb4d6d80890cf3c4d8 #13053

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5933