🐛 BUG: Invalid BEGIN TRANSACTION detection

[schkovich]

Which Cloudflare product(s) does this pertain to?

D1, Wrangler core

What version(s) of the tool(s) are you using?

3.29.0 [wrangler]

What version of Node are you using?

v20.11.1

What operating system and version are you using?

Ubuntu 22.04.3 LTS

Describe the Bug

Observed behaviour

I tried to update a row in D1 by passing a string that contains the words BEGIN TRANSACTION utilising the Wrangler D1 command execute and got the error below.

✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions.

Expected behaviour

The row will be updated.

Steps to reproduce

• No worker involved. I am running a Wrangler command against D1
• wrangler.toml is not relevant, since I am running a Wrangler command against D1, but here it is:

  name = "ai"
  main = "src/index.js"
  compatibility_date = "2023-12-06"

• I am not running a local dev server
• To reproduce run from terminal the two bash commands below:

  TEXT="You can''t refer to a table that doesn''t yet exist.\n* If you get \"cannot start a transaction within a transaction\", make sure you have removed \`BEGIN TRANSACTION\` and \`COMMIT\` from your dumped SQL statements."
  npx wrangler d1 execute <any-valid-db-name> --local --command "UPDATE Nodes SET text=\"${TEXT}\" WHERE id=537;" 

Please provide a link to a minimal reproduction

No response

Please provide any relevant error logs

No response

[schkovich]

A workaround is to add a space between BEGIN and TRANSACTION, eg BEGIN TRANSACTION. Since having two spaces is a valid SQL command, it makes me think that it might be possible to bypass this restriction by adding an extra space to the BEGIN TRANSACTION command. For example, create a file elon.sql:

BEGIN  TRANSACTION;
INSERT INTO "X" ("id", "text") VALUES (314, "Formerly know as Twitter");
# 101k insert statements
COMMIT;

and run

npx wrangler d1 execute musk-db --file=elon.sql