node-postgres $1 IS NULL error

Question

I am using node-postgres to write a web app backend, and I am running into this error:

error: could not determine data type of parameter $1
    at Connection.parseE (/home/***/niche-api/node/node_modules/pg/lib/connection.js:539:11)
    at Connection.parseMessage (/home/***/niche-api/node/node_modules/pg/lib/connection.js:366:17)
    at Socket.<anonymous> (/home/***/niche-api/node/node_modules/pg/lib/connection.js:105:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:172:18)
    at Socket.Readable.push (_stream_readable.js:130:10)
    at TCP.onread (net.js:542:20)

The goal of my query is to accept a query parameter passed in from the node express app, but to not filter the database query if the parameter does not exist in the api call. For example, if my parameter is called variableType and it is set to tmax (e.g., variableType=tmax), the database only responds with records with variableType of tmax. If the endpoint is hit without the query parameter, the database returns all records.

My query is:

SELECT * FROM variableTypes WHERE 1 = 1 AND ($1 IS NULL or $1 = variableTypeAbbreviation);

And I am calling it like this:

app.get("/variables", function(req, res){
  //get a list of the variables in the database
  var variableType = req.query.variableType
  if (variableType == undefined){
    variableType = null;
  }
  var client = new pg.Client({
    user: keys.user,
    password: keys.password,
    database: keys.dbName,
    hostname: keys.hostname
  })
  client.connect(function(err){
    if (err){
      res.json(err);
    }

    var query = client.query({name: 'variableSelect', text: "SELECT * FROM variableTypes WHERE 1 = 1 AND ($1 IS NULL or $1 = variableTypeAbbreviation);", values:[variableType]});
    console.log(query)
    query.on('row', function(row, result){
      console.log("Got row")
      result.addRow(row)
    })
    query.on('end', function(result){
      console.log("Done.")
      res.json(result)
      client.end()
    })

I've narrowed it down to the problem being located in the section of the query ($1 IS NULL). I don't really know where to go from here. I've written a similar query in python (using psycopg2), which works, which makes me think that it is more related to the node package.

  query =   select variableTypeID, variableType, variableTypeAbbreviation
    from variableTypes
    WHERE 1 = 1
    AND
        (%(abbreviation)s is NULL or %(abbreviation)s LIKE lower(variableTypes.variableTypeAbbreviation) )
        AND  (%(fullName)s is NULL or %(fullName)s = variableTypes.variableType )

cursor.execute(query, {'fullName': fullName, 'abbreviation' : abbreviation})

Any advice is much appreciated!

vitaly-t · Accepted Answer · 2016-06-16 15:24:08Z

2

The problem is in $1 IS NULL, where $1 is treated as a dynamic column name, which is not allowed in prepared statements, due to the protections against SQL injection implemented by the database server.

UPDATE

If you want to format your queries freely, while also without the risk of an SQL injection, check out pg-promise. And to properly format names for schema, table or column see SQL Names.

edited Jun 16, 2016 at 15:24

answered Jun 15, 2016 at 22:39

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SCOTT FARLEY Over a year ago

Thanks! That was my conclusion as well. Is there any way to achieve the same result in sql without this syntax?

vitaly-t Over a year ago

@SCOTTFARLEY, yes, without using prepared statements, with simple query parameterization. I have edited my answer with an example.

Emanuel Friedrich · Accepted Answer · 2023-06-30 14:08:57Z

0

think you need set the type of the $1 parameter, was my case

CASE WHEN $1::varchar IS NOT NULL THEN category = $1::varchar ELSE true END and

answered Jun 30, 2023 at 14:08

Emanuel Friedrich

3443 silver badges10 bronze badges

Collectives™ on Stack Overflow

node-postgres $1 IS NULL error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related