Subject: [PATCH] Quote identifiers in SQL queries in sqlite-mode

From: [email-masked]
Date: Wed, 15 Jan 2025 18:38:51 +0000

Tags: patch

The sqlite-mode package does not quote identifiers (table and column
names) in the SQL queries it performs. As a result, it e.g. cannot open
databases which have a table called "values".

Here is a patch which fixes it for me.

Reference: https://www.sqlite.org/lang_keywords.html

In GNU Emacs 29.1 (build 1, x86_64-pc-linux-gnu, X toolkit, cairo
version 1.18.0, Xaw3d scroll bars)
Windowing system distributor 'The X.Org Foundation', version 11.0.12101015
System Description: Arch Linux

Configured using:
 'configure
 --prefix=/nix/store/mzdvpxzkhlryxmbxwbih7bgvkzkw1ly7-emacs-29.1
 --disable-build-details --with-modules --with-x-toolkit=lucid
 --with-xft --with-cairo --with-compress-install
 --with-toolkit-scroll-bars --with-native-compilation
 --without-imagemagick --without-small-ja-dic --with-tree-sitter
 --with-xinput2 --without-xwidgets'

>From 7f85812ae501cbf4e321717e7bc7319846c76fcf Mon Sep 17 00:00:00 2001
From: Vladimir Panteleev <[email-masked]>
Date: Wed, 15 Jan 2025 18:33:23 +0000
Subject: [PATCH] Quote identifiers in SQL queries in sqlite-mode

* lisp/sqlite-mode.el: Quote identifiers (table and column names) in the
SQL queries. Fixes e.g. opening databases which have a table called
"values".
---
 lisp/sqlite-mode.el | 10 +++++-----
 1 file changed, 5 insertions(+), 5 deletions(-)

diff --git a/lisp/sqlite-mode.el b/lisp/sqlite-mode.el
index 5deb8c2d7bb..a4b96b02b48 100644
--- a/lisp/sqlite-mode.el
+++ b/lisp/sqlite-mode.el
@@ -76,7 +76,7 @@ sqlite-mode-list-tables
     (erase-buffer)
     (dolist (table (sqlite-select db "select name from sqlite_master where type = 'table' and name not like 'sqlite_%' order by name"))
       (push (list (car table)
-                  (caar (sqlite-select db (format "select count(*) from %s"
+                  (caar (sqlite-select db (format "select count(*) from \"%s\""
                                                   (car table)))))
             entries))
     (sqlite-mode--tablify '("Table Name" "Number of Rows")
@@ -137,7 +137,7 @@ sqlite-mode-list-columns
 
 (defun sqlite-mode--column-names (table)
   "Return a list of the column names for TABLE."
-  (mapcar (lambda (row) (nth 1 row)) (sqlite-select sqlite--db (format "pragma table_info(%s)" table))))
+  (mapcar (lambda (row) (nth 1 row)) (sqlite-select sqlite--db (format "pragma table_info(\"%s\")" table))))
 
 (defun sqlite-mode-list-data ()
   "List the data from the table under point."
@@ -171,7 +171,7 @@ sqlite--mode--list-data
           (setq stmt
                 (sqlite-select
                  sqlite--db
-                 (format "select rowid, * from %s where rowid >= ?" table)
+                 (format "select rowid, * from \"%s\" where rowid >= ?" table)
                  (list rowid)
                  'set))
           (sqlite-mode--tablify (sqlite-columns stmt)
@@ -201,11 +201,11 @@ sqlite-mode-delete
       (user-error "Not deleting"))
     (sqlite-execute
      sqlite--db
-     (format "delete from %s where %s"
+     (format "delete from \"%s\" where %s"
              (cdr table)
              (string-join
               (mapcar (lambda (column)
-                        (format "%s = ?" (car (split-string column " "))))
+                        (format "\"%s\" = ?" (car (split-string column " "))))
                       (cons "rowid" (sqlite-mode--column-names (cdr table))))
               " and "))
      row)
-- 
2.46.0