Proper string-escapes for Postgres

[indeyets]

Currently, knex tries to use backslashes to escape various characters in string literals for Postgres. The problem is, that Postgres doesn't interpret slashes in string literals by-default since version 9.1. It is possible to enable interpretation by setting standard_conforming_strings option to off, but this is not a recommended way.

Strictly speaking, usage of regular string-literals is just not a predictable option anymore.

The proper solution is: use non-standard E'literal'. They are defined with a strict set of rules and should work reliably for any modern Postgres version.

Input: Hello \"World\"
Probably wrong output: 'Hello \\"World\\"' (current knex)
Probably right output: 'Hello \"World\"'
Definitely right output: E'Hello \\"World\\"'

related to #1546, #886, #869, #828, #658

[elhigu]

Thanks @indeyets for opening this discussion. I'm not 100% sure if I got the examples right. I mean that how javascript is interpreting string literals has also something to add to this discussion.

So I'll just open up your example a bit to understand it better... Input Hello \"World\" must be written in javascript like 'Hello \\"World\\"' or otherwise already javascript string literal parsing removes the single backslash.

Anyways knex is actually not doing the string escaping in most of cases since just positional bindings are passed to node-postgres driver as javascript string which is responsible of escaping them correctly:

> knex('dodo').where('name', 'hell\\"o there').toSQL()
{ method: 'select',
  options: {},
  bindings: [ 'hell\\"o there' ], <-- javascript string containing `hell\o there` is passed to driver (this is good) 
  sql: 'select * from "dodo" where "name" = ?' }

But it is true that knex also has its own escaping function, which should work correctly. Which currently indeed replaces \ with \

> knex('dodo').where('name', 'hell\\"o there').toString()
'select * from "dodo" where "name" = \'hell\\\\"o there\''

Which really seem to result invalid string in database (too many backslashes...):

mikaelle=# Hello \\"World\\"';
     ?column?      
-------------------
 Hello \\"World\\"
(1 row)

So... we seem to need own string escaping function for postgres or change some other part of string literal interpolation to use E'' literals.

I think I agree that probably using E'' is safer choice for this @tgriesser @rhys-vdw @wubzz any comments?

ps. also how ? in raw sql string is added should be defined better... currently only postgres dialect has a bit hacky support for interpreting \\? as ? and not as positional binding.

[tgriesser]

Closing in favor of #1661