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.

5 files changed, 20 insertions, 23 deletions

diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py
index aac8585e..470aa28b 100644
--- a/wqflask/base/data_set.py
+++ b/wqflask/base/data_set.py
@@ -1218,8 +1218,8 @@ def geno_mrna_confidentiality(ob):
     with database_connection() as conn, conn.cursor() as cursor:
         cursor.execute(
             "SELECT confidentiality, "
-            "AuthorisedUsers FROM %s WHERE Name = %s",
-            (f"{ob.type}Freeze", ob.name,)
+            f"AuthorisedUsers FROM {ob.type}Freeze WHERE Name = %s",
+            (ob.name,)
         )
         result = cursor.fetchall()[0]
         if result:
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()
 
diff --git a/wqflask/wqflask/gsearch.py b/wqflask/wqflask/gsearch.py
index cd3724d9..dac3b917 100644
--- a/wqflask/wqflask/gsearch.py
+++ b/wqflask/wqflask/gsearch.py
@@ -205,9 +205,9 @@ class GSearch:
                     "Phenotype.Lab_code) AGAINST (%s IN BOOLEAN MODE) ) "
                     "OR (MATCH (Publication.Abstract, Publication.Title, "
                     "Publication.Authors) AGAINST (%s IN BOOLEAN MODE) ) "
-                    ") %s ORDER BY Species.`Name`, InbredSet.`Name`, "
-                    "PublishXRef.`Id` LIMIT 6000",
-                    (search_term, search_term, group_clause,)
+                    f") {group_clause} ORDER BY Species.`Name`, "
+                    "InbredSet.`Name`, PublishXRef.`Id` LIMIT 6000",
+                    ((search_term,)*2)
                 )
                 _result = cursor.fetchall()
             trait_list = []
diff --git a/wqflask/wqflask/interval_analyst/GeneUtil.py b/wqflask/wqflask/interval_analyst/GeneUtil.py
index 6adf528f..9e56f66b 100644
--- a/wqflask/wqflask/interval_analyst/GeneUtil.py
+++ b/wqflask/wqflask/interval_analyst/GeneUtil.py
@@ -13,7 +13,7 @@ def loadGenes(chrName, diffCol, startMb, endMb, species='mouse'):
     speciesDict = {}
     results = []
     with database_connection() as conn, conn.cursor() as cursor:
-        cursor.execute("SELECT Species.Name, GeneList.SpeciesId"
+        cursor.execute("SELECT Species.Name, GeneList.SpeciesId "
                        "FROM Species, GeneList WHERE "
                        "GeneList.SpeciesId = Species.Id "
                        "GROUP BY GeneList.SpeciesId")
@@ -25,14 +25,13 @@ def loadGenes(chrName, diffCol, startMb, endMb, species='mouse'):
         speciesId = speciesDict[species]
         otherSpecies = [[X, speciesDict[X]] for X in list(speciesDict.keys())]
         otherSpecies.remove([species, speciesId])
-        cursor.execute("SELECT %s FROM GeneList "
-                       "WHERE SpeciesId = %d AND "
+        cursor.execute(f"SELECT {', '.join(fetchFields)} FROM GeneList "
+                       "WHERE SpeciesId = %s AND "
                        "Chromosome = %s AND "
                        "((TxStart > %s and TxStart <= %s) "
                        "OR (TxEnd > %s and TxEnd <= %s)) "
                        "ORDER BY txStart",
-                       (", ".join(fetchFields),
-                        speciesId, chrName,
+                       (speciesId, chrName,
                         startMb, endMb,
                         startMb, endMb))
         results = cursor.fetchall()
@@ -69,11 +68,10 @@ def loadGenes(chrName, diffCol, startMb, endMb, species='mouse'):
                     othSpec, othSpecId = item
                     newdict2 = {}
                     cursor.execute(
-                        "SELECT %s FROM GeneList WHERE "
+                        f"SELECT {', '.join(fetchFields)} FROM GeneList WHERE "
                         "SpeciesId = %s AND "
                         "geneSymbol= '%s' LIMIT 1",
-                        (", ".join(fetchFields),
-                         othSpecId,
+                        (othSpecId,
                          newdict["GeneSymbol"]))
                     resultsOther = cursor.fetchone()
                     if resultsOther:
diff --git a/wqflask/wqflask/snp_browser/snp_browser.py b/wqflask/wqflask/snp_browser/snp_browser.py
index cd47f293..0dfa3e64 100644
--- a/wqflask/wqflask/snp_browser/snp_browser.py
+++ b/wqflask/wqflask/snp_browser/snp_browser.py
@@ -249,9 +249,9 @@ class SnpBrowser:
                         "a.Mb_start, a.Mb_end, a.Strand, a.Type, a.Size, "
                         "a.InDelSequence, b.Name FROM IndelAll a, "
                         "SnpSource b WHERE a.Chromosome = %s AND "
-                        "a.Mb_start >= %2.6f AND a.Mb_start < (%2.6f+.0010) "
+                        "a.Mb_start >= %s AND a.Mb_start < %s "
                         "AND b.Id = a.SourceId ORDER BY a.Mb_start")
-                    __vars = (self.chr, f"{self.start_mb:2.6f}",
+                    __vars = (self.chr, f"{self.start_mb+0.0010:2.6f}",
                               f"{self.end_mb+0.0010:2.6f}",)
             cursor.execute(__query, __vars)
             return self.filter_results(cursor.fetchall())