Fix multiple sql command processing

[findolor]

Currently when we pass a string that has multiple sql commands it only executes the first one and drops the rest. This is a bug that needs to be fixed.

[Jatkingmodern]

Likely root causes

The API is passing the SQL string to a driver method that only executes the first statement (some drivers expose separate exec vs run semantics).

The implementation attempts to split statements on ; using a naive split(';'), which fails for semicolons inside strings/comments and may discard empty fragments.

The library never iterates over split statements or returns early after the first execute call.

Proposed fix (implementation checklist)

Decide desired behavior: support multi-statement SQL strings (recommended), or explicitly reject them with a clear error (if you prefer callers to pass statements separately).

Prefer using driver-native multi-statement execution when available and safe:

Some drivers have a multiStatements option (MySQL) or an exec that accepts full SQL with multiple statements (SQLite).

If the driver supports it securely, use that — but ensure parameterization still works.

If native support is not available, implement a safe statement-splitting + execution loop:

Use a robust splitter that ignores semicolons inside single/double/backtick quotes and comments (both -- and /* ... */), not a naive split(';').

Execute each non-empty statement in order inside a single transaction (so either all statements apply or none do).

Add tests:

Unit tests covering: normal multi-statement strings; semicolons in strings; SQL comments; empty statements and trailing semicolons; error during a later statement should roll back earlier statements.

Add logging / error behavior:

If execution fails mid-way, return an informative error and ensure rollback.

Document behavior in README / API docs (e.g., "this API accepts multi-statement SQL strings; statements are executed sequentially in a transaction").

Release note: mark as bugfix; add migration note if behavior changed.// safeSplitSql.js
function safeSplitSql(sql) {
const parts = [];
let buf = '';
let inSingle = false;
let inDouble = false;
let inBacktick = false;
let inLineComment = false;
let inBlockComment = false;
for (let i = 0; i < sql.length; i++) {
const ch = sql[i];
const next = sql[i + 1];

// handle line comments: -- ... \n
if (!inSingle && !inDouble && !inBacktick && !inBlockComment && ch === '-' && next === '-') {
  inLineComment = true;
  buf += ch;
  continue;
}
if (inLineComment) {
  buf += ch;
  if (ch === '\n') inLineComment = false;
  continue;
}

// handle block comments: /* ... */
if (!inSingle && !inDouble && !inBacktick && !inLineComment && ch === '/' && next === '*') {
  inBlockComment = true;
  buf += ch;
  continue;
}
if (inBlockComment) {
  buf += ch;
  if (ch === '*' && next === '/') {
    // include '/' on next iteration
    continue;
  }
  if (ch === '/' && sql[i - 1] === '*') {
    inBlockComment = false;
  }
  continue;
}

// toggle quote states (handle escapes like \' and \" and `` if driver supports)
if (!inDouble && !inBacktick && ch === "'" ) { inSingle = !inSingle; buf += ch; continue; }
if (!inSingle && !inBacktick && ch === '"' ) { inDouble = !inDouble; buf += ch; continue; }
if (!inSingle && !inDouble && ch === '`' ) { inBacktick = !inBacktick; buf += ch; continue; }

// semicolon handling only when not inside quotes/comments
if (!inSingle && !inDouble && !inBacktick && !inLineComment && !inBlockComment && ch === ';') {
  const piece = buf.trim();
  if (piece) parts.push(piece);
  buf = '';
  continue; // drop the semicolon
}

buf += ch;

}
if (buf.trim()) parts.push(buf.trim());
return parts;
}
// executor.js (example for a generic DB client with begin/commit/rollback + run/exec)
async function executeMultiStatement(dbClient, sql) {
const statements = safeSplitSql(sql);
if (statements.length === 0) return;

// begin transaction if supported
await dbClient.query('BEGIN'); // adapt to your driver: db.begin() etc.
try {
for (const stmt of statements) {
// prefer a driver method that accepts raw SQL; adapt args if you need parameterization
await dbClient.query(stmt);
}
await dbClient.query('COMMIT');
} catch (err) {
await dbClient.query('ROLLBACK');
throw err;
}
}
Notes on the above approach

This safe-splitter covers basic quoting and comments; for full SQL dialect coverage (dollar-quoted strings in Postgres, nested quotes, procedural language blocks) consider using a SQL parser library or enabling the driver's multi-statement mode if available.

Running statements inside a transaction ensures atomicity (recommended).