From 6d0145cded2a5e17ac220779e0df24a4cbf2e071 Mon Sep 17 00:00:00 2001
From: Timo Wilken
Date: Sun, 25 Aug 2024 12:05:10 +0200
Subject: Move to guile-squee for better prepared statement support

This avoids potential SQL injection when putting API response values directly
into the SQL statement.
---
 tw/services/personal-data-exporter.scm | 36 +++++++++++-----------------------
 1 file changed, 11 insertions(+), 25 deletions(-)

diff --git a/tw/services/personal-data-exporter.scm b/tw/services/personal-data-exporter.scm
index 18a048e1..80e85622 100644
--- a/tw/services/personal-data-exporter.scm
+++ b/tw/services/personal-data-exporter.scm
@@ -5,7 +5,7 @@
   #:use-module ((gnu packages guile)
                 #:select (guile-json-4))
   #:use-module ((gnu packages guile-xyz)
-                #:select (guile-dbi guile-dbd-postgresql))
+                #:select (guile-squee))
   #:use-module ((gnu packages tls)
                 #:select (guile-gnutls))
   #:use-module (gnu services)
@@ -50,27 +50,14 @@ completely managed by @code{personal-data-exporter-service-type}."))
 (define (conso-fetch-command config)
   (match-record config <personal-data-exporter-configuration> (conso-config-file)
     (program-file "conso-fetch-command"
-      (with-extensions (list guile-dbi guile-dbd-postgresql
-                             guile-json-4 guile-gnutls)   ; guile-gnutls needed by (web client)
+      (with-extensions (list guile-squee guile-json-4 guile-gnutls)   ; guile-gnutls needed by (web client)
         #~(begin
-            (eval-when (expand load eval)
-              ;; guile-dbi and guile-dbd-postgresql are written for guile 2.2, but work fine on 3.0.
-              ;; Guix doesn't know this, so we need to add them to the load paths manually.
-              (set! %load-path
-                    (cons* #$(file-append guile-dbi "/share/guile/site/2.2")
-                           #$(file-append guile-dbd-postgresql "/share/guile/site/2.2")
-                           %load-path))
-              (set! %load-compiled-path
-                    (cons* #$(file-append guile-dbi "/lib/guile/2.2/site-ccache")
-                           #$(file-append guile-dbd-postgresql "/lib/guile/2.2/site-ccache")
-                           %load-compiled-path)))
-
             (use-modules (ice-9 match)
                          (ice-9 receive)
                          (srfi srfi-19)             ; dates
                          (web client)
                          (web response)
-                         (dbi dbi)
+                         (squee)
                          (json))
 
             (define-json-type <settings> (prm) (api-token))
@@ -97,16 +84,15 @@ completely managed by @code{personal-data-exporter-service-type}."))
 
             (define (insert-statement db table)
               "Generate a function that inserts the data point given to it into TABLE in DB."
-              (match-lambda
-                (($ <data-point> date value)
-                 (let ((date-type (if (string-contains date " ") "timestamp" "date")))
+              (let ((query (format #f "insert into \"~a\" values ($1, $2) on conflict (\"time\") do update set \"value\" = EXCLUDED.\"value\";" table)))
+                (match-lambda
+                  (($ <data-point> date value)
                    ;; If a value already exists for any given point in time, replace it.
-                   (dbi-query db (format #f "insert into \"~a\" values (~a '~a', ~a) on conflict (\"time\") do update;"
-                                         table date-type date value))))))
+                   (exec-query db query (list date value))))))
 
             (let ((settings (call-with-input-file #$conso-config-file json->settings))
-                  (db (dbi-open "postgresql" ":::socket:")))
-              (dbi-query db "
+                  (db (connect-to-postgres-paramstring "")))
+              (exec-query db "
 create table if not exists \"conso_daily\" (\"time\" date primary key, \"value\" real not null);
 create table if not exists \"conso_load\" (\"time\" timestamp primary key, \"value\" real not null);
 create table if not exists \"conso_max_power\" (\"time\" timestamp primary key, \"value\" real not null);
@@ -115,9 +101,9 @@ create table if not exists \"conso_max_power\" (\"time\" timestamp primary key,
                         (conso-request settings "daily_consumption"))
               (for-each (insert-statement db "conso_max_power")
                         (conso-request settings "consumption_max_power"))
-              (for-each (insert-statement db "conso_load_curve")
+              (for-each (insert-statement db "conso_load")
                         (conso-request settings "consumption_load_curve"))
-              (dbi-close db)))))))
+              (pg-conn-finish db)))))))
 
 (define (conso-backup-command config)
   (match-record config <personal-data-exporter-configuration>
-- 
cgit v1.2.3