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/data_set.py | 4 ++-- wqflask/base/trait.py | 15 +++++++-------- wqflask/wqflask/gsearch.py | 6 +++--- wqflask/wqflask/interval_analyst/GeneUtil.py | 14 ++++++-------- wqflask/wqflask/snp_browser/snp_browser.py | 4 ++-- 5 files changed, 20 insertions(+), 23 deletions(-) (limited to 'wqflask') 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()) -- cgit v1.2.3