Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How would I pass an array argument into this SQLite query?

I’m using the promised-sqlite3 library here for queries.

I need to match the string value of the field video against any one of the strings in the array videos.

At first I tried:

  await db.all('SELECT video, offset, watched FROM watched WHERE user = ? AND video IN ?',
    username(req), videos);

…but that syntax isn’t accepted. Then I tried:

  await db.all('SELECT video, offset, watched FROM watched WHERE user = ? AND video IN (?)',
    username(req), videos);

…which runs without error, but gave me no matches when I should have gotten a few. What works is this:

  await db.all(`SELECT video, offset, watched FROM watched WHERE user = ? AND video IN ('${videos.join("','")}')`,
    username(req));

…but that’s hardly a "best practices" way to do this, even though I do filter the content of the array to prevent SQL injection.

Is there a proper way to pass an array value that I haven’t found yet?

Tags:

2

Answers


  1. 0

    I don’t think this library supports using a single placeholder for a list value, like node-mysql does. So you need to construct the (?, ?, ...) list dynamically, and provide all the values using a spread argument.

    placeholders = Array(videos.length).fill('?').join(',');
await db.all(`
    SELECT video, offset, watched 
    FROM watched 
    WHERE user = ? AND video IN (${placeholders})`,
    username(req), ...videos);
    Login or Signup to reply.
  2. 0

    SQLite does not support binding an array like that.

    An alternative approach to @Barmar’s answer that I’ve used with arrays of size 10k+ items in production is to use json_each and pass the data as a JSON string:

    await db.all(
  "SELECT video, offset, watched FROM watched WHERE user = ? AND video IN (SELECT value from json_each(?))",
  username(req),
  JSON.stringify(videos),
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search