Knex: making values safe in raw queries where bound parameters aren't accepted

Question

I am familiar with parameter binding for raw queries, however there seems to be at least one situation where this is not allowed. When creating a user (on PostgreSQL, driver pg), I get an error if I try to use a bind for the password.

(assuming conn being an established and verified Knex connection)

conn.raw('CREATE ROLE test_user WITH LOGIN ENCRYPTED PASSWORD :pwd',
   {pwd: 'testpass'}
).then(r => { console.log(r); });

throws the following:

Unhandled rejection error: syntax error at or near "$1"
at Connection.parseE (/mnt/c/proj/insights/admin-api/node_modules/pg/lib/connection.js:553:11)
at Connection.parseMessage (/mnt/c/proj/insights/admin-api/node_modules/pg/lib/connection.js:378:19)
at Socket.<anonymous> (/mnt/c/proj/insights/admin-api/node_modules/pg/lib/connection.js:119:22)
at Socket.emit (events.js:182:13)
at Socket.EventEmitter.emit (domain.js:460:23)
at addChunk (_stream_readable.js:283:12)
at readableAddChunk (_stream_readable.js:264:11)
at Socket.Readable.push (_stream_readable.js:219:10)
at TCP.onread (net.js:639:20)
```

The same error is thrown if I use ? instead of a named bind.

Whereas if I include the password, it succeeds:

conn.raw('CREATE ROLE test_user2 WITH LOGIN ENCRYPTED PASSWORD \'testpass\'')
    .then(r => { console.log(r); });

Is there any way to achieve this query cleanly via Knex (such as a safe escape function)?

Answer 1

There are many places in queries where postgresql protocol does not support using parameter bindings. In those cases you need to write values directly to sent SQL query string.

Your case seems to be one of those.

If you like to use knex's potentially unsafe automatic escaping, you can write:

conn.raw(`CREATE ROLE test_user2 WITH LOGIN ENCRYPTED PASSWORD ${conn.raw('?', [pwd])}`)
    .then(r => { console.log(r); });

or in future version of knex (maybe in 0.16):

conn.raw(`CREATE ROLE test_user2 WITH LOGIN ENCRYPTED PASSWORD ${conn.val(pwd)}`)
    .then(r => { console.log(r); });

But you should validate pwd carefully first, because this is potentially dangerous and knex's escaping functionality might not be perfect.