[P1][security] ConfirmDialog wired only for production-destructive SQL — designer/compare/backup have no confirm gate

[EVWorth]

Context

The whole app has one ConfirmDialog, hardwired to the SQL editor's production-destructive-query confirm. Other features that mutate data roll their own silent execution.

Problem

src/components/layout/AppLayout.tsx:294-306 mounts one <ConfirmDialog> wired only to useResultStore.confirmDialog:

<ConfirmDialog
  isOpen={!!confirmDialog?.isOpen}
  title="⚠️ Destructive Query on Production"
  message="You are about to run a destructive query (DROP, DELETE, TRUNCATE, or ALTER) on a PRODUCTION database..."
  confirmLabel="Execute Anyway"
  cancelLabel="Cancel"
  danger
  onConfirm={confirmExecution}
  onCancel={cancelExecution}
/>

Multiple features have their own silent execution paths with no confirm:

• Designer — [P0][bug] handleSave runs multi-statement ALTER as a single executeQuery call — partial apply, no transaction, no read-only, no production-safety confirm #379 (P0, multi-statement ALTER no transaction), [P0][security] CREATE mode Save button runs CREATE TABLE directly without preview — no confirmation, no diff against existing schema #380 (P0, CREATE mode runs without preview/confirmation), [P0][bug] extractBaseType/extractLength discard column modifiers — UNSIGNED, ZEROFILL, CHARACTER SET, COLLATE, ON UPDATE CURRENT_TIMESTAMP lost on round-trip #377 (P0, comment/engine/charset spurious emit).
• Compare — [P0][security] SyncPreview handleExecute runs each statement independently — no transaction, no escaping, no production safety, no read-only #369 (P0, SyncPreview handleExecute each statement independently, no transaction, no escaping, no read-only).
• Backup/Restore — [P0][doc-drift] ARCHITECTURE 3.6 promises mysqldump wrapper; implementation is pure-TS with no binary probe #357 (P0, no mysqldump wrapper — pure TS), [P0][bug] generateBackup buffers entire dump in single string; large DBs OOM renderer #358 (P0, OOM renderer), [P1][security] Restore runs statements serially with no transaction wrapper; mid-file errors leave DB half-populated #359 (P1, restore no transaction wrapper).
• Routine — [P0][security] RoutineViewer DROP routes through raw executeQuery with identifier string concat — no escape, no production-safety, no read-only #393 (P0, DROP routes through raw executeQuery), [P0][bug] executeProcedure runs USE + SET + CALL + SELECT @vars as multi-statement SQL — partial apply, identifier interpolation in CALL #394 (P0, executeProcedure multi-statement partial apply).
• Explain ANALYZE — [P0][bug] EXPLAIN ANALYZE runs the actual query (UPDATE/INSERT/DELETE) with no confirmation, no production-safety, no read-only enforcement #412 (P0, runs actual query with no confirmation).

The pattern is identical (destructive SQL, multi-statement, partial-apply on failure, no read_only gating) but the confirm is bypassed because they don't route through executeQuery via the resultStore. Each feature rolls its own dialog (or no dialog).

The cross-cutting audit (docs/audits/cross-cutting.md F11) flagged this as P1.

Files

• src/components/layout/AppLayout.tsx:294-306 — single ConfirmDialog wired only to resultStore
• src/components/common/ConfirmDialog.tsx:1-65 — primitive is generic but only used by AppLayout
• src/components/designer/TableDesigner.tsx (no confirm path at all)
• src/components/compare/SyncPreview.tsx (no confirm path at all)
• src/components/backup/BackupDialog.tsx + RestoreDialog.tsx (no confirm path at all)

Repro

1. Designer: open CREATE TABLE for users, set charset, click Save → DDL executes immediately.
2. Compare: select two schemas, click "Execute Sync" → all generated statements run in sequence without confirmation.
3. Backup: select "Drop existing database before restore" → restore proceeds without confirming the drop.

Expected

A single useConfirm() hook backed by a confirmStore. The dialog is mounted once at AppLayout and shows whichever pending confirm is highest-priority. Each feature calls await useConfirm({ title, message, danger, confirmLabel }) before executing.

Proposed fix

Scope M.

1. Make ConfirmDialog accept arbitrary title, message, confirmLabel, cancelLabel, danger, onConfirm, onCancel props (most already exist).
2. Introduce src/stores/confirmStore.ts with { pendingConfirm: { ... } | null, requestConfirm(opts) → Promise<boolean> }.
3. AppLayout renders <ConfirmDialog {...pendingConfirm} /> reading from the store.
4. Designer/Compare/Backup/Restore/Explain each call const ok = await useConfirmStore.getState().requestConfirm({ ... }); if (!ok) return; before executing.

Acceptance

Designer "Save" on a non-empty table without preview opens a single ConfirmDialog showing the DDL diff. Compare "Execute Sync" opens a confirm listing the destructive statements. Backup "Restore" opens a confirm with the source/target DB names. The same primitive is reused. Each feature's existing #issue closes (this is the unifying fix).

Needs human verify

Yes (UX flow needs Tauri runtime).

Labels: audit, area/cross-cutting, severity/p1, kind/security