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.help Subject: Re: Printf and quoting in general, SQL injection in particular Date: Tue, 22 Jun 2021 03:52:24 +0300 Message-ID: References: <87pmwgdiyj.fsf@zoho.eu> <83y2b3tq07.fsf@gnu.org> <871r8vcrnm.fsf@posteo.net> <20210621141148.GA29347@tuxteam.de> <87zgvjcgh6.fsf@zoho.eu> <87h7hqviu4.fsf@zoho.eu> 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="9526"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Mutt/2.0.7+183 (3d24855) (2021-05-28) To: help-gnu-emacs@gnu.org Original-X-From: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Tue Jun 22 02:56:41 2021 Return-path: Envelope-to: geh-help-gnu-emacs@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 1lvUiX-0002IB-7j for geh-help-gnu-emacs@m.gmane-mx.org; Tue, 22 Jun 2021 02:56:41 +0200 Original-Received: from localhost ([::1]:39906 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1lvUiW-0002qg-9p for geh-help-gnu-emacs@m.gmane-mx.org; Mon, 21 Jun 2021 20:56:40 -0400 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]:56614) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1lvUi8-0002qH-P8 for help-gnu-emacs@gnu.org; Mon, 21 Jun 2021 20:56:17 -0400 Original-Received: from stw1.rcdrun.com ([217.170.207.13]:50265) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1lvUi7-0001Gg-3m for help-gnu-emacs@gnu.org; Mon, 21 Jun 2021 20:56:16 -0400 Original-Received: from localhost ([::ffff:197.157.0.61]) (AUTH: PLAIN admin, TLS: TLS1.3,256bits,ECDHE_RSA_AES_256_GCM_SHA384) by stw1.rcdrun.com with ESMTPSA id 0000000000075C65.0000000060D1352C.000023D0; Mon, 21 Jun 2021 17:56:12 -0700 Mail-Followup-To: help-gnu-emacs@gnu.org Content-Disposition: inline In-Reply-To: <87h7hqviu4.fsf@zoho.eu> Received-SPF: pass client-ip=217.170.207.13; envelope-from=bugs@gnu.support; helo=stw1.rcdrun.com X-Spam_score_int: -3 X-Spam_score: -0.4 X-Spam_bar: / X-Spam_report: (-0.4 / 5.0 requ) BAYES_00=-1.9, RCVD_IN_SORBS_WEB=1.5, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=no autolearn_force=no X-Spam_action: no action X-BeenThere: help-gnu-emacs@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: Users list for the GNU Emacs text editor List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Original-Sender: "help-gnu-emacs" Xref: news.gmane.io gmane.emacs.help:131176 Archived-At: * Emanuel Berg via Users list for the GNU Emacs text editor [2021-06-22 03:35]: > > I am thinking how can I make it safer for SQL queries. > > SQL injection isn't avoided by not assembling queries with > string functions but by quoting user input. It is impossible in `emacs-libpq' package to avoid formatting strings and passing it to database. What is possible is to minimize it so that users' input is automatically quoted by the database by passing it as parameters instead of passing data as parameters to `format'. I prefer the latter. There is less code. I have improved after Thomas's suggestions. Now I am preparings statements: (defun rcd-db-prepare-statement (name prepared pg) (unless (rcd-sql-first "SELECT statement FROM pg_prepared_statements WHERE name = $1" pg name) (rcd-sql prepared pg))) (defun rcd-db-prepare-statements () (rcd-db-prepare-statement "persons_emails" "PREPARE persons_emails(int) AS SELECT DISTINCT unnest(array[people_email1, people_email2, people_email3] || people_emailsobsolete) FROM people WHERE people_id = $1")) Now function is small and nice: (defun cf-emails-by-id (id) "Returns list of emails for contact ID" (delq nil (pq:query cf-db (format "EXECUTE persons_emails(%s)" id)))) and it was this big and without true necessity complex: (defun cf-emails-by-id (id) "Returns list of emails for contact ID" (let* ((sql (format "SELECT people_email1, people_email2, people_email3 FROM people WHERE people_id = %s" id)) (emails (rcd-sql-first sql cf-db)) (obsolete-emails (rcd-db-array-value-as-list "people" "people_emailsobsolete" id cf-db)) (emails (append emails obsolete-emails)) (emails (seq-remove 'seq-empty-p emails)) (emails (mapcar (lambda (e) (when (string-match "@" e) e)) emails)) (emails (remove nil emails))) emails)) -- Jean Take action in Free Software Foundation campaigns: https://www.fsf.org/campaigns In support of Richard M. Stallman https://stallmansupport.org/