From 09cc368920182681cad74a908414e59632db6bbd Mon Sep 17 00:00:00 2001
From: Munyoki Kilyungi
Date: Wed, 7 Sep 2022 11:00:41 +0300
Subject: Fix sql queries

"%s" should only be used outside table names and column names
otherwise a string literal will be inserted thereby leading to errors
in the sql statements.

* wqflask/base/data_set.py (geno_mrna_confidentiality): Use f-strings
for table/columns/clause.
* wqflask/base/trait.py (retrieve_trait_info): Ditto.
* wqflask/wqflask/gsearch.py (GSearch.__init__): Ditto.
* wqflask/wqflask/interval_analyst/GeneUtil.py (loadGenes): Ditto.
* wqflask/wqflask/snp_browser/snp_browser.py
(SnpBrowser.get_browser_results): Ditto.
---
 wqflask/base/trait.py | 15 +++++++--------
 1 file changed, 7 insertions(+), 8 deletions(-)

(limited to 'wqflask/base/trait.py')

diff --git a/wqflask/base/trait.py b/wqflask/base/trait.py
index 21575230..2ca34028 100644
--- a/wqflask/base/trait.py
+++ b/wqflask/base/trait.py
@@ -426,14 +426,13 @@ def retrieve_trait_info(trait, dataset, get_qtl_info=False):
                 display_fields_string = ', ProbeSet.'.join(dataset.display_fields)
                 display_fields_string = f'ProbeSet.{display_fields_string}'
                 cursor.execute(
-                    "SELECT %s FROM ProbeSet, ProbeSetFreeze, "
+                    f"SELECT {display_fields_string} FROM ProbeSet, ProbeSetFreeze, "
                     "ProbeSetXRef WHERE "
                     "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
                     "AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND "
                     "ProbeSetFreeze.Name = %s AND "
                     "ProbeSet.Name = %s",
-                    (display_fields_string, dataset.name,
-                     str(trait.name),)
+                    (dataset.name, str(trait.name),)
                 )
                 trait_info = cursor.fetchone()
             # XZ, 05/08/2009: We also should use Geno.Id to find marker instead of just using Geno.Name
@@ -442,20 +441,20 @@ def retrieve_trait_info(trait, dataset, get_qtl_info=False):
                 display_fields_string = ',Geno.'.join(dataset.display_fields)
                 display_fields_string = f'Geno.{display_fields_string}'
                 cursor.execute(
-                    "SELECT %s FROM Geno, GenoFreeze, "
+                    f"SELECT {display_fields_string} FROM Geno, GenoFreeze, "
                     "GenoXRef WHERE "
                     "GenoXRef.GenoFreezeId = GenoFreeze.Id "
                     "AND GenoXRef.GenoId = Geno.Id "
                     "AND GenoFreeze.Name = %s "
                     "AND Geno.Name = %s",
-                    (display_fields_string, dataset.name, trait.name)
+                    (dataset.name, trait.name)
                 )
                 trait_info = cursor.fetchone()
             else:  # Temp type
                 cursor.execute(
-                    "SELECT %s FROM %s WHERE Name = %s",
-                    (','.join(dataset.display_fields),
-                     dataset.type, trait.name,)
+                    f"SELECT {','.join(dataset.display_fields)} "
+                    f"FROM {dataset.type} WHERE Name = %s",
+                    (trait.name,)
                 )
                 trait_info = cursor.fetchone()
 
-- 
cgit v1.2.3