0

I have json encoded data that can have the following format: {"forumla": "callTo(\"par1\", \"par2\")"} When I try to insert it into a postgresql table with a jsonb column, I get the following Error:

SQL error QSqlError(-1, "QPSQL: Unable to create query", "ERROR:  invalid input syntax for type json
LINE 1: ...15-12-11 15:20:17.350', 21590, '{ "forumla...
                                           ^
DETAIL:  Token "par1" is invalid.
CONTEXT:  JSON data, line 1: ..." : "callTo(\\"par1...

")

It seems like Qt somehow replaced the escaped quotes (\") with an escaped backslash and a quote. Inserting the string via the psql cli works. Can someone help me with this?

Thanks in advance.

Update:
I removed the binding and replace the double backslash with a single one. Now I could insert the value successfully.

QSqlField f(name, QVariant::String);
f.setValue(fields.value(name));
const QString value = db.driver()->formatValue(f).replace("\\\\", "\\");

The problem stems probably from Qt (at least pre 5.x) not knowing about json in databases. So it can only treat it as a string and sees the need to escape backslashes.

This is obviously not an ideal solution.

Update 2:
I found an acceptable solution. Qt (at least 4.7) assumes a backslash to be a special character in a string value. This is no longer the case (by default) since 9.1. See http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE
So I had to set the following so Postgresql again assumes \ to be an escaped backslash:

QSqlQuery query(db);
query.exec("SET standard_conforming_strings TO false");
Daniel82
  • 101
  • 7
  • Do you use prepared statements (like [this](http://stackoverflow.com/a/19045489/1499698))? It shouldn't be a problem with bound parameters. – pozs Dec 11 '15 at 15:35
  • I use bound variables, (specifically I use the QDjango library), this is probably the reason why the QString is changed. – Daniel82 Dec 11 '15 at 15:55

2 Answers2

1

It would appear that Qt is automatically escaping any backslashes \ you have in your string. Why - I have no idea.

You could try using single quotes ' instead of ". It could be that Qt only cares about escaping double quotes. So by writing:

"callTo('par1', 'par2')")

You can bypass the issue. (UPDATE: confirmed that this works by OP, but is obviously not the ideal solution as it merely bypasses the problem)

Also alternatively it's possible that Qt has an option you can use to disable this functionality, but I'm afraid I'm not familiar enough with it as a tool to be able to tell you if it exists.

Hopefully that helps - it's not the answer I'd like to give you but in the absence of any more knowledgeable Qt experts it might give you some ideas on what to try!

Ieuan Stanley
  • 1,248
  • 8
  • 20
  • I tried replacing \" with ", but now Qt seems to think its not necessary to escape the quotation marks. It escaped the \ with \\ and now it does nothing. – Daniel82 Dec 11 '15 at 16:17
  • 1
    hah. So it's the \ itself that it's escaping. How weird. what happens if you use ' on it's own (so `"callTo('par1', 'par2')"`) single quotes within double quotes (and vice versa) don't end a string started by the other quote type. – Ieuan Stanley Dec 11 '15 at 16:23
  • Single quotes work, thank you. I can keep this as an interims solutions. But I really would like to get to the bottom of this. – Daniel82 Dec 11 '15 at 16:46
  • I don't blame you! I'll update my answer to reflect what you've found. – Ieuan Stanley Dec 11 '15 at 16:54
0

I found an acceptable solution. Qt (at least 4.7) assumes a backslash to be a special character in a string value. This is no longer the case (by default) since 9.1. See http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE So I had to set the following so Postgresql again assumes \ to be an escaped backslash:

QSqlQuery query(db);
query.exec("SET standard_conforming_strings TO false");
Daniel82
  • 101
  • 7