From unknown Fri Aug 15 02:02:07 2025 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Mailer: MIME-tools 5.509 (Entity 5.509) Content-Type: text/plain; charset=utf-8 From: bug#30386 <30386@debbugs.gnu.org> To: bug#30386 <30386@debbugs.gnu.org> Subject: Status: [PATCH cuirass] database: Prevent SQL injection. Reply-To: bug#30386 <30386@debbugs.gnu.org> Date: Fri, 15 Aug 2025 09:02:07 +0000 retitle 30386 [PATCH cuirass] database: Prevent SQL injection. reassign 30386 guix-patches submitter 30386 Danny Milosavljevic severity 30386 normal tag 30386 patch thanks From debbugs-submit-bounces@debbugs.gnu.org Wed Feb 07 18:13:33 2018 Received: (at submit) by debbugs.gnu.org; 7 Feb 2018 23:13:33 +0000 Received: from localhost ([127.0.0.1]:33575 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ejYuD-0006iG-7D for submit@debbugs.gnu.org; Wed, 07 Feb 2018 18:13:33 -0500 Received: from eggs.gnu.org ([208.118.235.92]:51414) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ejYuA-0006i3-QA for submit@debbugs.gnu.org; Wed, 07 Feb 2018 18:13:31 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1ejYu3-0007Rs-Fe for submit@debbugs.gnu.org; Wed, 07 Feb 2018 18:13:25 -0500 X-Spam-Checker-Version: SpamAssassin 3.3.2 (2011-06-06) on eggs.gnu.org X-Spam-Level: X-Spam-Status: No, score=0.8 required=5.0 tests=BAYES_50 autolearn=disabled version=3.3.2 Received: from lists.gnu.org ([2001:4830:134:3::11]:59080) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1ejYu3-0007RZ-Bv for submit@debbugs.gnu.org; Wed, 07 Feb 2018 18:13:23 -0500 Received: from eggs.gnu.org ([2001:4830:134:3::10]:42365) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1ejYu1-0006ZS-1w for guix-patches@gnu.org; Wed, 07 Feb 2018 18:13:23 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1ejYtu-0007De-Qr for guix-patches@gnu.org; Wed, 07 Feb 2018 18:13:21 -0500 Received: from dd26836.kasserver.com ([85.13.145.193]:54616) by eggs.gnu.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1ejYtu-00078b-FN for guix-patches@gnu.org; Wed, 07 Feb 2018 18:13:14 -0500 Received: from dayas.3.home (77.118.187.73.wireless.dyn.drei.com [77.118.187.73]) by dd26836.kasserver.com (Postfix) with ESMTPSA id 33C8333601D1; Thu, 8 Feb 2018 00:13:09 +0100 (CET) From: Danny Milosavljevic To: guix-patches@gnu.org Subject: [PATCH cuirass] database: Prevent SQL injection. Date: Thu, 8 Feb 2018 00:12:58 +0100 Message-Id: <20180207231258.31169-1-dannym@scratchpost.org> X-Mailer: git-send-email 2.15.1 Tags: patch X-detected-operating-system: by eggs.gnu.org: GNU/Linux 2.2.x-3.x [generic] [fuzzy] X-detected-operating-system: by eggs.gnu.org: GNU/Linux 2.6.x X-Received-From: 2001:4830:134:3::11 X-Spam-Score: -5.0 (-----) X-Debbugs-Envelope-To: submit Cc: Danny Milosavljevic X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -5.0 (-----) * src/cuirass/database.scm: Import (srfi srfi-26). (sqlite-fetch-all): New variable. (sqlite-bind-args): New variable. (sqlite-exec): Use the above. (db-add-specification): Prevent SQL injection. (db-get-specifications): Modify it for consistency. (db-add-derivation): Prevent SQL injection. (db-get-derivation): Prevent SQL injection. (db-add-evaluation): Prevent SQL injection. (db-add-build): Prevent SQL injection. (db-update-build-status!): Prevent SQL injection. (db-get-outputs): Prevent SQL injection. (db-build-request): Delete variable. (db-get-builds): Prevent SQL injection. (db-get-build): Use db-get-builds. (db-get-stamp): Prevent SQL injection. (db-add-stamp): Prevent SQL injection. --- src/cuirass/database.scm | 238 +++++++++++++++++++++++------------------------ 1 file changed, 116 insertions(+), 122 deletions(-) diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm index 5ca3ad3..ca1e778 100644 --- a/src/cuirass/database.scm +++ b/src/cuirass/database.scm @@ -26,6 +26,7 @@ #:use-module (ice-9 rdelim) #:use-module (srfi srfi-1) #:use-module (srfi srfi-19) + #:use-module (srfi srfi-26) #:use-module (sqlite3) #:export (;; Procedures. assq-refs @@ -46,35 +47,56 @@ db-get-builds read-sql-file read-quoted-string - sqlite-exec + sqlite-exec ; for tests only ;; Parameters. %package-database %package-schema-file ;; Macros. with-database)) -(define (%sqlite-exec db sql) - (let* ((stmt (sqlite-prepare db sql)) - (res (let loop ((res '())) - (let ((row (sqlite-step stmt))) - (if (not row) - (reverse! res) - (loop (cons row res))))))) - (sqlite-finalize stmt) - res)) +(define (sqlite-fetch-all stmt) + (reverse! (sqlite-fold cons '() stmt))) + +(define (sqlite-bind-args stmt . args) + "Bind STMT parameters, one after another, to ARGS. +Also binds named parameters to the respective ones." + (let loop ((i 1) + (args args)) + (if (null? args) + #f + (let ((arg (car args)) + (rest (cdr args))) + (if (keyword? arg) + (begin + (sqlite-bind stmt (keyword->symbol arg) (car rest)) + (loop i (cdr rest))) + (begin + (sqlite-bind stmt i arg) + (loop (1+ i) rest))))))) (define-syntax sqlite-exec - ;; Note: Making it a macro so -Wformat can do its job. (lambda (s) - "Wrap 'sqlite-prepare', 'sqlite-step', and 'sqlite-finalize'. Send to given -SQL statement to DB. FMT and ARGS are passed to 'format'." (syntax-case s () - ((_ db fmt args ...) - #'(%sqlite-exec db (format #f fmt args ...))) - (id - (identifier? #'id) - #'(lambda (db fmt . args) - (%sqlite-exec db (apply format #f fmt args))))))) + ((_ db sqltext arg ...) (string? (syntax->datum #'sqltext)) + #`(let* ((stmt (sqlite-prepare db sqltext #:cache? #t))) + (sqlite-bind-args stmt arg ...) + (sqlite-fetch-all stmt))) + ((_ db sqltext) (string? (syntax->datum #'sqltext)) + #`(let* ((stmt (sqlite-prepare db sqltext #:cache? #t))) + (sqlite-fetch-all stmt))) + ((_ db sqltext arg ...) + #`(let ((stmt (sqlite-prepare db sqltext #:cache? #f))) + (sqlite-bind-args stmt arg ...) + (let ((result (sqlite-fetch-all stmt))) + (sqlite-finalize stmt) + result))) + (id (identifier? #'id) + #'(lambda (db sqltext . args) + (let ((stmt (sqlite-prepare db sqltext #:cache? #f))) + (apply sqlite-bind-args stmt args) + (let ((result (sqlite-fetch-all stmt))) + (sqlite-finalize stmt) + result))))))) (define %package-database ;; Define to the database file name of this package. @@ -144,10 +166,12 @@ database object." (apply sqlite-exec db "\ INSERT OR IGNORE INTO Specifications (repo_name, url, load_path, file, \ proc, arguments, branch, tag, revision, no_compile_p) \ - VALUES ('~A', '~A', '~A', '~A', '~S', '~S', '~A', '~A', '~A', ~A);" + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" (append - (assq-refs spec '(#:name #:url #:load-path #:file #:proc #:arguments)) - (assq-refs spec '(#:branch #:tag #:commit) "NULL") + (assq-refs spec '(#:name #:url #:load-path #:file)) + (map symbol->string (assq-refs spec '(#:proc))) + (map object->string (assq-refs spec '(#:arguments))) + (assq-refs spec '(#:branch #:tag #:commit) #f) (list (if (assq-ref spec #:no-compile?) "1" "0")))) (last-insert-rowid db)) @@ -166,8 +190,8 @@ INSERT OR IGNORE INTO Specifications (repo_name, url, load_path, file, \ (#:proc . ,(with-input-from-string proc read)) (#:arguments . ,(with-input-from-string args read)) (#:branch . ,branch) - (#:tag . ,(if (string=? tag "NULL") #f tag)) - (#:commit . ,(if (string=? rev "NULL") #f rev)) + (#:tag . ,tag) + (#:commit . ,rev) (#:no-compile? . ,(positive? no-compile?))) specs)))))) @@ -175,20 +199,21 @@ INSERT OR IGNORE INTO Specifications (repo_name, url, load_path, file, \ "Store a derivation result in database DB and return its ID." (sqlite-exec db "\ INSERT OR IGNORE INTO Derivations (derivation, job_name, system, nix_name, evaluation)\ - VALUES ('~A', '~A', '~A', '~A', '~A');" + VALUES (?, ?, ?, ?, ?);" (assq-ref job #:derivation) (assq-ref job #:job-name) (assq-ref job #:system) (assq-ref job #:nix-name) - (assq-ref job #:eval-id))) + (assq-ref job #:eval-id)) + (last-insert-rowid db)) (define (db-get-derivation db id) "Retrieve a job in database DB which corresponds to ID." - (car (sqlite-exec db "SELECT * FROM Derivations WHERE derivation='~A';" id))) + (car (sqlite-exec db "SELECT * FROM Derivations WHERE derivation=?;" id))) (define (db-add-evaluation db eval) (sqlite-exec db "\ -INSERT INTO Evaluations (specification, revision) VALUES ('~A', '~A');" +INSERT INTO Evaluations (specification, revision) VALUES (?, ?);" (assq-ref eval #:specification) (assq-ref eval #:revision)) (last-insert-rowid db)) @@ -235,7 +260,7 @@ in the OUTPUTS table." (let* ((build-exec (sqlite-exec db "\ INSERT INTO Builds (derivation, evaluation, log, status, timestamp, starttime, stoptime)\ - VALUES ('~A', '~A', '~A', '~A', '~A', '~A', '~A');" + VALUES (?, ?, ?, ?, ?, ?, ?);" (assq-ref build #:derivation) (assq-ref build #:eval-id) (assq-ref build #:log) @@ -249,7 +274,7 @@ INSERT INTO Builds (derivation, evaluation, log, status, timestamp, starttime, s (match output ((name . path) (sqlite-exec db "\ -INSERT INTO Outputs (build, name, path) VALUES ('~A', '~A', '~A');" +INSERT INTO Outputs (build, name, path) VALUES (?, ?, ?);" build-id name path)))) (assq-ref build #:outputs)) build-id)) @@ -262,17 +287,17 @@ log file for DRV." (time-second (current-time time-utc))) (if (= status (build-status started)) - (sqlite-exec db "UPDATE Builds SET starttime='~A', status='~A' \ -WHERE derivation='~A';" + (sqlite-exec db "UPDATE Builds SET starttime=?, status=? \ +WHERE derivation=?;" now status drv) - (sqlite-exec db "UPDATE Builds SET stoptime='~A', \ -status='~A'~@[, log='~A'~] WHERE derivation='~A';" - now status log-file drv))) + (if log-file + (sqlite-exec db "UPDATE Builds SET stoptime=?, status=?, log=? WHERE derivation=?;" now status log-file drv) + (sqlite-exec db "UPDATE Builds SET stoptime=?, status=? WHERE derivation=?;" now status drv)))) (define (db-get-outputs db build-id) "Retrieve the OUTPUTS of the build identified by BUILD-ID in DB database." (let loop ((rows - (sqlite-exec db "SELECT name, path FROM Outputs WHERE build='~A';" + (sqlite-exec db "SELECT name, path FROM Outputs WHERE build=?;" build-id)) (outputs '())) (match rows @@ -283,15 +308,6 @@ status='~A'~@[, log='~A'~] WHERE derivation='~A';" (cons `(,name . ((#:path . ,path))) outputs)))))) -(define db-build-request "\ -SELECT Builds.id, Builds.timestamp, Builds.starttime, Builds.stoptime, Builds.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 = Derivations.derivation and Builds.evaluation = Derivations.evaluation \ -INNER JOIN Evaluations ON Derivations.evaluation = Evaluations.id \ -INNER JOIN Specifications ON Evaluations.specification = Specifications.repo_name") - (define (db-format-build db build) (match build (#(id timestamp starttime stoptime log status derivation job-name system @@ -310,90 +326,68 @@ INNER JOIN Specifications ON Evaluations.specification = Specifications.repo_nam (#:outputs . ,(db-get-outputs db id)) (#:branch . ,branch))))) -(define (db-get-build db id) - "Retrieve a build in database DB which corresponds to ID." - (let ((res (sqlite-exec db (string-append db-build-request - " WHERE Builds.id='~A';") id))) - (match res - ((build) - (db-format-build db build)) - (() #f)))) - (define (db-get-builds db filters) "Retrieve all builds in database DB which are matched by given FILTERS. FILTERS is an assoc list which possible keys are 'project | 'jobset | 'job | 'system | 'nr | 'order | 'status." - (define (format-where-clause filters) - (let ((where-clause - (filter-map - (lambda (param) - (match param - (('project project) - (format #f "Specifications.repo_name='~A'" project)) - (('jobset jobset) - (format #f "Specifications.branch='~A'" jobset)) - (('job job) - (format #f "Derivations.job_name='~A'" job)) - (('system system) - (format #f "Derivations.system='~A'" system)) - (('status 'done) - "Builds.status >= 0") - (('status 'pending) - "Builds.status < 0") - (_ #f))) - filters))) - (if (> (length where-clause) 0) - (string-append - "WHERE " - (string-join where-clause " AND ")) - ""))) - - (define (format-order-clause filters) - (or (any (match-lambda - (('order 'build-id) - "ORDER BY Builds.id ASC") - (('order 'decreasing-build-id) - "ORDER BY Builds.id DESC") - (('order 'finish-time) - "ORDER BY Builds.stoptime DESC") - (('order 'start-time) - "ORDER BY Builds.start DESC") - (('order 'submission-time) - "ORDER BY Builds.timestamp DESC") - (_ #f)) - filters) - "ORDER BY Builds.id DESC")) ;default order - - (define (format-limit-clause filters) - (or (any (match-lambda - (('nr number) - (format #f "LIMIT '~A'" number)) - (_ #f)) - filters) - "")) + ;; XXX Change caller and remove + (define (assqx-ref filters key) + (if (null? filters) + #f + (match (car filters) + ((xkey xvalue) (if (eq? key xkey) + xvalue + (assqx-ref (cdr filters) key)))))) + (let* ((order (if (eq? (assqx-ref filters 'order) 'build-id) + "ASC" + "DESC")) + (order-column-name + (match (assqx-ref filters 'order) + (('order 'build-id) "Builds.id") + (('order 'decreasing-build-id) "Builds.id") + (('order 'finish-time) "Builds.stoptime") + (('order 'start-time) "Builds.starttime") + (('order 'submission-time) "Builds.timestamp") + (_ "Builds.id"))) + (stmt-text (format #f "\ +SELECT Builds.id, Builds.timestamp, Builds.starttime, Builds.stoptime, Builds.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 = Derivations.derivation AND Builds.evaluation = Derivations.evaluation \ +INNER JOIN Evaluations ON Derivations.evaluation = Evaluations.id \ +INNER JOIN Specifications ON Evaluations.specification = Specifications.repo_name \ +WHERE (:id IS NULL OR (:id = Builds.id)) \ +OR (:project IS NULL OR (:project = Specifications.repo_name)) \ +OR (:jobset IS NULL OR (:jobset = Specifications.branch)) \ +OR (:job IS NULL OR (:job = Derivations.job_name)) \ +OR (:system IS NULL OR (:system = Derivations.system)) \ +OR (:status IS NULL OR (:status = 'done' AND Builds.status >= 0) OR (:status = 'pending' AND Builds.status < 0)) \ +ORDER BY ~a ~a LIMIT :nr;" order-column-name order)) + (stmt (sqlite-prepare db stmt-text #:cache? #t))) + (sqlite-bind-args stmt #:id (assqx-ref filters 'id) + #:project (assqx-ref filters 'project) + #:jobset (assqx-ref filters 'jobset) + #:job (assqx-ref filters 'job) + #:system (assqx-ref filters 'system) + #:status (and=> (assqx-ref filters 'status) + object->string) + #:nr (match (assqx-ref filters 'nr) + (#f -1) + (x x))) + (map (cut db-format-build db <>) (sqlite-fetch-all stmt)))) - (let loop ((rows - (sqlite-exec db (string-append - db-build-request - " " - (format-where-clause filters) - " " - (format-order-clause filters) - " " - (format-limit-clause filters) - ";"))) - (outputs '())) - (match rows - (() - (reverse outputs)) - ((row . rest) - (loop rest - (cons (db-format-build db row) outputs)))))) +(define (db-get-build db id) + "Retrieve a build in database DB which corresponds to ID." + (match (db-get-builds db '(('id id))) + ((build) + build) + (() #f))) (define (db-get-stamp db spec) "Return a stamp corresponding to specification SPEC in database DB." - (let ((res (sqlite-exec db "SELECT * FROM Stamps WHERE specification='~A';" + (let ((res (sqlite-exec db "SELECT * FROM Stamps WHERE specification=?;" (assq-ref spec #:name)))) (match res (() "") @@ -403,10 +397,10 @@ FILTERS is an assoc list which possible keys are 'project | 'jobset | 'job | "Associate stamp COMMIT to specification SPEC in database DB." (if (string-null? (db-get-stamp db spec)) (sqlite-exec db "\ -INSERT INTO Stamps (specification, stamp) VALUES ('~A', '~A');" +INSERT INTO Stamps (specification, stamp) VALUES (?, ?);" (assq-ref spec #:name) commit) (sqlite-exec db "\ -UPDATE Stamps SET stamp='~A' WHERE specification='~A';" +UPDATE Stamps SET stamp=? WHERE specification=?;" commit (assq-ref spec #:name)))) From debbugs-submit-bounces@debbugs.gnu.org Thu Feb 08 11:34:41 2018 Received: (at 30386) by debbugs.gnu.org; 8 Feb 2018 16:34:41 +0000 Received: from localhost ([127.0.0.1]:34241 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ejp9l-0006We-89 for submit@debbugs.gnu.org; Thu, 08 Feb 2018 11:34:41 -0500 Received: from dd26836.kasserver.com ([85.13.145.193]:37080) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ejp9j-0006WW-9s for 30386@debbugs.gnu.org; Thu, 08 Feb 2018 11:34:40 -0500 Received: from dayas.3.home (77.118.210.201.wireless.dyn.drei.com [77.118.210.201]) by dd26836.kasserver.com (Postfix) with ESMTPSA id 5BBF2336049F; Thu, 8 Feb 2018 17:34:37 +0100 (CET) From: Danny Milosavljevic To: 30386@debbugs.gnu.org Subject: [PATCH v2 cuirass] database: Prevent SQL injection. Date: Thu, 8 Feb 2018 17:34:32 +0100 Message-Id: <20180208163432.9468-1-dannym@scratchpost.org> X-Mailer: git-send-email 2.15.1 In-Reply-To: <20180207231258.31169-1-dannym@scratchpost.org> References: <20180207231258.31169-1-dannym@scratchpost.org> Tags: patch X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 30386 Cc: Danny Milosavljevic X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -0.7 (/) * src/cuirass/database.scm: Use (srfi srfi-26). (sqlite-fetch-all): New variable. (sqlite-bind-args): New variable, for now. (sqlite-exec): Automatically do not finalize literal SQL statements. (db-add-specification): Use #f for NULL. (db-get-specifications): Use #f for NULL. (db-build-request): Delete variable. (db-get-builds): Prevent SQL injection. (db-get-build): Use db-get-builds. --- src/cuirass/database.scm | 207 +++++++++++++++++++++++------------------------ 1 file changed, 100 insertions(+), 107 deletions(-) diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm index a40a2d8..2803fd5 100644 --- a/src/cuirass/database.scm +++ b/src/cuirass/database.scm @@ -26,6 +26,7 @@ #:use-module (ice-9 rdelim) #:use-module (srfi srfi-1) #:use-module (srfi srfi-19) + #:use-module (srfi srfi-26) #:use-module (sqlite3) #:export (;; Procedures. assq-refs @@ -46,29 +47,56 @@ db-get-builds read-sql-file read-quoted-string - sqlite-exec + sqlite-exec ; for tests only ;; Parameters. %package-database %package-schema-file ;; Macros. with-database)) -(define (sqlite-exec db sql . args) - "Evaluate the given SQL query with the given ARGS. Return the list of -rows." - (define (normalize arg) - ;; Turn ARG into a string, unless it's a primitive SQL datatype. - (if (or (null? arg) (pair? arg) (vector? arg)) - (object->string arg) - arg)) - - (let ((stmt (sqlite-prepare db sql #:cache? #t))) - (for-each (lambda (arg index) - (sqlite-bind stmt index (normalize arg))) - args (iota (length args) 1)) - (let ((result (sqlite-fold-right cons '() stmt))) - (sqlite-finalize stmt) - result))) +(define (sqlite-fetch-all stmt) + (reverse! (sqlite-fold cons '() stmt))) + +(define (sqlite-bind-args stmt . args) + "Bind STMT parameters, one after another, to ARGS. +Also binds named parameters to the respective ones." + (let loop ((i 1) + (args args)) + (if (null? args) + #f + (let ((arg (car args)) + (rest (cdr args))) + (if (keyword? arg) + (begin + (sqlite-bind stmt (keyword->symbol arg) (car rest)) + (loop i (cdr rest))) + (begin + (sqlite-bind stmt i arg) + (loop (1+ i) rest))))))) + +(define-syntax sqlite-exec + (lambda (s) + (syntax-case s () + ((_ db sqltext arg ...) (string? (syntax->datum #'sqltext)) + #`(let* ((stmt (sqlite-prepare db sqltext #:cache? #t))) + (sqlite-bind-args stmt arg ...) + (sqlite-fetch-all stmt))) + ((_ db sqltext) (string? (syntax->datum #'sqltext)) + #`(let* ((stmt (sqlite-prepare db sqltext #:cache? #t))) + (sqlite-fetch-all stmt))) + ((_ db sqltext arg ...) + #`(let ((stmt (sqlite-prepare db sqltext #:cache? #f))) + (sqlite-bind-args stmt arg ...) + (let ((result (sqlite-fetch-all stmt))) + (sqlite-finalize stmt) + result))) + (id (identifier? #'id) + #'(lambda (db sqltext . args) + (let ((stmt (sqlite-prepare db sqltext #:cache? #f))) + (apply sqlite-bind-args stmt args) + (let ((result (sqlite-fetch-all stmt))) + (sqlite-finalize stmt) + result))))))) (define %package-database ;; Define to the database file name of this package. @@ -143,7 +171,7 @@ INSERT OR IGNORE INTO Specifications (repo_name, url, load_path, file, \ (assq-refs spec '(#:name #:url #:load-path #:file)) (map symbol->string (assq-refs spec '(#:proc))) (map object->string (assq-refs spec '(#:arguments))) - (assq-refs spec '(#:branch #:tag #:commit) "NULL") + (assq-refs spec '(#:branch #:tag #:commit) #f) (list (if (assq-ref spec #:no-compile?) "1" "0")))) (last-insert-rowid db)) @@ -162,8 +190,8 @@ INSERT OR IGNORE INTO Specifications (repo_name, url, load_path, file, \ (#:proc . ,(with-input-from-string proc read)) (#:arguments . ,(with-input-from-string args read)) (#:branch . ,branch) - (#:tag . ,(if (string=? tag "NULL") #f tag)) - (#:commit . ,(if (string=? rev "NULL") #f rev)) + (#:tag . ,tag) + (#:commit . ,rev) (#:no-compile? . ,(positive? no-compile?))) specs)))))) @@ -289,15 +317,6 @@ WHERE derivation=? AND status != ?;" (cons `(,name . ((#:path . ,path))) outputs)))))) -(define db-build-request "\ -SELECT Builds.id, Builds.timestamp, Builds.starttime, Builds.stoptime, Builds.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 = Derivations.derivation and Builds.evaluation = Derivations.evaluation \ -INNER JOIN Evaluations ON Derivations.evaluation = Evaluations.id \ -INNER JOIN Specifications ON Evaluations.specification = Specifications.repo_name") - (define (db-format-build db build) (match build (#(id timestamp starttime stoptime log status derivation job-name system @@ -316,90 +335,64 @@ INNER JOIN Specifications ON Evaluations.specification = Specifications.repo_nam (#:outputs . ,(db-get-outputs db id)) (#:branch . ,branch))))) -(define (db-get-build db id) - "Retrieve a build in database DB which corresponds to ID." - (let ((res (sqlite-exec db (string-append db-build-request - " WHERE Builds.id=?;") id))) - (match res - ((build) - (db-format-build db build)) - (() #f)))) - (define (db-get-builds db filters) "Retrieve all builds in database DB which are matched by given FILTERS. FILTERS is an assoc list which possible keys are 'project | 'jobset | 'job | 'system | 'nr | 'order | 'status." - (define (format-where-clause filters) - (let ((where-clause - (filter-map - (lambda (param) - (match param - (('project project) - (format #f "Specifications.repo_name='~A'" project)) - (('jobset jobset) - (format #f "Specifications.branch='~A'" jobset)) - (('job job) - (format #f "Derivations.job_name='~A'" job)) - (('system system) - (format #f "Derivations.system='~A'" system)) - (('status 'done) - "Builds.status >= 0") - (('status 'pending) - "Builds.status < 0") - (_ #f))) - filters))) - (if (> (length where-clause) 0) - (string-append - "WHERE " - (string-join where-clause " AND ")) - ""))) - - (define (format-order-clause filters) - (or (any (match-lambda - (('order 'build-id) - "ORDER BY Builds.id ASC") - (('order 'decreasing-build-id) - "ORDER BY Builds.id DESC") - (('order 'finish-time) - "ORDER BY Builds.stoptime DESC") - (('order 'start-time) - "ORDER BY Builds.start DESC") - (('order 'submission-time) - "ORDER BY Builds.timestamp DESC") - (('order 'status+submission-time) - ;; With this order, builds in 'running' state (-1) appear - ;; before those in 'scheduled' state (-2). - "ORDER BY Builds.status DESC, Builds.timestamp DESC") - (_ #f)) - filters) - "ORDER BY Builds.id DESC")) ;default order - - (define (format-limit-clause filters) - (or (any (match-lambda - (('nr number) - (format #f "LIMIT '~A'" number)) - (_ #f)) - filters) - "")) + ;; XXX Change caller and remove + (define (assqx-ref filters key) + (if (null? filters) + #f + (match (car filters) + ((xkey xvalue) (if (eq? key xkey) + xvalue + (assqx-ref (cdr filters) key)))))) + (let* ((order (if (eq? (assqx-ref filters 'order) 'build-id) + "ASC" + "DESC")) + (order-column-name + (match (assqx-ref filters 'order) + (('order 'build-id) "Builds.id") + (('order 'decreasing-build-id) "Builds.id") + (('order 'finish-time) "Builds.stoptime") + (('order 'start-time) "Builds.starttime") + (('order 'submission-time) "Builds.timestamp") + (_ "Builds.id"))) + (stmt-text (format #f "\ +SELECT Builds.id, Builds.timestamp, Builds.starttime, Builds.stoptime, Builds.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 = Derivations.derivation AND Builds.evaluation = Derivations.evaluation \ +INNER JOIN Evaluations ON Derivations.evaluation = Evaluations.id \ +INNER JOIN Specifications ON Evaluations.specification = Specifications.repo_name \ +WHERE (:id IS NULL OR (:id = Builds.id)) \ +OR (:project IS NULL OR (:project = Specifications.repo_name)) \ +OR (:jobset IS NULL OR (:jobset = Specifications.branch)) \ +OR (:job IS NULL OR (:job = Derivations.job_name)) \ +OR (:system IS NULL OR (:system = Derivations.system)) \ +OR (:status IS NULL OR (:status = 'done' AND Builds.status >= 0) OR (:status = 'pending' AND Builds.status < 0)) \ +ORDER BY ~a ~a LIMIT :nr;" order-column-name order)) + (stmt (sqlite-prepare db stmt-text #:cache? #t))) + (sqlite-bind-args stmt #:id (assqx-ref filters 'id) + #:project (assqx-ref filters 'project) + #:jobset (assqx-ref filters 'jobset) + #:job (assqx-ref filters 'job) + #:system (assqx-ref filters 'system) + #:status (and=> (assqx-ref filters 'status) + object->string) + #:nr (match (assqx-ref filters 'nr) + (#f -1) + (x x))) + (map (cut db-format-build db <>) (sqlite-fetch-all stmt)))) - (let loop ((rows - (sqlite-exec db (string-append - db-build-request - " " - (format-where-clause filters) - " " - (format-order-clause filters) - " " - (format-limit-clause filters) - ";"))) - (outputs '())) - (match rows - (() - (reverse outputs)) - ((row . rest) - (loop rest - (cons (db-format-build db row) outputs)))))) +(define (db-get-build db id) + "Retrieve a build in database DB which corresponds to ID." + (match (db-get-builds db '(('id id))) + ((build) + build) + (() #f))) (define (db-get-stamp db spec) "Return a stamp corresponding to specification SPEC in database DB." From debbugs-submit-bounces@debbugs.gnu.org Fri Feb 09 04:51:27 2018 Received: (at 30386) by debbugs.gnu.org; 9 Feb 2018 09:51:27 +0000 Received: from localhost ([127.0.0.1]:34656 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ek5L5-00083L-Af for submit@debbugs.gnu.org; Fri, 09 Feb 2018 04:51:27 -0500 Received: from hera.aquilenet.fr ([185.233.100.1]:46276) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ek5L3-00083D-LA for 30386@debbugs.gnu.org; Fri, 09 Feb 2018 04:51:26 -0500 Received: from localhost (localhost [127.0.0.1]) by hera.aquilenet.fr (Postfix) with ESMTP id 0B24C10BF9; Fri, 9 Feb 2018 10:51:25 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at aquilenet.fr Received: from hera.aquilenet.fr ([127.0.0.1]) by localhost (hera.aquilenet.fr [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id oWA4hArbHzVL; Fri, 9 Feb 2018 10:51:24 +0100 (CET) Received: from ribbon (unknown [193.50.110.130]) by hera.aquilenet.fr (Postfix) with ESMTPSA id D3A888421; Fri, 9 Feb 2018 10:51:23 +0100 (CET) From: ludo@gnu.org (Ludovic =?utf-8?Q?Court=C3=A8s?=) To: Danny Milosavljevic Subject: Re: [bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection. 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> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Spam-Score: 1.0 (+) X-Debbugs-Envelope-To: 30386 Cc: 30386@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: 1.0 (+) 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 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. From debbugs-submit-bounces@debbugs.gnu.org Fri Feb 09 06:16:24 2018 Received: (at 30386) by debbugs.gnu.org; 9 Feb 2018 11:16:24 +0000 Received: from localhost ([127.0.0.1]:34735 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ek6fI-0005Mc-Hc for submit@debbugs.gnu.org; Fri, 09 Feb 2018 06:16:24 -0500 Received: from dd26836.kasserver.com ([85.13.145.193]:35976) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ek6fF-0005MS-KG for 30386@debbugs.gnu.org; Fri, 09 Feb 2018 06:16:22 -0500 Received: from localhost (178.113.144.132.wireless.dyn.drei.com [178.113.144.132]) by dd26836.kasserver.com (Postfix) with ESMTPSA id 8E19333603BA; Fri, 9 Feb 2018 12:16:19 +0100 (CET) Date: Fri, 9 Feb 2018 12:16:11 +0100 From: Danny Milosavljevic To: ludo@gnu.org (Ludovic =?ISO-8859-1?Q?Court=E8s?=) Subject: Re: [bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection. Message-ID: <20180209121612.09a0cf53@scratchpost.org> In-Reply-To: <87r2pu4hk4.fsf@gnu.org> References: <20180207231258.31169-1-dannym@scratchpost.org> <20180208163432.9468-1-dannym@scratchpost.org> <87r2pu4hk4.fsf@gnu.org> X-Mailer: Claws Mail 3.16.0 (GTK+ 2.24.31; x86_64-unknown-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 30386 Cc: 30386@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -0.7 (/) Hi Ludo, no worries! > optimization looks good, provided the extra conditions don=E2=80=99t make= sqlite > slower. =20 Compared to parsing the SQL text again and again (which is dead slow), I th= ink an extra NULL check *on the same field* is not going to matter at all. Even compared to using lots of main memory and thus not being able to use the processor's cache (if we had lots of prepared statements), I think an extra NULL check is still better :) Of course once we have a lot of data in the tables, the actual lookup costs will dwarf any setup costs. Then still, it's checking the same field that's used anyway, so the extra cost should be neglible. >Do you think we can salvage this bit from your patch? =20 Sure. > It might allow us to use =E2=80=98sqlite-exec=E2=80=99 directly, and thus > benefit from the binding support in there, as in: >=20 > (sqlite-exec db "=E2=80=A6 WHERE " id " is NULL or =E2=80=A6") I added sqlite-bind-arguments with keyword arguments specifically so sqlite= -exec doesn't suck. So it would be like (sqlite-exec db "SELECT =E2=80=A6 :a =E2=80=A6 :b =E2= =80=A6 :a" #:a 42 #:b 2) Before, it was: (sqlite-exec db "SELECT =E2=80=A6 ? =E2=80=A6 ? =E2=80=A6 ?" 42 2 42) which repeated stuff - and was very fragile when changing things (one can e= asily get the order wrong and it would not have errored out). From debbugs-submit-bounces@debbugs.gnu.org Fri Feb 09 11:05:40 2018 Received: (at 30386) by debbugs.gnu.org; 9 Feb 2018 16:05:40 +0000 Received: from localhost ([127.0.0.1]:35613 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ekBBB-0007o1-AY for submit@debbugs.gnu.org; Fri, 09 Feb 2018 11:05:37 -0500 Received: from hera.aquilenet.fr ([185.233.100.1]:49200) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ekBBA-0007nt-8Z for 30386@debbugs.gnu.org; Fri, 09 Feb 2018 11:05:36 -0500 Received: from localhost (localhost [127.0.0.1]) by hera.aquilenet.fr (Postfix) with ESMTP id 4E84B11381; Fri, 9 Feb 2018 17:05:35 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at aquilenet.fr Received: from hera.aquilenet.fr ([127.0.0.1]) by localhost (hera.aquilenet.fr [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 3zOoEcK5Hz4t; Fri, 9 Feb 2018 17:05:34 +0100 (CET) Received: from ribbon (unknown [193.50.110.130]) by hera.aquilenet.fr (Postfix) with ESMTPSA id 71915112E6; Fri, 9 Feb 2018 17:05:34 +0100 (CET) From: ludo@gnu.org (Ludovic =?utf-8?Q?Court=C3=A8s?=) To: Danny Milosavljevic Subject: Re: [bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection. References: <20180207231258.31169-1-dannym@scratchpost.org> <20180208163432.9468-1-dannym@scratchpost.org> <87r2pu4hk4.fsf@gnu.org> <20180209121612.09a0cf53@scratchpost.org> X-URL: http://www.fdn.fr/~lcourtes/ X-Revolutionary-Date: 21 =?utf-8?Q?Pluvi=C3=B4se?= an 226 de la =?utf-8?Q?R=C3=A9volution?= X-PGP-Key-ID: 0x090B11993D9AEBB5 X-PGP-Key: http://www.fdn.fr/~lcourtes/ludovic.asc X-PGP-Fingerprint: 3CE4 6455 8A84 FDC6 9DB4 0CFB 090B 1199 3D9A EBB5 X-OS: x86_64-pc-linux-gnu Date: Fri, 09 Feb 2018 17:05:34 +0100 In-Reply-To: <20180209121612.09a0cf53@scratchpost.org> (Danny Milosavljevic's message of "Fri, 9 Feb 2018 12:16:11 +0100") Message-ID: <87k1vmywq9.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Spam-Score: 1.0 (+) X-Debbugs-Envelope-To: 30386 Cc: 30386@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: 1.0 (+) Danny Milosavljevic skribis: >> optimization looks good, provided the extra conditions don=E2=80=99t mak= e sqlite >> slower.=20=20 > > Compared to parsing the SQL text again and again (which is dead slow), I = think > an extra NULL check *on the same field* is not going to matter at all. > > Even compared to using lots of main memory and thus not being able to use > the processor's cache (if we had lots of prepared statements), I think an > extra NULL check is still better :) > > Of course once we have a lot of data in the tables, the actual lookup cos= ts > will dwarf any setup costs. Then still, it's checking the same field tha= t's > used anyway, so the extra cost should be neglible. Sounds good, let=E2=80=99s do that then. >> It might allow us to use =E2=80=98sqlite-exec=E2=80=99 directly, and thus >> benefit from the binding support in there, as in: >>=20 >> (sqlite-exec db "=E2=80=A6 WHERE " id " is NULL or =E2=80=A6") > > I added sqlite-bind-arguments with keyword arguments specifically so sqli= te-exec > doesn't suck. > > So it would be like (sqlite-exec db "SELECT =E2=80=A6 :a =E2=80=A6 :b =E2= =80=A6 :a" > #:a 42 > #:b 2) > > Before, it was: > > (sqlite-exec db "SELECT =E2=80=A6 ? =E2=80=A6 ? =E2=80=A6 ?" > 42 > 2 > 42) Right, but now it=E2=80=99s as I wrote above: you can include arguments in = the middle of the SQL strings, and =E2=80=98sqlite-exec=E2=80=99 takes care of = turning that into question marks and so on: https://git.savannah.gnu.org/cgit/guix/guix-cuirass.git/commit/?id=3Db0c3= 9b31f61cfc494e0dfbe823b3fe4275efbc7a Anyway, we can support both the keyword style you show above, and the other thing I mention, and use whichever is most convenient for the code at hand. I find the =E2=80=98sqlite-exec=E2=80=99 convenient for simple cases where = the query is a literal, but the keyword style might be more convenient for complex queries like =E2=80=98db-get-builds=E2=80=99. Thanks, Ludo=E2=80=99. From debbugs-submit-bounces@debbugs.gnu.org Fri Feb 09 11:45:13 2018 Received: (at 30386) by debbugs.gnu.org; 9 Feb 2018 16:45:14 +0000 Received: from localhost ([127.0.0.1]:35639 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ekBnV-0000GY-PH for submit@debbugs.gnu.org; Fri, 09 Feb 2018 11:45:13 -0500 Received: from dd26836.kasserver.com ([85.13.145.193]:60942) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ekBnT-0000GP-Iz for 30386@debbugs.gnu.org; Fri, 09 Feb 2018 11:45:12 -0500 Received: from localhost (178.113.144.132.wireless.dyn.drei.com [178.113.144.132]) by dd26836.kasserver.com (Postfix) with ESMTPSA id 10C8433603BA; Fri, 9 Feb 2018 17:45:09 +0100 (CET) Date: Fri, 9 Feb 2018 17:45:07 +0100 From: Danny Milosavljevic To: ludo@gnu.org (Ludovic =?ISO-8859-1?Q?Court=E8s?=) Subject: Re: [bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection. Message-ID: <20180209174507.74345900@scratchpost.org> In-Reply-To: <87k1vmywq9.fsf@gnu.org> References: <20180207231258.31169-1-dannym@scratchpost.org> <20180208163432.9468-1-dannym@scratchpost.org> <87r2pu4hk4.fsf@gnu.org> <20180209121612.09a0cf53@scratchpost.org> <87k1vmywq9.fsf@gnu.org> X-Mailer: Claws Mail 3.16.0 (GTK+ 2.24.31; x86_64-unknown-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 30386 Cc: 30386@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -0.7 (/) > Right, but now it=E2=80=99s as I wrote above: you can include arguments i= n the > middle of the SQL strings, and =E2=80=98sqlite-exec=E2=80=99 takes care o= f turning > that into question marks and so on: >=20 > https://git.savannah.gnu.org/cgit/guix/guix-cuirass.git/commit/?id=3Db0= c39b31f61cfc494e0dfbe823b3fe4275efbc7a Ah, didn't see that Before. Wow! Nice. I should pull more often :) From debbugs-submit-bounces@debbugs.gnu.org Fri Mar 02 07:59:09 2018 Received: (at 30386-done) by debbugs.gnu.org; 2 Mar 2018 12:59:10 +0000 Received: from localhost ([127.0.0.1]:40207 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1erkHF-00065J-Mh for submit@debbugs.gnu.org; Fri, 02 Mar 2018 07:59:09 -0500 Received: from hera.aquilenet.fr ([185.233.100.1]:45464) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1erkHE-00065A-Bp for 30386-done@debbugs.gnu.org; Fri, 02 Mar 2018 07:59:09 -0500 Received: from localhost (localhost [127.0.0.1]) by hera.aquilenet.fr (Postfix) with ESMTP id C9F69613; Fri, 2 Mar 2018 13:59:07 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at aquilenet.fr Received: from hera.aquilenet.fr ([127.0.0.1]) by localhost (hera.aquilenet.fr [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 2c-UgFBcNa4r; Fri, 2 Mar 2018 13:59:06 +0100 (CET) Received: from ribbon (unknown [193.50.110.134]) by hera.aquilenet.fr (Postfix) with ESMTPSA id C0C0B2AA; Fri, 2 Mar 2018 13:59:06 +0100 (CET) From: ludo@gnu.org (Ludovic =?utf-8?Q?Court=C3=A8s?=) To: Danny Milosavljevic Subject: Re: [bug#30386] [PATCH v2 cuirass] database: Prevent SQL injection. References: <20180207231258.31169-1-dannym@scratchpost.org> <20180208163432.9468-1-dannym@scratchpost.org> Date: Fri, 02 Mar 2018 13:59:06 +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: <87a7vqhbvp.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Spam-Score: 1.0 (+) X-Debbugs-Envelope-To: 30386-done Cc: 30386-done@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: 1.0 (+) Danny Milosavljevic 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)) \ > +ORDER BY ~a ~a LIMIT :nr;" order-column-name order)) > + (stmt (sqlite-prepare db stmt-text #:cache? #t))) > + (sqlite-bind-args stmt #:id (assqx-ref filters 'id) > + #:project (assqx-ref filters 'project) > + #:jobset (assqx-ref filters 'jobset) > + #:job (assqx-ref filters 'job) > + #:system (assqx-ref filters 'system) > + #:status (and=3D> (assqx-ref filters 'status) > + object->string) > + #:nr (match (assqx-ref filters 'nr) > + (#f -1) > + (x x))) This was pushed as 1bab5c4e56eb1849edc2cf0b23d433aeb2cac421, closing this issue now. Thank you! Ludo=E2=80=99. From unknown Fri Aug 15 02:02:07 2025 Received: (at fakecontrol) by fakecontrolmessage; To: internal_control@debbugs.gnu.org From: Debbugs Internal Request Subject: Internal Control Message-Id: bug archived. Date: Sat, 31 Mar 2018 11:24:06 +0000 User-Agent: Fakemail v42.6.9 # This is a fake control message. # # The action: # bug archived. thanks # This fakemail brought to you by your local debbugs # administrator