Use prepared statements for UPDATE sql function

2 files changed, 13 insertions, 13 deletions

diff --git a/gn3/db/__init__.py b/gn3/db/__init__.py
index 8b6bf73..ce92a7d 100644
--- a/gn3/db/__init__.py
+++ b/gn3/db/__init__.py
@@ -43,18 +43,20 @@ def update(conn: Any,
     """Run an UPDATE on a table"""
     if not (any(astuple(data)) and any(astuple(where))):
         return None
+    data_ = {k: v for k, v in asdict(data).items()
+             if v is not None and k in TABLEMAP[table]}
+    where_ = {k: v for k, v in asdict(where).items()
+              if v is not None and k in TABLEMAP[table]}
     sql = f"UPDATE {table} SET "
     sql += ", ".join(f"{TABLEMAP[table].get(k)} "
-                     f"= '{escape_string(str(v)).decode('utf-8')}'" for
-                     k, v in asdict(data).items()
-                     if v is not None and k in TABLEMAP[table])
+                     "= %s" for k in data_.keys())
     sql += " WHERE "
     sql += " AND ".join(f"{TABLEMAP[table].get(k)} = "
-                        f"'{escape_string(str(v)).decode('utf-8')}'" for
-                        k, v in asdict(where).items()
-                        if v is not None and k in TABLEMAP[table])
+                        "%s" for k in where_.keys())
     with conn.cursor() as cursor:
-        cursor.execute(sql)
+        cursor.execute(sql,
+                       tuple(data_.values()) + tuple(where_.values()))
+        conn.commit()
         return cursor.rowcount
 
 
diff --git a/tests/unit/db/test_phenotypes.py b/tests/unit/db/test_phenotypes.py
index fdeca5e..21eb757 100644
--- a/tests/unit/db/test_phenotypes.py
+++ b/tests/unit/db/test_phenotypes.py
@@ -37,12 +37,10 @@ class TestPhenotypes(TestCase):
                 where=Phenotype(id_=1, owner="Rob")), 1)
             cursor.execute.assert_called_once_with(
                 "UPDATE Phenotype SET "
-                "Pre_publication_description = "
-                "'Test Pre Pub', "
-                "Post_publication_description = "
-                "'Test Post Pub', Submitter = 'Rob' "
-                "WHERE id = '1' AND Owner = 'Rob'"
-            )
+                "Pre_publication_description = %s, "
+                "Post_publication_description = %s, "
+                "Submitter = %s WHERE id = %s AND Owner = %s",
+                ('Test Pre Pub', 'Test Post Pub', 'Rob', 1, 'Rob'))
 
     def test_fetch_phenotype(self):
         """Test that a single phenotype is fetched properly