From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.org!.POSTED!not-for-mail From: Christopher Allan Webber Newsgroups: gmane.lisp.guile.user Subject: Re: Prevent SQL Injection in DBI Date: Mon, 27 Mar 2017 13:24:47 -0500 Message-ID: <8760iud1cg.fsf@dustycloud.org> References: <20170326185427.32f0f832@jcubic> NNTP-Posting-Host: blaine.gmane.org Mime-Version: 1.0 Content-Type: text/plain X-Trace: blaine.gmane.org 1490639126 14065 195.159.176.226 (27 Mar 2017 18:25:26 GMT) X-Complaints-To: usenet@blaine.gmane.org NNTP-Posting-Date: Mon, 27 Mar 2017 18:25:26 +0000 (UTC) User-Agent: mu4e 0.9.18; emacs 25.1.1 Cc: guile-user@gnu.org To: Jakub Jankiewicz Original-X-From: guile-user-bounces+guile-user=m.gmane.org@gnu.org Mon Mar 27 20:25:22 2017 Return-path: Envelope-to: guile-user@m.gmane.org Original-Received: from lists.gnu.org ([208.118.235.17]) by blaine.gmane.org with esmtp (Exim 4.84_2) (envelope-from ) id 1csZKT-0003E3-5R for guile-user@m.gmane.org; Mon, 27 Mar 2017 20:25:21 +0200 Original-Received: from localhost ([::1]:48442 helo=lists.gnu.org) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1csZKZ-0001zX-79 for guile-user@m.gmane.org; Mon, 27 Mar 2017 14:25:27 -0400 Original-Received: from eggs.gnu.org ([2001:4830:134:3::10]:39270) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1csZK2-0001yO-H9 for guile-user@gnu.org; Mon, 27 Mar 2017 14:24:55 -0400 Original-Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1csZJy-0004Cb-J7 for guile-user@gnu.org; Mon, 27 Mar 2017 14:24:54 -0400 Original-Received: from dustycloud.org ([50.116.34.160]:59524) by eggs.gnu.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1csZJy-0004CX-Et for guile-user@gnu.org; Mon, 27 Mar 2017 14:24:50 -0400 Original-Received: from oolong (localhost [127.0.0.1]) by dustycloud.org (Postfix) with ESMTPS id 812D3265E1; Mon, 27 Mar 2017 14:24:48 -0400 (EDT) In-reply-to: <20170326185427.32f0f832@jcubic> X-detected-operating-system: by eggs.gnu.org: GNU/Linux 2.2.x-3.x [generic] [fuzzy] X-Received-From: 50.116.34.160 X-BeenThere: guile-user@gnu.org X-Mailman-Version: 2.1.21 Precedence: list List-Id: General Guile related discussions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guile-user-bounces+guile-user=m.gmane.org@gnu.org Original-Sender: "guile-user" Xref: news.gmane.org gmane.lisp.guile.user:13582 Archived-At: Jakub Jankiewicz writes: > Hi all, > > I want to use guile-dbi with unsafe user input. I have code like this: > > (dbi-query db-obj (string-append "SELECT * FROM users WHERE username = '" > username > "'")) > > How can I escape username given from user to prevent sql injection? > > I could validate username to only contain letters using [a-zA-Z] regex but > what about other languages that have non Latin letters and names like O'Conor? > This will also don't work for password that may have special characters. guile-squee is pretty immature but I do think it does this one bit correctly... parameters to the procedure shouldn't be substituted in the string, they should be provided as additional arguments, like (exec-query db-obj "SELECT * FROM users WHERE username=$1" '("alice")) I seem to remember trying to find out if this was possible to do in dbi-query, and not succeeding at finding such a route? (guile-dbi, unlike guile-squee, has some maturity though...)