13
var knex = require('knex')(config);
var bookshelf = require('bookshelf')(knex);
var SKU = bookshelf.Model.extend({
    tableName: 'skus',

});
SKU.where('id', undefined).fetch().then(function (skus) {
    if (skus) console.log(skus.toJSON());
}).catch(function (err) {
    console.error(err);
});

It throws

Undefined binding(s) detected when compiling SELECT query.

It is working fine with 0.11.5, it stopped working with 0.11.6 onwards. I am pointing to 0.13.0 right now.

useNullAsDefault: true

works fine with insert queries but not select queries. Is there any flag i should pass to resolve this error?

Mrugesh Vaghela
  • 195
  • 1
  • 1
  • 11

2 Answers2

16

.where('id', undefined) does not mean anything in SQL. You cannot be querying something that is not there.

Maybe you wanted to query where id IS NULL? With knex it can be done like this: .whereNull('id')

With earlier knex versions it would have been just ignored, but starting from 0.12.x.

So to have equivalent functionality with newer (and actually it is compatible with older < 0.12 versions too) knex versions you should do:

SKU.fetch().then(function (skus) {
    if (skus) console.log(skus.toJSON());
}).catch(function (err) {
    console.error(err);
});

Unless bookshelf is adding some extra magic there...

The useNullAsDefault: true option Is used only for when inserting multiple rows in one insert. It does not make sense to use it unless you are using sqlite (check last example of http://knexjs.org/#Builder-insert).

Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • This error can occur if the Knex query relies on a parameter value that's supplied using JSON, and the JSON is invalid in some way. E.g you might send an HTTP POST request, and the request handler performs a Knex query based on the HTTP body. If you don't specifying that the POST body is JSON (`Content-Type: application/json; charset=utf-8`), or the JSON is invalid, you may see the error. – Chris Halcrow Mar 05 '21 at 04:31
  • Yes, there are literally infinite ways to provide undefined values to knex. – Mikael Lepistö Mar 06 '21 at 13:07
  • Must say this is a very annoying error! `where({id: undefined}` shouldn't result in an "undefined column `id`" error message. – ankush981 Mar 31 '22 at 12:00
  • @ankush981 actually it should throw the error. Historically many knex apps had really huge security holes when `undefined` filters were just ignored and instead of returning / modifying correct row, bunch of unrelated rows were returned/modified. – Mikael Lepistö Apr 04 '22 at 07:50
2

parameter mismatch in payload

the parameter which sql query is expecting and the parameter you sending in requestbody is not matching. in my case sql was expecting parameter "gradeId" but in requestBody i was sending "postId"

Aayush Bhattacharya
  • 1,628
  • 18
  • 17