From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Jean Louis Newsgroups: gmane.emacs.devel Subject: Re: [NonGNU ELPA] New package: sqlite3 Date: Fri, 7 Apr 2023 08:17:40 +0300 Message-ID: References: <87cz5o6csk.fsf@bernoul.li> <87mt4swxsw.fsf@posteo.net> <875ybd7mbh.fsf@bernoul.li> <87y1nzb95o.fsf@posteo.net> <874jqddeow.fsf@logand.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="14725"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Mutt/2.2.9+54 (af2080d) (2022-11-21) Cc: Philip Kaludercic , Jonas Bernoulli , emacs-devel@gnu.org To: Tomas Hlavaty Original-X-From: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Fri Apr 07 07:21:06 2023 Return-path: Envelope-to: ged-emacs-devel@m.gmane-mx.org Original-Received: from lists.gnu.org ([209.51.188.17]) by ciao.gmane.io with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pkeX4-0003XJ-Ob for ged-emacs-devel@m.gmane-mx.org; Fri, 07 Apr 2023 07:21:06 +0200 Original-Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1pkeWK-0006bP-7C; Fri, 07 Apr 2023 01:20:20 -0400 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1pkeWI-0006b2-9u for emacs-devel@gnu.org; Fri, 07 Apr 2023 01:20:18 -0400 Original-Received: from stw1.rcdrun.com ([217.170.207.13]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1pkeWE-0003ly-UH for emacs-devel@gnu.org; Fri, 07 Apr 2023 01:20:18 -0400 Original-Received: from localhost ([::ffff:102.85.248.95]) (AUTH: PLAIN admin, TLS: TLS1.3,256bits,ECDHE_RSA_AES_256_GCM_SHA384) by stw1.rcdrun.com with ESMTPSA id 0000000000087CA9.00000000642FA811.00003ABA; Thu, 06 Apr 2023 22:20:16 -0700 Mail-Followup-To: Tomas Hlavaty , Philip Kaludercic , Jonas Bernoulli , emacs-devel@gnu.org Content-Disposition: inline In-Reply-To: <874jqddeow.fsf@logand.com> Received-SPF: pass client-ip=217.170.207.13; envelope-from=bugs@gnu.support; helo=stw1.rcdrun.com X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-devel@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "Emacs development discussions." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Original-Sender: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Xref: news.gmane.io gmane.emacs.devel:305153 Archived-At: * Tomas Hlavaty [2023-03-21 23:36]: > On Tue 21 Mar 2023 at 09:51, Jean Louis 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/