中文
devexamples

SQLite Parameterized IN Clause Safety

Build dynamic IN clauses with generated placeholders instead of string interpolation, keeping every user value bound as a statement parameter.

JavascriptNodeSecurity Boundaries

Code

snippet
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT)');
db.prepare('INSERT INTO users (email) VALUES (?)').run('[email protected]');

function findEmails(ids) {
  if (ids.length === 0) return [];
  if (ids.some((id) => !Number.isInteger(id))) {
    throw new TypeError('ids must be integers');
  }

  const placeholders = ids.map(() => '?').join(', ');
  const stmt = db.prepare(
    'SELECT id, email FROM users WHERE id IN (' + placeholders + ')',
  );
  return stmt.all(...ids);
}

try {
  // 恶意输入只是参数值，永远不会被解析成 SQL 语法
  db.prepare('SELECT * FROM users WHERE email = ?').all("x' OR '1'='1");
} catch (err) {
  console.log('injection attempt contained:', err instanceof Error);
}

const rows = findEmails([1, 2, 3]);
console.log('matched rows:', rows.length);
db.close();

Related Examples