Re: [NonGNU ELPA] New package: sqlite3

[Jean Louis <bugs@gnu.support>]

* Tomas Hlavaty <tom@logand.com> [2023-03-22 00:03]:
> The idea is that one should not concatenate strings by hand but one
> should write the query as sexp (likely build that cons tree using quote
> or backquote).  That cons tree should then be converted to string by a
> lisp function.  Only after that should the string be passed to sqlite.
> 
>    sexp (cons tree) -> string -> sqlite

I have got 2027 SELECT statements, 463 INSERT statements, and 81
UPDATE statements in Emacs Lisp, mostly for PostgreSQL, little less
for SQLite3.

There are many string concatenations, and I never had a visual problem
with it. Maybe it depends on various styles of programming.

Example:
--------

(defun rcd-db-words-list-translations (&optional id)
  "List translations of the table `words'"
  (interactive)
  (when-tabulated-id "words"
      (let ((sql (format "SELECT translations_id, languages_name, words_value, translations_translation FROM translations, languages, words WHERE words_id = translations_words AND languages_id = translations_languages AND translations_words = %s" id)))
	(rcd-db-sql-report "Translations" sql [("ID" 4 t) ("Language" 30 t) ("Words" 30 t) ("Translations" 30 t)] "translations" nil 'rcd-db-words-list-translations))))

In the above case there is main function `rcd-db-sql-report' which
only accepts SQL. I spare writing code by using single function to
handle reports. If I would make that function accept "carefully" the
parameters, that would mean that for every of thousands of SELECT
statements I would need to make little different function, and would
waste terribly my time. 

I do not find writing SQL queries as s-exp practically useful, as that
causes programmer NOT to have the SQL statement at hand to verify or
debug the SQL query. In that case I would need to wait for program to
construct SQL, to show me somewhere, that I can try it out.

Otherwise, I use SQL first, then enter it in the Emacs Lisp. Isn't
that more practical to try SQL first until satisifed and then use it?

Back to above function, the macro `when-tabulated-id "words"' passes
the ID only as number. It is checking if user is in the table "words"
and then ID can be taken only from tabulated list ID, and due to
program style in general, there is no other ID but number ID. Nothing
can happen.

Of course, user could modify the buffer of Emacs and "inject" the
dangerous ID there, and thus dangerous SQL possibly. While one can say
those are Emacs weaknesses, for me those are features. That user is
able to modify everything within Emacs, including the code, this fact
alone cannot make SQL queries "safer" by using exlusively s-exp for
SQL. Then I can also modify that SQL and inject anyway dangerous SQL,
analogous typographic and computer errors can take place with s-exp or
without.

IMHO, examples discussed are way too hypothetical without single
real world problem reported so far.

The difference is that with SQL as strings, one can see the SQL
practically, and construct it and place in Emacs Lisp freely, without
double work to re-structure it into s-exp.

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/