CODEWITHSUNDEEP
javascriptnode.jspostgresqlpgpg-promise
wizard
wizard

Reputation: 583

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

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.

Upvotes: 6

Views: 13273

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61516

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"

Upvotes: 4

vitaly-t
vitaly-t

Reputation: 25840

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.

Upvotes: 2

Related Questions