Re: [NonGNU ELPA] New package: sqlite3

[Jean Louis <bugs@gnu.support>]

* Tomas Hlavaty <tom@logand.com> [2023-03-21 23:36]:
> On Tue 21 Mar 2023 at 09:51, Jean Louis <bugs@gnu.support> wrote:
> > While such packages exists, for me I do not find them usable as then I
> > have to forget about the SQL and learn about the new Emacs Lisp
> > structure that is to correspond to SQL. I see personally no benefit in
> > that.
> [...]
> > (sql (format "SELECT DISTINCT ON (people_id) 
>         ^
>         sql injection danger right there
> 
> Using strings is a bad idea.  You have to manually ensure every such use
> is properly escaped.  If you used sexp and let elisp do the conversion,
> every such usage could be automatically properly escaped.
> 
> Also using sexp does not mean you cannot use plain sql:
> 
>    (sql `(SELECT DISTINCT ON (people_id)

"SQL injection right there", booh, scare, terror...

You are exaggerating using single fact that "SQL injections are
security issue", while forgetting the other fact that "Emacs security
is weak by default".

If we would speak of web server programming for public where input
information is restricted to HTTP channel, then I would say that SQL
injections are security issue and programming style shall change.

If we speak of web server programming with team of trained and trusted
staff members, then SQL injection is not an issue, as they could
"inject" much more dangerous harm into the group than the singe
SQL. It matters not. Such people could destroy computers, hard disks,
and anything so much more. And if any of them is really a hidden
psychopath that does "only" less harmful SQL injections in the group
that feeds him, then this will remain in the logs and will be found
who was it.

If we speak of Emacs interface instead of "public input through HTTP"
interface, then talking about SQL injections is pointless, as user can
anyway inject Emacs Lisp, just anything through Emacs interface.

If you wish to keep scaring people on this mailing list, explain them
all the factors for informed decision making.

If you wish to say how you learn about SQL injection, great, but then
remember to tell people that such securit warning applies rather for
web (HTTP) or similar interfaces where program is exposed to public.

It doesn't apply for Emacs text editor interface, because you can
fricking inject anything you want with M-: and by talking how `format'
is dangerous with the SQL query, and "please avoid SQL injections" you
are only introducing unreasonable fears, and will make some people
believe that "using strings to construct SQL is wrong", which is can't
be said so decisively.

In addition, there are many cases where neither PostgreSQL or SQLite3
will allow native interpolation of attributes.

For example I do not believe that following could be replaced with
native interpolation:

(defun rcd-db-table-oid (table pg &optional schema)
  "Return the TABLE OID for database handle PG.

Use optional SCHEMA."
  (let* ((schema (or schema "public"))
	 (sql (format "SELECT '%s.%s'::regclass::oid" schema table)))
    (rcd-sql-first sql pg)))

Neither this one:

(defun rcd-db-table-update-comment (table comment pg)
  "Update TABLE with COMMENT for handle PG."
  (rcd-sql (format "COMMENT ON TABLE %s IS %s" table (sql-escape-string comment)) pg))

This one for sure cannot be replaced with native interpolation as it
requires all the tables to have TABLE_id column as dictated by GeDaFe design.

GeDaFe - PostgreSQL Generic Database Interface:
http://gedafe.github.io/doc/gedafe-sql.en.html 

(defun rcd-db-table-last-id (table pg)
  "Return the last entry ID for the TABLE with database handle PG."
  (rcd-sql-first (format "SELECT %s_id FROM %s ORDER BY %s_id DESC LIMIT 1" table table table) pg))

The design then let me get any value from any column just by using
following, for example to get last name or name of company, I can use
following:

(rcd-db-get-entry "people" "people_name" 1 cf-db)

which uses string interpolation in background:

(defun rcd-db-get-entry (table column id pg)
  "Return value for the COLUMN from the RCD Database by using the entrxy ID.
Argument TABLE is database table."
  (if id
      (let* ((sql (format "SELECT %s FROM %s WHERE %s_id = %s" column table table id))
	     (value (rcd-sql-first sql pg)))
      value)
    (error "Function `rcd-db-get-entry': ID not conveyed")))

Summary is that no programmer who uses Emacs Lisp need to worry about
the SQL injection, but about the people who are using the program.

More about SQL injection:
https://en.wikipedia.org/wiki/SQL_injection

Programmers who use Emacs Lisp to accept public input, from unknown
people through Internet web HTTP interface, should worry about SQL
injection and in that case try their best to verify the input. Even in
that case there is no need to blame using strings or `format' when
constructing SQL, but programmer who doesn't understand the input, who
neglected to verify that input is what it is supposed to be.


Jean

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

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