[sqlite] PRAGMA + bindings not working

[WebReflection]

What version of Bun is running?

0.5.6

What platform is your computer?

Linux 6.1.8-arch1-1 x86_64 unknown

What steps can reproduce the bug?

import { Database } from "bun:sqlite";

const name = 'test';

const db = new Database("pragma.bug.db");
db.run(`
  CREATE TABLE IF NOT EXISTS ${name} (
    id INTEGER PRIMARY_KEY,
    value TEXT NOT NULL
  )
`);

// this works
console.log(db.query(`PRAGMA table_info('${name}')`).all());

// and this works too
console.log(db.query(`SELECT * FROM pragma_table_info(?)`).all(name));

// as well as this one
console.log(db.query(`SELECT * FROM pragma_table_info($name)`).all({$name: name}));

// ... HOWEVER ...

// this doesn't work
console.log(db.query(`PRAGMA table_info(?)`).all(name));

// and neither does this one
console.log(db.query(`PRAGMA table_info($name)`).all({$name: name}));

db.close();

What is the expected behavior?

PRAGMA queries should accepts bindings just like SELECT or other queries.

What do you see instead?

290 |       : SQL.run(this.#handle, query, ...params);
291 |   }
292 | 
293 |   prepare(query, params, flags) {
294 | 
295 |     return new Statement(SQL.prepare(this.#handle, query, params, flags || 0));
                            ^
error: near "?": syntax error
      at prepare (bun:sqlite:295:25)
      at query (bun:sqlite:334:15)
      at /home/.../pragma.bug.js:25:12

Additional information

No response

[WebReflection]

FWIWI it looks like better-sqlite has a dedicated .pragma command to execute PRAGMA related queries ... maybe that's the missing part here?

[eliot-akira]

The issue still exists with Bun version 1.0.26.

35 |   return db.prepare(`pragma table_info(?)`).all(table)
              ^
SQLiteError: near "?": syntax error
 errno: 1
      at prepare (bun:sqlite:193:19)

---

In the documentation for better-sqlite3, there's a note about using their provided method db.pragma().

It's better to use this method instead of normal prepared statements when executing PRAGMA, because this method normalizes some odd behavior that may otherwise be experienced. The documentation on SQLite3 PRAGMA can be found here.

(From https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#pragmastring-options---results)

But the implementation of this method doesn't look like it's doing anything special, it simply calls prepare(). Searching their codebase for pragma (result) didn't yield any insights.

Searching through relevant parts of Bun (like src/js/bun/sqlite.ts and src/bun.js/bindings/sqlite), I didn't see any obvious solution either.

---

For now, as suggested in the issue description, the workaround is to not use prepared parameters. (And sanitizing any passed values beforehand.)

db.prepare(`pragma table_info('${table}')`).all()