I have a sqlite query that I'm looking into parameterization to avoid bad sql injection things on the internet...
So things like:
Select * From myTable Where id = $id
are fine if I have $id defined somewhere and pass that as a parameter to my db calls.
paramters.$id = 150;
db.all(myQuery, parameters, function (err, rows) {
results = rows;
});
I wonder if I need to go out of my way to also parameterize things that are sorted and paginated (both are inputs that users can give)...
I tried to do something like:
var sorter = JSON.parse(value);
parameters.$sortMethod = sorter.method;
parameters.$sortOrder = sorter.order;
sort_filter += 'ORDER BY $sortMethod $sortOrder';
No dice though. I'm guessing sqlite3 just doesn't let you parameterize things that are in ORDER, LIMIT and OFFSET. I thought there was something really sneaky maybe folks out there could do by ending a sqlite statement prematurely in the order and then creating a new malicious statement, but maybe SQLITE3 only lets you exercise one statement at a time (http://www.qtcentre.org/threads/54748-Execute-multiple-sql-command-in-SQLITE3)
Should I not worry about parameterizing things in order limit and offset? For reference, I'm running this on node.js with this sqlite library: https://github.com/mapbox/node-sqlite3
Thanks much in advance!
SQLite (and any other database) allows you to parameterize expressions, that is, any numbers, strings, blobs, or NULL values that appear in a statement. This includes the values in the LIMIT/OFFSET clauses.
Anything else cannot be parameterized. This would be table and column names, operators, or any other keyword (like SELECT, ORDER BY, or ASC).
If you need to change any parts of your SQL statements that are not expressions, you have to create the statement on the fly. (There is no danger of SQL injection as long as your code constructs the statement by itself, not using any unchecked user data.)