[sqlite] Positional numbered question mark parameters are incorrectly bound as named parameters

Version

v24.5.0

Platform

Linux dev 6.1.0-37-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.140-1 (2025-05-22) x86_64 GNU/Linux

Subsystem

sqlite

What steps will reproduce the bug?

test("numbered question mark parameters", () => {
  const db = new DatabaseSync(":memory:")
  db.exec(`CREATE TABLE TestInsertTable (arg1, arg2);`)
  const statement = db.prepare(`
    INSERT INTO TestInsertTable (arg1) VALUES (?1);`)

  statement.run("asdf")  // This line fails.

  statement.run({ "?1": "asdf" })   // This line succeeds.
})

How often does it reproduce? Is there a required condition?

It's always reproducible.

What is the expected behavior? Why is that the expected behavior?

Numbered question mark parameters are positional parameters and they should be bound left-to-right from ?1 to SQLITE_MAX_VARIABLE_NUMBER.
Please refer to https://www.sqlite.org/lang_expr.html#varparam
In Node.js' implementation, however, they are bound as named parameters.
I verified that in other implementations (e.g. Python), they are correctly bound as positional parameters (as a tuple or array) and do not require passing the {"?1": value, ...} object, which defeats the purpose of the numbered question mark parameters.

What do you see instead?

Sqlite exception: Error: column index out of range

Additional information

https://www.sqlite.org/lang_expr.html#varparam

Just for a reference, would you mind linking to the Python example you mentioned?

I've made a simple experiment (in the PR) that I think will make it work, but now I'm wondering about it. Since ?NNN are not anonymous parameters like ?

I mean, it makes sense for anonymous params to be positional, but I'm not sure about the others. In the classification, ?NNN, @AAA, :AAA, and $AAA have similar behavior, so they should be treated alike. Maybe?

Sure, here's an example in Python:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        department TEXT NOT NULL,
        salary REAL,
        hire_date TEXT
    )
''')

cursor.execute('''
    INSERT INTO employees (name, department, salary, hire_date)
    VALUES (?1, ?2, ?3, ?4)
''', ("Alice Johnson", "Engineering", 75000.0, "2023-01-15"))

cursor.execute('''
    INSERT INTO employees (name, department, salary, hire_date)
    VALUES (?1, ?2, ?3, ?4)
''', ("Bob Smith", "Marketing", 65000.0, "2023-02-20"))

(I just executed it successfully on Python 3.12.4)

The unit test above (in JavaScript) can be updated to look like this:

const statement = db.prepare(`
    INSERT INTO TestInsertTable (arg1, arg2) VALUES (?1, ?1);`)

  statement.run("asdf")  // This line fails while it should succeed.

The documentation clearly identifies ?NNN as ordinal, while $, @, and : are "named" - https://www.sqlite.org/lang_expr.html#varparam

I was asking in the sense that sqlite3_bind_parameter_name returns a name for all the named params.

sqlite3_bind_parameter_name(stmt, 1); // returns "?1" for ?NN params

I still agree that it would make much more sense to be positional in the interface. The PR is in place. Thanks for reporting.

Navigation Menu

Repository navigation

[sqlite] Positional numbered question mark parameters are incorrectly bound as named parameters #59340

Version

Platform

Subsystem

What steps will reproduce the bug?

How often does it reproduce? Is there a required condition?

What is the expected behavior? Why is that the expected behavior?

What do you see instead?

Additional information

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[sqlite] Positional numbered question mark parameters are incorrectly bound as named parameters #59340

Description

Version

Platform

Subsystem

What steps will reproduce the bug?

How often does it reproduce? Is there a required condition?

What is the expected behavior? Why is that the expected behavior?

What do you see instead?

Additional information

Activity

geeksilva97 commented on Aug 4, 2025

geeksilva97 commented on Aug 4, 2025

WillAvudim commented on Aug 4, 2025

WillAvudim commented on Aug 4, 2025

geeksilva97 commented on Aug 5, 2025

geeksilva97 commented on Aug 5, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions