workerd 🐛 Bug Report: SqliteDatabase::prepareSql does not handle CRLF line endings

workerd will reject prepared statements executed in SINGLE mode appear to have more than one statement.

https://github.com/cloudflare/workerd/blob/d9ed653a4d1ba4926906884c6f562ce65a5d2f27/src/workerd/util/sqlite.c%2B%2B#L392-L396

However, the code for incrementing tail seems to only account for \n (LF) whereas users on Windows will have \r\n (CRLF).

https://github.com/cloudflare/workerd/blob/d9ed653a4d1ba4926906884c6f562ce65a5d2f27/src/workerd/util/sqlite.c%2B%2B#L390-L390

The issue can be seen when using Wrangler and providing a file with a newline, when saved with CRLF line endings:

$ wrangler d1 execute <db> --file ./sql --local

❌ - A prepared SQL statement must contain only one statement.
INSERT INTO foo VALUES ('bar');

It can also be reproduced in a Cloudflare Worker:

// ❌ - A prepared SQL statement must contain only one statement.
await env.DB.prepare("INSERT INTO foo VALUES('bar');\r\n");
// ✅ - runs as expected
await env.DB.prepare("INSERT INTO foo VALUES('bar');\n");

Oct 11 '23 16:10 KianNH

So I'm getting this error (only started recently) when running a d1 execute from my Windows machine (only when I run it on prod; local wrangler dev works fine). The file I'm running does not have CRLF line endings -- is this problem only for when the actual file contains CRLF, or is this just a bug with how wrangler is sending the file from Windows?

Nov 04 '23 19:11 radix

e.g.

PS C:\Users\radix\Projects\arpeggiorpg\worker> bash -c python3
Python 3.10.12 (main, Jun 11 2023, 05:26:28) [GCC 11.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.

>>> open('schema.sql', 'rb').read()
b'/* remember to include NOT NULL directives when creating new tables! */\n\n/* Also: Don\'t put semicolons in comments! Wrangler has a bug. */\n\nCREATE TABLE IF NOT EXISTS game_metadata (\n    game_id text PRIMARY KEY NOT NULL,\n    name text NOT NULL\n);\n\nCREATE TABLE IF NOT EXISTS superusers (\n    user_id text PRIMARY KEY NOT NULL\n);\n\nINSERT OR IGNORE INTO superusers (user_id) VALUES (\'google_105096330625444095578\');\n\n\nCREATE TABLE IF NOT EXISTS user_games (\n    user_id text NOT NULL,\n    game_id text NOT NULL,\n    role text NOT NULL,\n    profile_name text NOT NULL\n);\nCREATE INDEX IF NOT EXISTS user_games_by_user_idx ON user_games (user_id);\n\n\n/* This UNIQUE constraint ensures that each *user* can only be associated to a game once as a GM and\nonce as a player.\n\n(really, the only reason it includes "role" is for testing purposes. I don\'t think there are any\nreal use-cases for a user to be both a GM and a player at the same time)\n*/\nCREATE UNIQUE INDEX IF NOT EXISTS user_game_role_unique ON user_games(user_id, game_id, role);\n'

>>> b'\r' in open('schema.sql', 'rb').read()
False

PS C:\Users\radix\Projects\arpeggiorpg\worker> .\node_modules\.bin\wrangler d1 execute DB --file schema.sql                                                                             
🌀 Mapping SQL input into an array of statements
🌀 Parsing 6 statements
🌀 Executing on remote database DB (9bbbb737-cabc-44cb-bd76-44c3a5625759):
🌀 To execute on your local development database, pass the --local flag to 'wrangler d1 execute'

X [ERROR] A request to the Cloudflare API (/accounts/9da1987464827a8209f127a4e0f02b77/d1/database/9bbbb737-cabc-44cb-bd76-44c3a5625759/query) failed.

  A prepared SQL statement must contain only one statement. [code: 7500]

  If you think this is a bug, please open an issue at:
  https://github.com/cloudflare/workers-sdk/issues/new/choose

Nov 04 '23 19:11 radix