Node.js - PostgreSQL - could not determine data type of parameter $1 error

Question

I'm trying to create a PostgreSQL prepared statement using node.js pg npm package. However, I keep getting an error:

could not determine data type of parameter $1

 function promiseQuery(sql, values) {
    return new Promise(function(resolve, reject) {
        pool.query('select $1 from workers', ['name'], function(err, result) {
            if (err) {console.log(err); reject(err)}
            else resolve(result.rows);   
        })
    });
}

In the db the name field is set to type text not null.

I also tries pg-promise, but with no success either.

@VaoTsun in the pg documentation there is a very similar example: https://www.npmjs.com/package/pg — wizard, May 29 '17 at 08:23
exactly, the docs specify the syntax: `pool.query('SELECT $1::int AS number', ['2'], function(err, res)` which you aren't using in your code — VladNeacsu, May 29 '17 at 08:24
in that example, brianc is a text value, not a column name - is it your case?.. — Vao Tsun, May 29 '17 at 08:25
Then pass the `name` as a parameter to your `promiseQuery` and just concatenate it, like: `'select ' + param['name'] + ' from workers'` — VladNeacsu, May 29 '17 at 08:29
I can do it, but it's very weird to me it's not possible to use prepared statement syntax. — wizard, May 29 '17 at 08:31
It wouldn't protect against SQL Injection if you could do dynamic SQL like that. — Łukasz Kamiński, May 29 '17 at 08:34
I think the whole point of prepared statements is to protect you from being able to do that. with plpgsql you can dinamically build `execute format('select %I from workers','name')` — Vao Tsun, May 29 '17 at 09:07

Daniel Vérité · Answer 1 · 2020-07-15T17:46:40.787

In the query select name from workers, from the point of view of the SQL syntax name is an identifier, and identifiers can never be passed as $N parameters, they must appear verbatim in the command. Otherwise the query cannot be prepared.

$N parameters can only appear in the query at positions where literals (constants) would be.

You'd have the same error if trying something similar with the PREPARE SQL command, outside of any client-side library:

PREPARE p as SELECT $1 FROM pg_class;
ERROR:  could not determine data type of parameter $1

The solution is to build the query in javascript with string replacement techniques for column names or table names, before submitting it to the database.

pg-promise does support injection of identifiers into a query with a specific syntax. From its documentation:

db.query('SELECT $1:name FROM $2:name', ['*', 'table']);
//=> SELECT * FROM "table"

> string replacement techniques Doesnt that re open the query to SQL injection? — Vincent Buscarello, Jul 15 '20 at 17:16
@VincentBuscarello: it's safe when adequately quoted. It appears that pg-promise does support the quoting/escaping of identifiers (not sure it did 3 years ago). I've added a bit to the answer. — Daniel Vérité, Jul 15 '20 at 17:52

vitaly-t · Answer 2 · 2017-06-14T06:11:26.850

Extending on the answer by Daniel Vérité...

You cannot combine Prepared Statements with dynamic column names, you'd have to generate the query on the client-side.

Using pg-promise syntax for SQL Names, you can properly escape your query like this:

db.any('SELECT $1~ FROM table', [colName])
// OR:
db.any('SELECT $1:name FROM table', [colName])
// OR:
db.any('SELECT ${colName~} FROM table', {colName})
// OR:
db.any('SELECT ${colName:name} FROM table', {colName})
// Etc, other variable syntax, like $[], $//, $<>, $()

And if you want to do it for a list of columns, then the simplest way to do it is like this:

const colNames = ['one', 'two', 'three'];

db.any('SELECT $1~ FROM table', [colNames])
// etc, the same variations as above, all will generate:
// SELECT "one","two","three" FROM table

or from all object properties:

const data = {
    one: 123,
    two: true,
    three: 'text'
};
db.any('SELECT $1~ FROM table', [data])
// etc, the same variations as above, all will generate:
// SELECT "one","two","three" FROM table

All these methods will properly escape the query, making sure SQL injection is not possible.

Node.js - PostgreSQL - could not determine data type of parameter $1 error

2 Answers2