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

admin管理员组

文章数量:1346316

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.

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.

• javascript
• node.js
• postgresql
• pg
• pg-promise

Share Improve this question Follow edited Oct 7, 2018 at 21:33 S-Man 23.8k88 gold badges4747 silver badges7373 bronze badges asked May 29, 2017 at 8:20 wizardwizard 58322 gold badges1010 silver badges2626 bronze badges 9

• are you sure you can pass identifier like that?.. – Vao Tsun Commented May 29, 2017 at 8:22
• @VaoTsun in the pg documentation there is a very similar example: npmjs./package/pg – 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 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 Commented May 29, 2017 at 8:25
• 1 It wouldn't protect against SQL Injection if you could do dynamic SQL like that. – Łukasz Kamiński Commented May 29, 2017 at 8:34

 |  Show 4 more ments

2 Answers 2

Sorted by: Reset to default 4

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 mand. 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 mand, 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"

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

You cannot bine 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.

本文标签： javascriptNodejsPostgreSQLcould not determine data type of parameter 1 errorStack Overflow