Error: D1_ERROR: Wrong number of parameter bindings for SQL query.

[alexgallacher]

Hi Team - I believe I'm experiencing the same issue identified in 504 where I'm getting the following error when attempting to Insert into D1. I understand a new Miniflare release should have fixed this issue.

✘ [ERROR] Error processing request: Error: D1_ERROR: Wrong number of parameter bindings for SQL query.

const sql = `
INSERT INTO Events (eventType, severity, eventId, source, eventTime, eventTimeUK, timeCreated)
VALUES (?, ?, ?, ?, ?, ?, ?)
`;
const parameters = [eventType, severity, eventId, source, eventTime, eventTimeUK, timeCreated]; // these are defined on the fly
const result = await db.prepare(sql).run(parameters);

I'm using ⛅️ wrangler 3.22.4. Perhaps I'm doing something wrong but any assistance would be greatly appreciated.

Many thanks for any comments in advance.

Alex.

[mrbbot]

Hey! 👋 Thanks for raising this. Does this code work for you when deployed to production? Could you share the version of your database from wrangler d1 info <database_name> too?

[SupremeTechnopriest]

I just hit this as well. It also happens in production. I am running wrangler 3.50.0. There doesnt appear to be a version in the output from wrangler d1 info, but my created_at is 2023-09-29T23:08:01.953Z.

Sample query I am trying to parameterize:

    const limit = ctx.query.limit || 25
    const page = (ctx.query.page || 0) * limit

    const sql = oneline`
        SELECT t.id, t.name, t.description, t.shareLink, a.avatar, a.firstName, a.lastName FROM templates t
        LEFT JOIN accounts a ON t.ownerId = a.id
        INNER JOIN templates_fts s ON s.id = t.id
        WHERE templates_fts MATCH '{name description}: ?'
        ORDER BY bm25(templates_fts, 0, 2, 1)
        LIMIT ?
        OFFSET ?;
    `
    const result = await ctx.env.DB.prepare(sql)
      .bind(ctx.query.query, limit, page)
      .run()

Results in this error: D1_ERROR: Wrong number of parameter bindings for SQL query.

Query works fine when it isn't parameterized. I guess for now I can manually sanitize the input, but would be nice to have this fixed.

Let me know if you want me to open this issue in the workerd repo.

[seivad]

Hey I just hit this as well, after bashing it around for longer than needed, co-pilot did a sneaky typeahead with the three dots to de-structure and it seems to have worked:

Example:

let deleting = ['test1', 'test2']
const records = await c.env.DB.prepare(`SELECT * FROM table WHERE column IN (${deleting.map(() => '?').join(', ')})`)
.bind(...deleting)
.run()
console.log('Records::', records)

[SupremeTechnopriest]

@seivad I'll try spreading an array into bind tomorrow and report back. Thanks for sharing!

[SupremeTechnopriest]

Didn't work for me. Didn't really expect it to... would have been surprised if it did.

[matthewlynch]

@SupremeTechnopriest I had this issue late last night but realised that the were a couple of quotes that were messing with the query. I'd look at the WHERE templates_fts MATCH '{name description}: ?' because I'm assuming ctx.query.query is also a string and it might not get formatted correctly.

[SupremeTechnopriest]

@matthewlynch InterestingI Yes it is also a string. How did you solve it on your end?

[SupremeTechnopriest]

The query definitely needs the quotes... It looks like the ? isn't being parsed out properly when its inside quotes. I think this is a bug in D1.

[matthewlynch]

@SupremeTechnopriest Can you log the value of sql and ctx.query.query? My assumption, based off the code you provided is that there are additional quotes being injected in the first parameter binding.

I noticed my issue when I started logging the queries being sent to D1 via the ORM I am using (Drizzle).

[SupremeTechnopriest]

@matthewlynch

SQL:

SELECT t.id, t.name, t.description, t.shareLink, a.avatar, a.firstName, a.lastName FROM templates t
      LEFT JOIN accounts a ON t.ownerId = a.id
      INNER JOIN templates_fts s ON s.id = t.id
      WHERE templates_fts MATCH '{name description}: ?'
      ORDER BY bm25(templates_fts, 0, 2, 1)
      LIMIT ?
      OFFSET ?

ctx.query.query:

foo

The query is a user's search term. So in theory this could be anything. I would expect that the parameterized query would sanitize and escape the input.

[SupremeTechnopriest]

I am also using drizzle, but to rule out any weirdness there I went direct to the database. Drizzle doesnt support virtual tables, so I would have to use:

sql``

For now I have to use sql.raw() to make this query work.

[AlecKriebel]

For others finding this, this was caused by incorrect quoting within my Drizzle queries.

itemId in these examples is a string. This fails:

sql`json_extract(metadata, '$.itemId') = '${itemId}'`

This succeeds, and does not throw D1_ERROR: Wrong number of parameter bindings for SQL query.:

sql`json_extract(metadata, '$.itemId') = ${itemId}`,

[penalosa]

@alexgallacher @SupremeTechnopriest does @AlecKriebel's solution work for you?

[SupremeTechnopriest]

@penalosa I can try again today, but I'm pretty sure the quotes are required for FTS. Ill let you know.

[wataruoguchi]

I had the same error message. @AlecKriebel 's solution worked.

The following doesn't work:

_db
        .select()
        .from(users)
        .where(sql`lower(${users.name}) = '${name}'`)

generates:

sql: `select "id", "name", "email" from "users" where lower("users"."name") = '?'`,
params: [ 'nichole' ]

Then the one below, without quotes around name works:

_db
        .select()
        .from(users)
        .where(sql`lower(${users.name}) = ${name}`)

generates:

sql: `select "id", "name", "email" from "users" where lower("users"."name") = ?`,
params: [ 'nichole' ]

[penalosa]

We haven't heard from you in while so I'm going to close this issue for now. If you're still running into problems feel free to open a new issue with more details and we can investigate further.

[JustFly1984]

@penalosa please reopen
This is sucks that we currently can't type exact amount of arguments required for bind based on the 1?, 2? in prepared SQL query.

For some reason I had a duplicate, possible after merge conflict, which made 25 arguments instead of 24 in one request from 3 in the same file.

D1PreparedStatement.bind(...values: unknown[]): D1PreparedStatement

unknown[] should be a tuple [unknown,unknown, unknown, unknown, unknown] the size required by prepare.