knex: Undefined binding(s) detected when compiling SELECT query.

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 ?

Below is the output

<-- HandshakeInitializationPacket
HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.7.17',
  threadId: 31198,
  scrambleBuff1: <Buffer 15 16 54 1c 2c 5d 2c 7c>,
  filler1: <Buffer 00>,
  serverCapabilities1: 65535,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 49663,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 6b 40 71 43 13 32 70 32 4c 27 0d 1f>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'root',
  scrambleBuff: <Buffer ce 32 57 bf 00 32 02 a9 69 7f 88 c0 e2 ea ca 33 3b 64 36 22>,
  database: '####',
  protocol41: true }

<-- HandshakeInitializationPacket
HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.7.17',
  threadId: 31199,
  scrambleBuff1: <Buffer 51 39 39 13 2e 19 61 7a>,
  filler1: <Buffer 00>,
  serverCapabilities1: 65535,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 49663,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 54 35 07 20 40 55 2d 5a 4b 06 3f 09>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'root',
  scrambleBuff: <Buffer 43 11 f7 29 08 98 26 f5 57 d1 f9 3d e8 ab 2e ef d3 a4 59 1c>,
  database: '#########',
  protocol41: true }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

<-- HandshakeInitializationPacket
HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.7.17',
  threadId: 31200,
  scrambleBuff1: <Buffer 6e 21 11 4d 48 3a 73 71>,
  filler1: <Buffer 00>,
  serverCapabilities1: 65535,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 49663,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 1b 68 28 1a 5f 52 33 2d 6d 73 58 3e>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'root',
  scrambleBuff: <Buffer f6 bd d7 05 24 87 03 6d 65 2c 4d a0 7c 28 2a 75 67 2d 85 09>,
  database: '###',
  protocol41: true }

Error: Undefined binding(s) detected when compiling SELECT query: select `skus`.* from `skus` where `id` = ? limit ?
    at QueryCompiler_MySQL.toSQL (/Users/Mrugesh/Roambee/repository/webserver/node_modules/knex/lib/query/compiler.js:131:13)
    at Builder.toSQL (/Users/Mrugesh/Roambee/repository/webserver/node_modules/knex/lib/query/builder.js:115:44)
    at /Users/Mrugesh/Roambee/repository/webserver/node_modules/knex/lib/runner.js:56:32
    at tryCatcher (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/util.js:16:23)
    at /Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/using.js:185:26
    at tryCatcher (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:693:18)
    at Promise._fulfill (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:638:18)
    at PromiseArray._resolve (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise_array.js:126:19)
    at PromiseArray._promiseFulfilled (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise_array.js:144:14)
    at Promise._settlePromise (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:574:26)
    at Promise._settlePromise0 (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/Users/Mrugesh/Roambee/repository/webserver/node_modules/bluebird/js/release/promise.js:693:18)
<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

--> ComQuitPacket
ComQuitPacket { command: 1 }

--> ComQuitPacket
ComQuitPacket { command: 1 }

--> ComQuitPacket
ComQuitPacket { command: 1 }

<-- HandshakeInitializationPacket
HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.7.17',
  threadId: 31201,
  scrambleBuff1: <Buffer 2a 0b 40 0f 4e 7a 76 10>,
  filler1: <Buffer 00>,
  serverCapabilities1: 65535,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 49663,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 0f 47 46 6c 63 26 4e 26 4c 45 17 70>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'root',
  scrambleBuff: <Buffer cd fe f5 fd 9c aa fe 39 02 85 79 6f 07 4b b5 18 36 0f 7b b1>,
  database: '####',
  protocol41: true }

<-- HandshakeInitializationPacket
HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.7.17',
  threadId: 31202,
  scrambleBuff1: <Buffer 1d 6b 6d 3b 3c 73 36 3a>,
  filler1: <Buffer 00>,
  serverCapabilities1: 65535,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 49663,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 6c 71 54 70 5f 59 6e 06 6c 64 21 4e>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'root',
  scrambleBuff: <Buffer 56 39 47 76 85 70 4c 6c cf da 28 e4 bf 2c 00 59 01 9e aa 2b>,
  database: '#####',
  protocol41: true }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

About this issue

  • Original URL
  • State: closed
  • Created 7 years ago
  • Reactions: 4
  • Comments: 19 (5 by maintainers)

Most upvoted comments

So what should be done there?

prevent invoking where if the key is undefined, or maybe set a default value

SKU.where('id', req.param.KEY || 0).fetch()
+5
absolux on Jul 11, 2017

@mrugesh-onehop it’s an opinionated behavior to throw an error when a an undefined binding if given to where() instead of ignoring it quietly SKU.where('id', undefined)

+5
absolux on Jul 11, 2017

Also to answer the question, there is no way to prevent knex from throwing an error when one passes undefined to its APIs.

Undefined is really meaningless value for SQL and earlier accidentally ignoring it caused lot of security holes in many apps (looks like it was also causing invalid functionality in your app if expected result was getting no results).

Ignoring whole where clause in case of undefined value was opinionated functionality and so is the throwing an error, but now it is actually documented functionality http://knexjs.org/#Builder-wheres).

Closing since there seems to be no actions to be taken to resolve this issue… we can keep up discussion here anyways.

+3
elhigu on Jul 11, 2017

Yeah, i gotta fix this soon.

+2
ghost on Jul 11, 2017

@uurtech no we haven’t upgraded knex, we are still on older version. If you still need to fix, you need to change in your query.

+1
ghost on Mar 2, 2022
Read more comments on GitHub
← knex: Unable to use most Knex types with my TypeScript code due to their not being exported
knex: Undefined max_batch when using knexSnakeCaseWrappers (Objection) →