Use prepared statements for FETCH sql function

2 files changed, 6 insertions, 5 deletions

diff --git a/gn3/db/__init__.py b/gn3/db/__init__.py
index d62b575..fea43ec 100644
--- a/gn3/db/__init__.py
+++ b/gn3/db/__init__.py
@@ -66,14 +66,14 @@ def fetchone(conn: Any,
     """Run a SELECT on a table. Returns only one result!"""
     if not any(astuple(where)):
         return None
+    where_ = {k: v for k, v in asdict(where).items()
+              if v is not None and k in TABLEMAP[table]}
     sql = f"SELECT * FROM {table} "
     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(where_.values()))
         return DATACLASSMAP[table](*cursor.fetchone())
 
 
diff --git a/tests/unit/db/test_phenotypes.py b/tests/unit/db/test_phenotypes.py
index 21eb757..824d186 100644
--- a/tests/unit/db/test_phenotypes.py
+++ b/tests/unit/db/test_phenotypes.py
@@ -61,7 +61,8 @@ class TestPhenotypes(TestCase):
             self.assertEqual(phenotype.pre_pub_description,
                              "Test pre-publication")
             cursor.execute.assert_called_once_with(
-                "SELECT * FROM Phenotype WHERE id = '35' AND Owner = 'Rob'")
+                "SELECT * FROM Phenotype WHERE id = %s AND Owner = %s",
+                (35, 'Rob'))
 
     def test_diff_from_dict(self):
         """Test that a correct diff is generated"""