Skip to content

Write proper SQL literals when copying or dragging a table from DB Schema - #4199

Open
anandghegde wants to merge 1 commit into
sqlitebrowser:masterfrom
anandghegde:fix-schema-copy-sql-values
Open

anandghegde wants to merge 1 commit into
sqlitebrowser:masterfrom
anandghegde:fix-schema-copy-sql-values

Conversation

@anandghegde

Copy link
Copy Markdown

Copying a table from the DB Schema pane (right-click → Copy, or ⌘C/Ctrl+C), or dragging it onto another database, generates a CREATE TABLE statement plus one INSERT per row. DbStructureModel::mimeData() builds those INSERTs by wrapping every value in single quotes without escaping it:

insertStatement += QString("'%1',").arg(tableModel.data(...).toString());

This PR writes each value as a proper SQL literal, the same way "Copy as SQL" in the table grid (ExtendedTableWidget::copyMimeData) already does:

  • NULL → NULL
  • BLOB → X'…'
  • integer/real values unquoted
  • text through sqlb::escapeString

Reproduction

CREATE TABLE people(id INTEGER PRIMARY KEY, name TEXT, nickname TEXT, score REAL, avatar BLOB);
INSERT INTO people(name, nickname, score, avatar) VALUES ('O''Brien', NULL, 9.5, X'00FF10'), ('Ann', 'annie', NULL, NULL);

DB Schema → right-click people → Copy:

Before

INSERT INTO "main"."people" VALUES('1','O'Brien','','9.5','');
INSERT INTO "main"."people" VALUES('2','Ann','annie','','');
  • The first statement is a syntax error (near "Brien"), so the pasted SQL fails, and so does dropping the table onto another database, which runs the same SQL.
  • NULLs become ''.
  • The BLOB is lost.
  • Numbers become strings.

After

INSERT INTO "main"."people" VALUES(1,'O''Brien',NULL,9.5,X'00ff10');
INSERT INTO "main"."people" VALUES(2,'Ann','annie',NULL,NULL);

Test plan

  • Built on macOS (Apple Silicon, Qt 5.15.16 from the project tap) with -DENABLE_TESTING=ON; ctest passes (4/4).
  • Copied the table above from the DB Schema pane in a build of master and in a build with this change, then ran each clipboard result with sqlite3 -bail against a new database:
    • Before: fails with a parse error on the first INSERT.
    • After: runs cleanly. id, name, quote(nickname), quote(score), quote(avatar), typeof(id), typeof(score) is identical to the source table.
  • The drop path runs this same generated SQL through dropMimeData(), so it gets the same fix.

Separately: like the SQL export in #4198, these INSERTs also include generated columns, so a table that has them still can't be copied this way. I've left that out of this PR to keep it to the value literals.

…hema

Copying a table from the DB Schema pane, or dragging it onto another
database, builds INSERT statements by wrapping every value in single
quotes without escaping it. As a result:

- a value containing a quote, e.g. O'Brien, produces invalid SQL and the
  copy or drop fails
- NULL values become empty strings
- BLOB values are lost (written as '')
- numbers are written as strings

Write each value the way "Copy as SQL" in the table grid already does:
NULL as NULL, BLOBs as X'..' literals, numbers unquoted and text escaped.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant