Search code examples
node.jssql-serverknex.jswhere-in

Knex + SQL Server whereIn query 8-12s -- raw version returns NO results but if I input the .toQuery() result directly I get results

The database is in Azure cloud and not being used in production currently. There are 80.000 rows and a uprn is a VARCHAR(100);

I'm already using JOI to validate each UPRN as well;

I'm using KNEX with a SQL Server database with the following whereIn query: 

knex(LOCATIONS.table).whereIn(LOCATIONS.uprn, req.body.uprns)

but this takes 8-12s to complete and sometimes timesout. if I use .toQuery() on the same thing, SSMS will return the result within 1-2.

If I do a raw query, the resulting .toQuery() or toString() works in SSMS and returns results. But if I try to use the raw directly, it will return 0 results.

I'm looking to either fix what's making whereIn so slow or get the raw query working.

EDIT 1:

After much debugging and trying -- it seems that the bug is due to how knex deals with arrays, so I made a for-of loop to add ? ? ? for each array element and then inputed the array for all params.

This led me to realizing the performance issue is due to SQL server way of parameterising.

I ended up building a raw query string with all of the parameters and validating the input with Joi string/regex config: 

 Joi.string()
            .min(1)
            .max(35)
            .regex(/^[a-z\d\-_\s]+$/i)

allowing only for alphanumeric, dashes and spaces which should prevent sql injection.

I'm going to look deeper into security issues with this and might make a separate login that can only SELECT data from that table and nothing more to run with these queries.

Solution

  • Needed to just handle it raw and validate separately.