chore: Revert "feat: CLI: Native DuckDB file loading (#409)" (#470)

ilyabo · web-flow · commit c0de1db9e732 · 2026-03-16T10:41:46.000+01:00
Signed-off-by: Ilya Boyandin &lt;ilyabo@gmail.com&gt;
diff --git a/apps/sqlrooms-cli-ui/src/serverApi.ts b/apps/sqlrooms-cli-ui/src/serverApi.ts
@@ -1,4 +1,5 @@
 import {PersistStorage, StorageValue} from 'zustand/middleware';
+import {RuntimeConfig} from './runtimeConfig';
 
 type DuckDbLikeConnector = {
   query: (sql: string) => PromiseLike<any>;
@@ -139,3 +140,51 @@ export function createDuckDbPersistStorage(
     },
   };
 }
+
+function getApiBaseUrl(config: RuntimeConfig): string {
+  return (config.apiBaseUrl || '').replace(/\/$/, '');
+}
+
+const SAFE_PATH_RE = /^[A-Za-z0-9_\-./:\\]+$/;
+
+/**
+ * Validate and sanitize a server-returned file path to prevent SQL injection
+ * when the path is later interpolated into DuckDB queries
+ * (e.g. `read_ipc('${filePath}')`).
+ */
+function validateServerPath(raw: unknown): string {
+  if (typeof raw !== 'string' || raw.length === 0) {
+    throw new Error('Server returned an invalid upload path (not a string)');
+  }
+
+  const normalized = raw.replace(/\\/g, '/');
+
+  if (!SAFE_PATH_RE.test(normalized)) {
+    throw new Error(
+      `Server returned an upload path with disallowed characters: ${normalized}`,
+    );
+  }
+
+  if (normalized.includes('..')) {
+    throw new Error(
+      `Server returned an upload path with directory traversal: ${normalized}`,
+    );
+  }
+
+  return normalized;
+}
+
+export async function uploadFileToServer(
+  file: File,
+  config: RuntimeConfig,
+): Promise<string> {
+  const uploadUrl = `${getApiBaseUrl(config)}/api/upload`;
+  const form = new FormData();
+  form.append('file', file, file.name);
+  const res = await fetch(uploadUrl, {method: 'POST', body: form});
+  if (!res.ok) {
+    throw new Error(`Upload failed: ${res.statusText}`);
+  }
+  const data = (await res.json()) as {path: string};
+  return validateServerPath(data.path);
+}
diff --git a/apps/sqlrooms-cli-ui/src/store.ts b/apps/sqlrooms-cli-ui/src/store.ts
@@ -51,7 +51,7 @@ import {createHttpDbBridge, DbConnection} from '@sqlrooms/db';
 import {getDefaultScaffoldTree} from './helpers';
 import {LAYOUT} from './layout';
 import {fetchRuntimeConfig} from './runtimeConfig';
-import {createDuckDbPersistStorage} from './serverApi';
+import {createDuckDbPersistStorage, uploadFileToServer} from './serverApi';
 
 export const AppBuilderProjectConfig = z.object({
   appsBySheetId: z
@@ -111,20 +111,12 @@ const connector = createWebSocketDuckDbConnector({
   wsUrl: runtimeConfig.wsUrl || 'ws://localhost:4000',
 });
 
-type FileWithNativePath = File & {path?: string};
-
-function getCliFileReference(file: File): string {
-  const nativePath = (file as FileWithNativePath).path;
-  if (typeof nativePath === 'string' && nativePath.trim()) {
-    return nativePath;
-  }
-  return file.name;
-}
-
 const baseLoadFile = connector.loadFile.bind(connector);
 connector.loadFile = async (file, desiredTableName, options) => {
   if (file instanceof File) {
-    return baseLoadFile(getCliFileReference(file), desiredTableName, options);
+    const serverPath = await uploadFileToServer(file, runtimeConfig);
+    const renamedFile = new File([file], serverPath, {type: file.type});
+    return baseLoadFile(renamedFile, desiredTableName, options);
   }
   return baseLoadFile(file, desiredTableName, options);
 };
diff --git a/python/sqlrooms-cli/README.md b/python/sqlrooms-cli/README.md
@@ -16,7 +16,7 @@ What happens:
 
 - Starts the DuckDB websocket backend (from `sqlrooms-server`) on `ws://localhost:4000`.
 - Serves the AI example UI on `http://localhost:4173` and opens your browser (disable with `--no-open-browser`).
-- Drag-and-drop CSV/Parquet/DuckDB files to load them into DuckDB directly from your filesystem using the dropped file reference (no upload copy).
+- Drag-and-drop CSV/Parquet/DuckDB files to load them into DuckDB; files are uploaded to a local `sqlrooms_uploads` folder and referenced by path.
 - UI state is stored in the SQLRooms meta namespace (default `__sqlrooms`) of the selected DuckDB file.
 
 ## CLI flags
@@ -40,7 +40,7 @@ Tables created in the selected DuckDB file (or attached meta DB if `--meta-db` i
 - `__sqlrooms.ui_state` (one row: `key='default'`)
 - `__sqlrooms.sync_rooms` (only used when `--sync` is enabled)
 
-Runtime config for the UI is exposed at `/api/config` / `/config.json`.
+Uploads go to `/api/upload`. Runtime config for the UI is exposed at `/api/config` / `/config.json`.
 
 ## Config file
 
