Preparing statement with input in Nodejs and Sqlite3

Question

I'm trying to read from my database using the sqlite3 package in NodeJS. In the code I have:

 var sqlite = require('sqlite3');
 var db = new sqlite.Database('myDatabase.db');

 var someUserInput = 'Some given id by the user';

 db.all(
     'SELECT * FROM MyTable WHERE userId="' + someUserInput + '"',
     function(err, rows) { /* Do something here */});

Instead of directly adding the user input, I would like to be able to prepare the statement properly, in order to avoid any malicious input from the user.

I have tried:

 db.prepare('SELECT * FROM MyTable WHERE userId=?', someUserInput).sql

But that still gives me:

'SELECT * FROM MyTable WHERE userId=?'

With no replacement from the input.

Thanks!

In SQLite, query parameters are passed directly to the SQL executor; they don't get textually substituted into the query. — Colonel Thirty Two
– Colonel Thirty Two, Commented Dec 17, 2015 at 16:22

jkeeler · Accepted Answer · 2015-12-17 16:19:48Z

1

I think you almost had it with your db.all call. Try passing your user value as a parameter:

db.all(
     'SELECT * FROM MyTable WHERE userId=?',
     someUserInput,
     function(err, rows) { /* Do something here */});

Source: https://github.com/mapbox/node-sqlite3/wiki/API#databaseallsql-param--callback

answered Dec 17, 2015 at 16:19

jkeeler

1,0186 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pedro Over a year ago

Totally missed that when searching the documentation, thanks a lot!

Collectives™ on Stack Overflow

Preparing statement with input in Nodejs and Sqlite3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related