Issue Review: cloudflare/workers-sdk#5815

Summary

D1 queries with mixed anonymous (?) and numbered (?1) parameters fail with "Wrong number of parameter bindings" when anonymous parameters appear before numbered parameters in the SQL.

Findings

• Created: 2024-05-13
• Updated: 2025-10-30
• Version: 3.55.0 (wrangler) → 4.60.0 (current)
• Component: D1 (workerd SQLite layer)
• Labels: bug, d1
• Comments: 0

Key Evidence

1. Issue successfully reproduced with wrangler 4.60.0 local D1
2. Root cause identified: SQLite parameter assignment behavior
3. Related issue #5765 was closed as "not a bug" but this issue describes inconsistent behavior worth documenting

Root Cause Analysis

The issue stems from SQLite's parameter assignment rules (from SQLite docs):

? - A question mark that is not followed by a number creates a parameter with a number one greater than the largest parameter number already assigned.

When parsing UPDATE table SET column1 = ?, column2 = ? WHERE id = ?1:

1. First ? is parsed → no params assigned yet → index 1
2. Second ? is parsed → max is 1 → index 2
3. ?1 is parsed → explicitly index 1 (same slot as first ?!)
4. sqlite3_bind_parameter_count() returns 2 (highest index)

User expects 3 parameters but SQLite only sees 2 unique slots. The first ? and ?1 share index 1.

Reproduction Results

Test	Query Pattern	Params	Result
1	SET col1 = ?, col2 = ? WHERE id = ?1	3	FAILS
2	SET col1 = ?, col2 = ? WHERE id = ?	3	PASSES
3	WHERE id = ?1 AND col1 = ? AND col2 = ?	3	PASSES
4	SET col1 = ?2, col2 = ?3 WHERE id = ?1	3	PASSES
6	SET col1 = ? WHERE id = ?1	1	PASSES (!)
10	WHERE id = ?1 AND col2 = ?	2	PASSES
12	SET col1 = ?, col2 = ? WHERE id = ?1	2	PASSES (!)

Test 6 and 12 are particularly revealing: the "failing" queries actually work with fewer parameters because ? and ?1 share the same binding slot.

Code References

The error occurs in workerd at:

• src/workerd/util/sqlite.c++:1419-1420:

SQLITE_REQUIRE(size == sqlite3_bind_parameter_count(statement), kj::none,
    "Wrong number of parameter bindings for SQL query.");

The D1 API in workerd passes bindings directly:

• src/cloudflare/internal/d1-api.ts:470-509 - bind() method transforms values
• Bindings are passed as an array mapped to sequential SQLite parameter indices

Recommendation

Status: KEEP OPEN

Reasoning: While this is technically expected SQLite behavior (SQLite docs say "to avoid confusion, it is best to avoid mixing named and numbered parameters"), the current behavior is:

1. Inconsistent (works in some orderings but not others)
2. Produces a misleading error message ("wrong number" when really "conflicting indices")
3. Not documented in D1 documentation
4. A common user footgun (multiple issues reported)

Action: Either:

1. Add validation in D1 to detect and warn about mixed parameter styles with a clear error message
2. Add clear documentation about this limitation
3. Consider pre-processing SQL to rewrite parameters to avoid conflicts

Proposed Solution

Option A: Better Error Detection (Recommended - Medium Difficulty)

Add validation in the D1 API layer (d1-api.ts or miniflare's database.worker.ts) to detect mixed parameter styles and provide a clearer error:

// In D1PreparedStatement.bind() or before sending to workerd
function validateParameterStyle(sql: string): void {
  const numberedMatch = sql.match(/\?(\d+)/g);
  const anonymousCount = (sql.match(/\?(?!\d)/g) || []).length;
  
  if (numberedMatch && anonymousCount > 0) {
    // Check for potential conflicts
    const numberedIndices = new Set(numberedMatch.map(m => parseInt(m.slice(1))));
    const maxNumbered = Math.max(...numberedIndices);
    
    // If anonymous params could overlap with numbered ones, warn
    if (anonymousCount + maxNumbered > numberedIndices.size + anonymousCount) {
      throw new Error(
        `D1_ERROR: Cannot mix anonymous (?) and numbered (?N) parameters in a way that causes index conflicts. ` +
        `Use only anonymous parameters (?), only numbered parameters (?1, ?2, ...), or ensure numbered parameters ` +
        `are all higher than the position where they appear. See: https://developers.cloudflare.com/d1/...`
      );
    }
  }
}

Option B: Documentation Only (Easy Difficulty)

Add a warning to D1 documentation explaining:

• Anonymous ? and numbered ?N parameters should not be mixed
• If mixed, numbered parameters must appear before all anonymous parameters
• Workaround: use all anonymous or all numbered parameters

Files That Would Need Modification

For Option A:

• packages/miniflare/src/workers/d1/database.worker.ts - Add validation before query execution
• src/cloudflare/internal/d1-api.ts (in workerd) - Add validation in bind/prepare

For Option B:

• Cloudflare D1 documentation (external repo)

Testing Recommendations

1. Add test cases for mixed parameter patterns:

   • Anonymous before numbered (should fail with clear message)
   • Numbered before anonymous (should work)
   • All anonymous (should work)
   • All numbered (should work)
   • Numbered with gaps (?1, ?3) with anonymous filling gaps

2. Add integration tests in:

   • packages/miniflare/test/plugins/d1/index.spec.ts
   • D1 E2E tests if they exist

Implementation Difficulty

Medium for Option A:

• Requires understanding SQLite parameter assignment rules
• Need to handle edge cases (named parameters :name, @name, $name)
• Should work consistently between local (miniflare) and remote D1

Easy for Option B:

• Documentation update only

Suggested Comment

This issue has been verified and the root cause identified. The behavior is due to SQLite's parameter assignment rules where anonymous ? parameters get assigned indices sequentially, which can conflict with explicitly numbered ?N parameters.

Workaround: Use only anonymous parameters (?) or only numbered parameters (?1, ?2, ?3). If mixing, ensure numbered parameters appear before all anonymous parameters in the SQL string.

We're keeping this open to track adding either:

1. Better error detection/messages for this pattern
2. Documentation about this limitation

For reference, the SQLite docs state: "To avoid confusion, it is best to avoid mixing named and numbered parameters."