From mboxrd@z Thu Jan  1 00:00:00 1970
Received: from eggs.gnu.org ([2001:4830:134:3::10]:50921)
	by lists.gnu.org with esmtp (Exim 4.71)
	(envelope-from <Debian-debbugs@debbugs.gnu.org>) id 1ek5Lh-0008Tg-1p
	for guix-patches@gnu.org; Fri, 09 Feb 2018 04:52:06 -0500
Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71)
	(envelope-from <Debian-debbugs@debbugs.gnu.org>) id 1ek5Ld-0003bz-Rt
	for guix-patches@gnu.org; Fri, 09 Feb 2018 04:52:05 -0500
Received: from debbugs.gnu.org ([208.118.235.43]:54995)
	by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16)
	(Exim 4.71) (envelope-from <Debian-debbugs@debbugs.gnu.org>)
	id 1ek5Ld-0003bt-OH
	for guix-patches@gnu.org; Fri, 09 Feb 2018 04:52:01 -0500
Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2)
	(envelope-from <Debian-debbugs@debbugs.gnu.org>) id 1ek5Ld-00084K-Gg
	for guix-patches@gnu.org; Fri, 09 Feb 2018 04:52:01 -0500
Subject: [bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection.
Resent-Message-ID: <handler.30386.B30386.151816988730963@debbugs.gnu.org>
From: ludo@gnu.org (Ludovic =?UTF-8?Q?Court=C3=A8s?=)
References: <20180207231258.31169-1-dannym@scratchpost.org>
	<20180208163432.9468-1-dannym@scratchpost.org>
Date: Fri, 09 Feb 2018 10:51:23 +0100
In-Reply-To: <20180208163432.9468-1-dannym@scratchpost.org> (Danny
	Milosavljevic's message of "Thu, 8 Feb 2018 17:34:32 +0100")
Message-ID: <87r2pu4hk4.fsf@gnu.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
List-Id: <guix-patches.gnu.org>
List-Unsubscribe: <https://lists.gnu.org/mailman/options/guix-patches>,
	<mailto:guix-patches-request@gnu.org?subject=unsubscribe>
List-Archive: <http://lists.gnu.org/archive/html/guix-patches/>
List-Post: <mailto:guix-patches@gnu.org>
List-Help: <mailto:guix-patches-request@gnu.org?subject=help>
List-Subscribe: <https://lists.gnu.org/mailman/listinfo/guix-patches>,
	<mailto:guix-patches-request@gnu.org?subject=subscribe>
Errors-To: guix-patches-bounces+kyle=kyleam.com@gnu.org
Sender: "Guix-patches" <guix-patches-bounces+kyle=kyleam.com@gnu.org>
To: Danny Milosavljevic <dannym@scratchpost.org>
Cc: 30386@debbugs.gnu.org

Hi Danny,

Apologies for not noticing your message earlier!  I was head-down trying
to get this thing in shape and wasn=E2=80=99t checking for email.  I=E2=80=
=99ll do
better now on, especially since you know way better than me how to deal
with these database issues.  :-)

Danny Milosavljevic <dannym@scratchpost.org> skribis:

> +         (stmt-text (format #f "\
> +SELECT Builds.id, Builds.timestamp, Builds.starttime, Builds.stoptime, B=
uilds.log, Builds.status, Builds.derivation,\
> +Derivations.job_name, Derivations.system, Derivations.nix_name,\
> +Specifications.repo_name, Specifications.branch \
> +FROM Builds \
> +INNER JOIN Derivations ON Builds.derivation =3D Derivations.derivation A=
ND Builds.evaluation =3D Derivations.evaluation \
> +INNER JOIN Evaluations ON Derivations.evaluation =3D Evaluations.id \
> +INNER JOIN Specifications ON Evaluations.specification =3D Specification=
s.repo_name \
> +WHERE (:id IS NULL OR (:id =3D Builds.id)) \
> +OR (:project IS NULL OR (:project =3D Specifications.repo_name)) \
> +OR (:jobset IS NULL OR (:jobset =3D Specifications.branch)) \
> +OR (:job IS NULL OR (:job =3D Derivations.job_name)) \
> +OR (:system IS NULL OR (:system =3D Derivations.system)) \
> +OR (:status IS NULL OR (:status =3D 'done' AND Builds.status >=3D 0) OR =
(:status =3D 'pending' AND Builds.status < 0))

Do you think we can salvage this bit from your patch?  The NULL
optimization looks good, provided the extra conditions don=E2=80=99t make s=
qlite
slower.  It might allow us to use =E2=80=98sqlite-exec=E2=80=99 directly, a=
nd thus
benefit from the binding support in there, as in:

  (sqlite-exec db "=E2=80=A6 WHERE " id " is NULL or =E2=80=A6")

Thoughts?

Thanks!

Ludo=E2=80=99.