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: npmjs.com/package/pg — wizard
– wizard, Commented May 29, 2017 at 8: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
– VladNeacsu, Commented May 29, 2017 at 8:24
in that example, brianc is a text value, not a column name - is it your case?.. — Vao Tsun
– Vao Tsun, Commented May 29, 2017 at 8:25
It wouldn't protect against SQL Injection if you could do dynamic SQL like that. — Łukasz Kamiński
– Łukasz Kamiński, Commented May 29, 2017 at 8:34

Daniel Vérité · Accepted Answer · 2020-07-15 17:46:40Z

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?
@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.

vitaly-t · Accepted Answer · 2017-06-14 06:11:26Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related