Description

During a deep codebase audit of Querya-Desktop, several critical and latent issues were identified across data presentation, query generation, staging buffer lifecycle, and data export.

Issues to Resolve

1. [High Severity] Row Filtering Bypassed in Grid with StagingBuffer & Mutation Index Collision

• Files: lib/features/workspace/results_tab.dart, lib/features/workspace/result_grid_view.dart
• Bug: ResultsTab computes filteredRows = _getFilteredRows(effectiveRows, ...) and passes it to VirtualResultGrid(rows: filteredRows, stagingBuffer: stagingBuffer). However, VirtualResultGrid._baseRows directly returns widget.stagingBuffer?.effectiveRows ?? widget.rows. Thus, when a staging buffer is attached (PostgreSQL, MySQL, SQLite, extensions), the filter is completely ignored visually. Furthermore, if rows was used directly, editing row i of the filtered view would mutate row i of the base buffer instead of the mapped underlying row.
• Fix: Maintain an index mapping (_filteredIndices) for grid display and route mutations through mapped original row indices.

2. [High Severity] SQL Syntax Error / Escape Issue with Backslashes in MySQL DML Generation

• Files: lib/core/database/table_mutation_engine.dart
• Bug: Strings are escaped solely by replacing ' with ''. In MySQL (default SQL mode without NO_BACKSLASH_ESCAPES), the backslash \ is an active escape character. Strings containing Windows file paths or trailing backslashes (e.g. C:\Path\) cause \' to escape the closing quote, breaking SQL syntax or leading to syntax injection vulnerabilities.
• Fix: Check SqlDialect.mysql and escape backslashes with \\ prior to quoting.

3. [Medium Severity] Truncation of Leading Zeros in JSON Selection Export

• Files: lib/features/workspace/result_grid_view.dart (ResultGridSelection.toJson)
• Bug: toJson heuristically parses cell values using int.tryParse(val). Strings with leading zeros (e.g. "01234", "007", phone numbers) are coerced to integers (1234, 7), corrupting strings upon export.
• Fix: Preserve strings with leading zeros (e.g. ^0\d+$) as string literals.

4. [Medium Severity] TSV Export / Copy Delimiter Corruption on Newlines & Tabs

• Files: lib/features/workspace/result_grid_view.dart (ResultGridSelection.toTsv)
• Bug: TSV export joins cells with \t and lines with \n without RFC-4180 style quoting. Cells with internal newlines or tabs break column and row alignments when pasted into spreadsheet applications.
• Fix: Quote cells containing \t, \n, or " using standard TSV/CSV quoting rules.

5. [Medium Severity] StagingBuffer Memory Leak in SqlQueryTabSession

• Files: lib/features/workspace/sql_query_tab_session.dart
• Bug: SqlQueryTabSession.dispose() disposes controller and topFraction, but fails to call stagingBuffer?.dispose(). Attached listeners and buffer data remain in memory when tabs are closed.
• Fix: Ensure stagingBuffer?.dispose() is invoked in dispose().

Acceptance Criteria

• Row filtering works correctly even when a StagingBuffer is attached.
• Edits in filtered view accurately mutate the target row in the underlying buffer.
• MySQL DML generation properly handles backslashes (\).
• JSON export preserves strings with leading zeros.
• TSV export properly escapes cells with newlines and tabs.
• SqlQueryTabSession.dispose() cleanly disposes stagingBuffer.
• All unit and widget tests pass.