Is there some string escaping or preparing?

[unikoid]

Hello. I'm using node-postgres. And I want to insert some strings into some table.
If I'm using single query, I can do like this and everything is working ok:

client.query('INSERT INTO sometbl VALUES ($1, $2)', ['foo', 'bar']);

But since I am using transactions and trying to do like this:

client.query('BEGIN; INSERT INTO sometbl VALUES ($1, $2); UPDATE sometable SET somevalue = $3; END;', ['foo', 'bar', 'foobar']);

I am getting an error with code 42601: 'cannot insert multiple commands into a prepared statement'.

Yes, I know that I can use simple string concatenation or some realizations of sprintf() to insert these parameters into the query string, but, I think, it isn't safe to do it without any preparation like escaping.
Is there some convinient way to do such preparation before inserting string values into the query string?

For example, I have found PQescapeLiteral function in the libpq, maybe there is some interface to this or similar function exists?
If no, can it be implemented?
//Sorry if the question is silly, I'm newbie in postgresql and node.

[brianc]

There is no built-in string escaping in node-postgres. The pure javascript version cannot rely on PQescapeLiteral being present and since the pure javsacript & native libpq bindings need to share the same API it hasn't been added.

You can use a transaction with parameterized sql by issuing each sql statement as their own command as follows:

//assume `client` is a connected instance of a postgres client
client.query('begin');
client.query('INSERT INTO somtbl VALUES($1, $2);', ['foo', 'bar']);
client.query('UPDATE sometable SET somevalue = $1', ['foobar'], function(err, result) {
  if(err) {
    //if there was an error postgres has already & automatically rolled back changes from the INSERT command
    //so execute any application error handling here
  }
  else {
    client.query('COMMIT');  //I guess 'END' works as well, but COMMIT is what's documented by Postgres
  }
});

Any node-postgres client instance will ensure each query is executed in order. For more information:

https://github.com/brianc/node-postgres/blob/master/test/integration/client/transaction-tests.js

[unikoid]

Thank you for your answer. The problem is solved.

[williamkapke]

@brianc - don't mess with me like this!

I don't think you ment to use $3 in your example. I had to throw together a sanity test...

pg.connect(function(err, client, done) {
    client.query('BEGIN');
    client.query('SELECT $1::text t1, $2::text t2', ['a', 'b'], function(err, qry){
        console.log(err || qry.rows);
    });
    client.query('SELECT $1::text t3', ['c'], function(err, qry){
        console.log(err || qry.rows);
    });
    client.query('COMMIT', function(err, qry){
        console.log("done");
        done();
    });
});

THAT code works. 👍
Using $3 in the second SELECT does not. 👎
(as far as I can tell)

[shaunc]

If you don't need to wrap in a transaction, but need atomic execution, the particular problem could be solved with a CTE as well:

...
client.query("with s as (INSERT INTO sometbl VALUES ($1, $2) returning 1) UPDATE sometable SET somevalue = $3 from s", ['a', 'b', 'c'])

[brianc]

haha @williamkapke sorry about that! Will edit my response to not suck!

[benjie]

(NOTE: the current pg supports both escapeIdentifier and escapeLiteral on client instances:

node-postgres/packages/pg/lib/client.js

Lines 408 to 437 in 3f6760c

	// Ported from PostgreSQL 9.2.4 source code in src/interfaces/libpq/fe-exec.c
	Client.prototype.escapeIdentifier = function (str) {
	return '"' + str.replace(/"/g, '""') + '"'
	}
	// Ported from PostgreSQL 9.2.4 source code in src/interfaces/libpq/fe-exec.c
	Client.prototype.escapeLiteral = function (str) {
	var hasBackslash = false
	var escaped = '\''
	for (var i = 0; i < str.length; i++) {
	var c = str[i]
	if (c === '\'') {
	escaped += c + c
	} else if (c === '\\') {
	escaped += c + c
	hasBackslash = true
	} else {
	escaped += c
	}
	}
	escaped += '\''
	if (hasBackslash === true) {
	escaped = ' E' + escaped
	}
	return escaped
	}

)

[jayarjo]

@benjie I do not recognize the logic can you elaborate on what escapeIdentifier and escapeLiteral are for?

[benjie]

Sure; escapeIdentifier is for escaping identifiers as documented here: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS "(To include a double quote, write two double quotes.)"

escapeLiteral is for escaping string constants as documented here: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

[dschinkel]

screw all that just use this by TJ: https://github.com/segmentio/pg-escape