SQL sanitation help: sqlstring library + paramterized queries.

Hi Everyone!​I am fairly new to node and have an issue. I am trying to paramterize queries using the sqlstring library in order to make them safe for the database to run... Here is some example code:​let filter = idList !== '' ? ` AND id IN(${idList})` : ''; filter += locationsList !== '' ? ` AND a.locationID IN(${locationsList})` : ''; filter += start !== undefined ? ` AND a.lastEdited >= ${start}` : ''; filter += end !== undefined ? ` AND a.lastEdited <= ${end}` : ''; filter += name !== undefined ? ` AND a.name LIKE '%${name}%'` : ''; const qry = ` SELECT a.id 'id' ,a.number 'number' ,a.name 'name' ,a.locationID 'locationID' ,a.stale 'staleHit' ,a.location 'location' ,a.lastEdited 'lastEdited' ,a.userID 'owner' FROM tbl_foo_${'?'} a WHERE a.id > ${'?'} ${'?'} LIMIT ${'?'};`; let values = [ id, cursor, filter, limit ]; const rows = query(db, qry, values); //inside of query it does this and then runs the query against the database if (qry.includes('?')) { sanitizedQry = sqlstring.format(qry, values); } The issue with this being is that filters are optional depending on what a user passes into the function so the order of them can change. As far as i know Sqlstring uses the order to determine what value matches the proper question mark. If no filters are active then i just get left with '' an empty string and it throws a sql syntax error.​If i patamterize every variable in the filters then they get out of order if only name is active, but not the rest.Any help with this would be awesome. Thanks!

Submitted August 06, 2019 at 04:35AM by Atlantisman