From 6efb3cbdc08f93685dff481635a9e4a9997f8017 Mon Sep 17 00:00:00 2001 From: jasplin Date: Fri, 18 Mar 2011 13:55:53 +0100 Subject: Make SQL queries safer and more robust. This patch ensures proper processing of SQL queries by making use of the second argument to the Psycopg execute() method: WRONG: >>> SQL = "INSERT INTO authors (name) VALUES ('%s');" # NEVER DO THIS >>> data = ("O'Reilly", ) >>> cur.execute(SQL % data) # THIS WILL FAIL MISERABLY CORRECT: >>> SQL = "INSERT INTO authors (name) VALUES (%s);" # Notice: no quotes >>> data = ("O'Reilly", ) >>> cur.execute(SQL, data) # Notice: no % operator This has a double purpose: 1: Arguments are automatically handled properly according to their type (in particular strings containing single quotes). 2: SQL injection is prevented. --- scripts/computerankings.py | 24 +++---- scripts/dbaccess.py | 10 +-- scripts/getrankings.py | 30 ++++---- scripts/getresultdetails2.py | 10 +-- scripts/getstats1.py | 5 +- scripts/getstats2.py | 8 +-- scripts/gettimeseriesdetails.py | 2 +- scripts/gettimeseriesstats.py | 2 +- scripts/listcontexts.py | 8 ++- scripts/listtestcases1.py | 7 +- scripts/listtestcases2.py | 11 ++- scripts/misc.py | 156 ++++++++++++++++++++-------------------- scripts/settimeseriesnote.py | 28 ++++---- scripts/singlecontextbmstats.py | 11 +-- scripts/uploadresults.py | 42 +++++------ 15 files changed, 177 insertions(+), 177 deletions(-) (limited to 'scripts') diff --git a/scripts/computerankings.py b/scripts/computerankings.py index 6726806..e5165ce 100755 --- a/scripts/computerankings.py +++ b/scripts/computerankings.py @@ -115,19 +115,11 @@ def registerRanking(table, stat_index, stat_name, context1_id, context2_id): prev_stat_value = stat_value # Insert or update the corresponding row in the 'ranking' table: - query = ( - "SELECT merge_ranking(" - + str(context1_id) - + ", " + str(context2_id) - + ", " + str(benchmark_id) - + ", " + str(metric_id) - + ", " + str(lc_timestamp) - + ", " + str(stat_id) - + ", " + str(stat_value) - + ", " + str(ranking_pos) - + ");" - ) - execQuery(query, False) + execQuery( + "SELECT merge_ranking(%s, %s, %s, %s, %s, %s, %s, %s)", + (context1_id, context2_id, benchmark_id, metric_id, + lc_timestamp, stat_id, stat_value, ranking_pos), + False) # ### 2 B DOCUMENTED! @@ -255,13 +247,13 @@ def updateRankings( try: sha12_pos = zip(*snapshots)[0].index(sha12_id) except ValueError: - print "no observations found for SHA-1 ID:", sha12_id + sys.stderr.write("no observations found for SHA-1 ID: " + str(sha12_id)) sys.exit(1) sha11_pos = max(0, (sha12_pos - 2 * durtolmax) + 1) snapshots = snapshots[sha11_pos:(sha12_pos + 1)] if len(snapshots) < 2: - print ( - "no observations found before SHA-1 ID: " + sha12_id + + sys.stderr.write( + "no observations found before SHA-1 ID: " + str(sha12_id) + " (computing rankings makes no sense)") sys.exit(1) diff --git a/scripts/dbaccess.py b/scripts/dbaccess.py index d18346e..c12e186 100644 --- a/scripts/dbaccess.py +++ b/scripts/dbaccess.py @@ -47,15 +47,16 @@ def connectDatabase(): global cursor cursor = conn.cursor() -# Executes a query against the database. Returns any result set iff -# fetch_results is true. -def execQuery(query, fetch_results = True): + +# Executes a query against the database. args contains the arguments to be +# passed to the query. Returns any result set iff fetch_results is true. +def execQuery(query, args, fetch_results = True): if not "cursor" in globals(): connectDatabase() assert "cursor" in globals() try: - cursor.execute(query) + cursor.execute(query, args) if fetch_results: return cursor.fetchall() except psycopg2.Error: @@ -63,6 +64,7 @@ def execQuery(query, fetch_results = True): print "reason:", str(sys.exc_info()) sys.exit(1) + # Commits everything that has been written to the database. def commit(): if not "cursor" in globals(): diff --git a/scripts/getrankings.py b/scripts/getrankings.py index 02a1f9f..45cc9a3 100644 --- a/scripts/getrankings.py +++ b/scripts/getrankings.py @@ -63,15 +63,15 @@ class GetRankings: # Get all time series notes: qres = execQuery( "SELECT benchmarkId, metricId, note FROM timeSeriesAnnotation" - " WHERE hostId = %d AND platformId = %d AND branchId = %d" - % (self.host_id, self.platform_id, self.branch_id)) + " WHERE hostId = %s AND platformId = %s AND branchId = %s", + (self.host_id, self.platform_id, self.branch_id)) notes = {} for benchmark_id, metric_id, note in qres: notes[benchmark_id, metric_id] = note # Get rankings for each statistic: - stat_infos = execQuery("SELECT id, value FROM rankingStat;") + stat_infos = execQuery("SELECT id, value FROM rankingStat", ()) for stat_id, stat_name in stat_infos: # Get the unsorted ranking information: @@ -79,9 +79,9 @@ class GetRankings: "SELECT benchmarkId, metricId, context1Id, pos, value," " lastChangeTimestamp" " FROM ranking" - " WHERE context2Id = %d" - " AND statId = %d;" - % (self.context2_id, stat_id)) + " WHERE context2Id = %s" + " AND statId = %s", + (self.context2_id, stat_id)) ranking = [] @@ -121,9 +121,9 @@ class GetRankings: ranking_prev_list = execQuery( "SELECT benchmarkId, metricId, pos" " FROM ranking" - " WHERE context2Id = %d" - " AND statId = %d;" - % (context2_prev_id, stat_id)) + " WHERE context2Id = %s" + " AND statId = %s", + (context2_prev_id, stat_id)) ranking_prev = {} for benchmark_id, metric_id, pos in ranking_prev_list: ranking_prev[benchmark_id, metric_id] = pos @@ -152,12 +152,14 @@ class GetRankings: # Extract affected SHA-1s: + assert len(context_ids) > 0 sha1_infos = execQuery( "SELECT context.id, sha1Id, sha1.value" " FROM context, sha1" - " WHERE context.id IN (%s)" - " AND sha1Id = sha1.id;" - % (", ".join(map(str, context_ids)))) + " WHERE context.id IN" + " (%s" + ", %s"*(len(context_ids) - 1) + ")" + + " AND sha1Id = sha1.id", + tuple(context_ids)) return sha1_infos, rankings @@ -191,8 +193,8 @@ class GetRankings: self.sha1_infos, self.rankings = self.getRankings() self.snapshots = self.getSnapshotsInMaxRange(self.sha1_infos) - self.benchmarks = execQuery("SELECT id, value FROM benchmark;") - self.metrics = execQuery("SELECT id, value FROM metric;") + self.benchmarks = execQuery("SELECT id, value FROM benchmark", ()) + self.metrics = execQuery("SELECT id, value FROM metric", ()) self.writeOutput() diff --git a/scripts/getresultdetails2.py b/scripts/getresultdetails2.py index 20d128e..3b5f2b3 100644 --- a/scripts/getresultdetails2.py +++ b/scripts/getresultdetails2.py @@ -50,11 +50,11 @@ class GetResultDetails2: "SELECT value, valid, EXTRACT(EPOCH FROM startTime)::INT" " FROM result, upload" " WHERE uploadId = upload.id" - " AND contextId = %d" - " AND benchmarkId = %d" - " AND metricId = %d" - " ORDER BY value DESC;" - % (context, benchmark, metric)): + " AND contextId = %s" + " AND benchmarkId = %s" + " AND metricId = %s" + " ORDER BY value DESC", + (context, benchmark, metric)): sample.append({ 'value' : value, 'valid' : valid, diff --git a/scripts/getstats1.py b/scripts/getstats1.py index 89da8cf..f9e6005 100644 --- a/scripts/getstats1.py +++ b/scripts/getstats1.py @@ -89,8 +89,9 @@ class GetStats1: self.host_id, self.platform_id, self.branch_id, self.sha1_id) # Get all distinct benchmark/metric combinations matching the context: - bmark_metrics = execQuery("SELECT DISTINCT benchmarkId, metricId" - " FROM result WHERE contextId = %d;" % context_id) + bmark_metrics = execQuery( + "SELECT DISTINCT benchmarkId, metricId" + " FROM result WHERE contextId = %s", (context_id,)) bmstats_list = [] diff --git a/scripts/getstats2.py b/scripts/getstats2.py index eac6833..f7fe6c6 100644 --- a/scripts/getstats2.py +++ b/scripts/getstats2.py @@ -181,14 +181,14 @@ class GetStats2: bmark_metrics = execQuery( "SELECT DISTINCT benchmarkId, metricId" " FROM result" - " WHERE contextId = %d" + " WHERE contextId = %s" " INTERSECT " "SELECT DISTINCT benchmarkId, metricId" " FROM result" - " WHERE contextId = %d" + " WHERE contextId = %s" #" LIMIT 10" - ";" - % (context1_id, context2_id) + ";", + (context1_id, context2_id) ) bmstats_list = [] diff --git a/scripts/gettimeseriesdetails.py b/scripts/gettimeseriesdetails.py index 56e7a1a..621c98c 100644 --- a/scripts/gettimeseriesdetails.py +++ b/scripts/gettimeseriesdetails.py @@ -1,6 +1,6 @@ import sys import json -from dbaccess import execQuery, database +from dbaccess import database from misc import ( idToText, textToId, metricIdToLowerIsBetter, benchmarkToComponents, getSnapshots, getTimeSeries, getChanges, getTimeSeriesMiscStats, diff --git a/scripts/gettimeseriesstats.py b/scripts/gettimeseriesstats.py index ea1294c..6b95080 100644 --- a/scripts/gettimeseriesstats.py +++ b/scripts/gettimeseriesstats.py @@ -1,6 +1,6 @@ import sys import json -from dbaccess import execQuery, database +from dbaccess import database from misc import ( idToText, textToId, metricIdToLowerIsBetter, benchmarkToComponents, getSnapshots, getBMTimeSeriesStatsList, printJSONHeader) diff --git a/scripts/listcontexts.py b/scripts/listcontexts.py index 01c2403..4aae8bc 100644 --- a/scripts/listcontexts.py +++ b/scripts/listcontexts.py @@ -12,7 +12,8 @@ class ListContexts: def rankingsExist(self, contextId): rankings = execQuery( - "SELECT id FROM ranking WHERE context2Id = %d LIMIT 1;" % contextId) + "SELECT id FROM ranking WHERE context2Id = %s LIMIT 1", + (contextId,)) return len(rankings) > 0 @@ -23,8 +24,9 @@ class ListContexts: "SELECT id, hostId, platformId, branchId, sha1Id," " EXTRACT(EPOCH FROM timestamp)::INT" " FROM context" - " ORDER BY hostId, platformId, branchId, timestamp DESC;") \ - + [(-1, -1, -1, -1, -1, -1)] # Sentinel value + " ORDER BY hostId, platformId," + " branchId, timestamp DESC;", ()) \ + + [(-1, -1, -1, -1, -1, -1)] # Sentinel value self.contexts = [] diff --git a/scripts/listtestcases1.py b/scripts/listtestcases1.py index 9e50cab..90f1cc5 100644 --- a/scripts/listtestcases1.py +++ b/scripts/listtestcases1.py @@ -21,9 +21,10 @@ class ListTestCases1: # Get all distinct benchmarks matching the context: bmark_ids = execQuery( "SELECT DISTINCT benchmarkId " - "FROM result WHERE contextId = %d;" - % getContext( - self.host_id, self.platform_id, self.branch_id, self.sha1_id)) + "FROM result WHERE contextId = %s", + (getContext( + self.host_id, self.platform_id, self.branch_id, + self.sha1_id),)) # Extract all distinct test case components: tc_map = {} diff --git a/scripts/listtestcases2.py b/scripts/listtestcases2.py index 9cdcbda..020dcd2 100644 --- a/scripts/listtestcases2.py +++ b/scripts/listtestcases2.py @@ -31,17 +31,16 @@ class ListTestCases2: # Get all distinct benchmarks matching both contexts: bmark_ids = execQuery( "SELECT DISTINCT benchmarkId" - " FROM result WHERE contextId = %d" + " FROM result WHERE contextId = %s" " INTERSECT " "SELECT DISTINCT benchmarkId" - " FROM result WHERE contextId = %d;" - % (getContext( + " FROM result WHERE contextId = %s", + (getContext( self.host1_id, self.platform1_id, self.branch1_id, self.sha11_id), - getContext( + getContext( self.host2_id, self.platform2_id, self.branch2_id, - self.sha12_id) - ) + self.sha12_id)) ) # Extract all distinct test case components: diff --git a/scripts/misc.py b/scripts/misc.py index 8d0be5c..e5663c3 100644 --- a/scripts/misc.py +++ b/scripts/misc.py @@ -15,14 +15,15 @@ def idToText(table, id_): idToTextCache[table] = {} if id_ in idToTextCache[table]: - return idToTextCache[table][id_]; + return idToTextCache[table][id_] - text = ((execQuery( - "SELECT value FROM " + table + " WHERE id = " + str(id_) + - ";")[0][0]) if (id_ >= 0) else "") - idToTextCache[table][id_] = text; + text = (execQuery( + "SELECT value FROM " + table + " WHERE id = %s", (id_,))[0][0] + if (id_ >= 0) else "") + idToTextCache[table][id_] = text + + return text - return text; # ### 2 B DOCUMENTED! def textToId(table, text): @@ -33,15 +34,15 @@ def textToId(table, text): textToIdCache[table] = {} if text in textToIdCache[table]: - return textToIdCache[table][text]; + return textToIdCache[table][text] + + res = execQuery("SELECT id FROM " + table + " WHERE value = %s", (text,)) + id_ = res[0][0] if (len(res) > 0) else -1 - res = execQuery( - "SELECT id FROM " + table + " WHERE value = '" + str(text) + "';") - id_ = res[0][0] if (len(res) > 0) else -1; + textToIdCache[table][text] = id_ - textToIdCache[table][text] = id_; + return id_ - return id_; # ### 2 B DOCUMENTED! # Maybe also rename to lowerIsBetter() ? (but note that a global function with @@ -52,28 +53,30 @@ def metricIdToLowerIsBetter(metric_id): metricIdToLIBCache = {} if metric_id in metricIdToLIBCache: - return metricIdToLIBCache[metric_id]; + return metricIdToLIBCache[metric_id] lib = execQuery( - "SELECT lowerIsBetter FROM metric WHERE id = " + str(metric_id) + - ";")[0][0] - metricIdToLIBCache[metric_id] = lib; + "SELECT lowerIsBetter FROM metric WHERE id = %s", (metric_id,))[0][0] + metricIdToLIBCache[metric_id] = lib + + return lib - return lib; # Returns the non-negative ID of the given context, or -1 if not found. def getContext(host_id, platform_id, branch_id, sha1_id): - result = execQuery("SELECT id FROM context" - " WHERE hostId = %d" - " AND platformId = %d" - " AND branchId = %d" - " AND sha1Id = %d" - "LIMIT 1;" - % (host_id, platform_id, branch_id, sha1_id)) + result = execQuery( + "SELECT id FROM context" + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s" + " AND sha1Id = %s" + "LIMIT 1", + (host_id, platform_id, branch_id, sha1_id)) if len(result): return result[0][0] return -1 + # Returns the test case, test function, and data tag components of # a benchmark of the form :(
). The test case and test function # components may not contain whitespace, or a ':', '(', or ')' character. @@ -86,12 +89,13 @@ def benchmarkToComponents(benchmark): else: raise BaseException("invalid benchmark syntax: >" + benchmark + "<") + +# Returns the timestamp associated with a particular context ID. This will +# be the UTC timestamp of the earliest upload for this context. def getTimestampFromContext(context_id): return execQuery( - "SELECT EXTRACT(EPOCH FROM timestamp)::INT" - " FROM context" - " WHERE id = %d;" - % context_id)[0][0] + "SELECT EXTRACT(EPOCH FROM timestamp)::INT FROM context WHERE id = %s", + (context_id,))[0][0] # Finds snapshots that match a host/platform/branch combination and that @@ -102,33 +106,27 @@ def getTimestampFromContext(context_id): # (sha1, first upload timestamp). def getSnapshots(host_id, platform_id, branch_id, sha11_id, sha12_id): timestamp1 = execQuery( - "SELECT EXTRACT(EPOCH FROM timestamp)::INT FROM context " + - "WHERE hostId = " + str(host_id) + - " AND platformId = " + str(platform_id) + - " AND branchId = " + str(branch_id) + - " AND sha1Id = " + str(sha11_id) + ";")[0][0] - + "SELECT EXTRACT(EPOCH FROM timestamp)::INT FROM context" + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s" + " AND sha1Id = %s", + (host_id, platform_id, branch_id, sha11_id))[0][0] if sha12_id >= 0: - timestamp2 = execQuery( - "SELECT EXTRACT(EPOCH FROM timestamp)::INT FROM context " + - "WHERE hostId = " + str(host_id) + - " AND platformId = " + str(platform_id) + - " AND branchId = " + str(branch_id) + - " AND sha1Id = " + str(sha12_id) + ";")[0][0] - + "SELECT EXTRACT(EPOCH FROM timestamp)::INT FROM context" + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s" + " AND sha1Id = %s", + (host_id, platform_id, branch_id, sha12_id))[0][0] # Ensure chronological order: if timestamp1 > timestamp2: timestamp1, timestamp2 = timestamp2, timestamp1 - range_expr = "BETWEEN %d AND %d" % (timestamp1, timestamp2) - else: - range_expr = ">= %d" % timestamp1 - - # Each distinct SHA-1 that occurs for this host/platform/branch # combination may occur multiple times with different upload times. # Get the list of distinct SHA-1 IDs along with the earliest upload @@ -136,14 +134,14 @@ def getSnapshots(host_id, platform_id, branch_id, sha11_id, sha12_id): # order of the uploadId attributes is consistent with the order # of their corresponding startTime attributes): snapshots = execQuery( - "SELECT sha1Id, EXTRACT(EPOCH FROM timestamp)::INT AS " - "firstUploadTime FROM context " - "WHERE hostId = %d" - " AND platformId = %d" - " AND branchId = %d" - " AND EXTRACT(EPOCH FROM timestamp)::INT %s" - " ORDER BY timestamp ASC;" - % (host_id, platform_id, branch_id, range_expr)) + "SELECT sha1Id, EXTRACT(EPOCH FROM timestamp)::INT AS firstUploadTime" + " FROM context" + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s" + " AND EXTRACT(EPOCH FROM timestamp)::INT " + range_expr + + " ORDER BY timestamp ASC", + (host_id, platform_id, branch_id)) return tuple(snapshots) @@ -151,7 +149,6 @@ def getSnapshots(host_id, platform_id, branch_id, sha11_id, sha12_id): # Finds all snapshots matching a host/platform/branch combination. # Returns a chronologically (except when reverse is True) ordered n-tuple # of 2-tuples: (sha1, first upload timestamp). -# def getAllSnapshots(host_id, platform_id, branch_id, reverse = False): # Each distinct SHA-1 that occurs for this host/platform/branch @@ -163,11 +160,11 @@ def getAllSnapshots(host_id, platform_id, branch_id, reverse = False): snapshots = execQuery( "SELECT sha1Id, EXTRACT(EPOCH FROM timestamp)::INT AS firstUploadTime" " FROM context" - " WHERE hostId = %d" - " AND platformId = %d" - " AND branchId = %d" - % (host_id, platform_id, branch_id) + - " ORDER BY timestamp " + ("DESC;" if reverse else "ASC;")) + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s" + " ORDER BY timestamp " + ("DESC" if reverse else "ASC"), + (host_id, platform_id, branch_id)) return tuple(snapshots) @@ -181,12 +178,12 @@ def getLastRankingSnapshot(host_id, platform_id, branch_id): " FROM ranking," " (SELECT id, sha1Id, timestamp" " FROM context" - " WHERE hostId = %d" - " AND platformId = %d" - " AND branchId = %d) AS matchingContext" + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s) AS matchingContext" " WHERE context2Id = matchingContext.id" - " ORDER BY timestamp DESC LIMIT 1;" - % (host_id, platform_id, branch_id)) + " ORDER BY timestamp DESC LIMIT 1", + (host_id, platform_id, branch_id)) if len(result): return result[0] return -1, -1 @@ -203,12 +200,12 @@ def getRankingContexts(host_id, platform_id, branch_id): " FROM ranking," " (SELECT id, sha1Id, timestamp" " FROM context" - " WHERE hostId = %d" - " AND platformId = %d" - " AND branchId = %d) AS matchingContext" + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s) AS matchingContext" " WHERE context2Id = matchingContext.id" - " ORDER BY etimestamp DESC;" - % (host_id, platform_id, branch_id)) + " ORDER BY etimestamp DESC", + (host_id, platform_id, branch_id)) return result @@ -251,13 +248,14 @@ def getTimeSeries( contexts.append(getContext(host_id, platform_id, branch_id, sha1_id)) # Fetch raw values: + assert len(contexts) > 0 raw_values = (execQuery( "SELECT value, valid, contextId FROM result" - " WHERE contextId IN (%s)" - " AND benchmarkId = %d" - " AND metricId = %d" - " ORDER BY contextId;" - % (", ".join(map(str, contexts)), benchmark_id, metric_id)) + + " WHERE contextId IN (%s" + ", %s"*(len(contexts) - 1) + ")" + + " AND benchmarkId = %s" + " AND metricId = %s" + " ORDER BY contextId", + tuple(contexts) + (benchmark_id, metric_id)) + [(-1, -1, -1)]) # Note sentinel item # Compute per-sample stats: @@ -587,9 +585,11 @@ def getBMTimeSeriesStatsList( # Get all distinct benchmark/metric combinations that match the # host/platform/branch context and are within the selected snapshot # interval. Each such combination corresponds to a time series. - bmark_metrics = execQuery("SELECT DISTINCT benchmarkId, metricId" - " FROM result WHERE contextId IN (%s);" - % ", ".join(map(str, contexts))) + assert len(contexts) > 0 + bmark_metrics = execQuery( + "SELECT DISTINCT benchmarkId, metricId FROM result" + " WHERE contextId IN (%s" + ", %s"*(len(contexts) - 1) + ")", + contexts) bmstats_list = [] diff --git a/scripts/settimeseriesnote.py b/scripts/settimeseriesnote.py index 7e4aa5b..6d815e9 100644 --- a/scripts/settimeseriesnote.py +++ b/scripts/settimeseriesnote.py @@ -19,23 +19,23 @@ class SetTimeSeriesNote: if len(self.note) == 0: # Delete the row from the table: - query = ( + execQuery( "DELETE FROM timeSeriesAnnotation" - " WHERE hostId = %d" - " AND platformId = %d" - " AND branchId = %d" - " AND benchmarkId = %d" - " AND metricId = %d;" - % (self.host_id, self.platform_id, self.branch_id, - self.benchmark_id, self.metric_id)) - execQuery(query, False) + " WHERE hostId = %s" + " AND platformId = %s" + " AND branchId = %s" + " AND benchmarkId = %s" + " AND metricId = %s", + (self.host_id, self.platform_id, self.branch_id, + self.benchmark_id, self.metric_id), + False) else: # Insert or update table row: - query = ( - "SELECT merge_timeSeriesNote(%d, %d, %d, %d, %d, '%s');" - % (self.host_id, self.platform_id, self.branch_id, - self.benchmark_id, self.metric_id, self.note)) - execQuery(query, False) + execQuery( + "SELECT merge_timeSeriesNote(%s, %s, %s, %s, %s, %s)", + (self.host_id, self.platform_id, self.branch_id, + self.benchmark_id, self.metric_id, self.note), + False) # Write to database: commit() diff --git a/scripts/singlecontextbmstats.py b/scripts/singlecontextbmstats.py index 665a44c..f8d2858 100644 --- a/scripts/singlecontextbmstats.py +++ b/scripts/singlecontextbmstats.py @@ -63,11 +63,12 @@ def extractSingleContextBMStats(context_id, benchmark_id, metric_id): values = [] - query_result = execQuery("SELECT value, valid FROM result" - " WHERE contextId = %d" - " AND benchmarkId = %d" - " AND metricId = %d;" - % (context_id, benchmark_id, metric_id)) + query_result = execQuery( + "SELECT value, valid FROM result" + " WHERE contextId = %s" + " AND benchmarkId = %s" + " AND metricId = %s;", + (context_id, benchmark_id, metric_id)) for value, valid in query_result: if valid: diff --git a/scripts/uploadresults.py b/scripts/uploadresults.py index 460207f..f0d5bb3 100755 --- a/scripts/uploadresults.py +++ b/scripts/uploadresults.py @@ -171,7 +171,7 @@ def findOrInsertId(table, value, *args): #print "value: >" + value + "<, ", query_result = execQuery( - "SELECT id FROM " + table + " WHERE value = '" + str(value) + "';") + "SELECT id FROM " + table + " WHERE value = %s", (value,)) if len(query_result) == 1: # Found, so return ID: #print "returning existing ID: >" + str(query_result[0][0]) + "<" @@ -180,14 +180,16 @@ def findOrInsertId(table, value, *args): # Not found, so insert: query = "INSERT INTO " + table + " (value" for i in range(0, len(args), 2): - query += ", " + str(args[i]) - query += ") VALUES ('" + str(value) + "'" + query += ", " + args[i] + query += ") VALUES (%s" + values = [value] for i in range(0, len(args), 2): - query += ", " + str(args[i + 1]) + query += ", %s" + values.append(args[i + 1]) # ... and retrieve ID: - query += ") RETURNING id;" - query_result = execQuery(query) + query += ") RETURNING id" + query_result = execQuery(query, values) assert len(query_result) == 1 #print "returning new ID: >" + str(query_result[0][0]) + "<" @@ -198,10 +200,10 @@ def findOrInsertId(table, value, *args): def uploadToDatabase(host, platform, branch, sha1, results): # Append a row to the 'upload' table (to record this upload event) ... - execQuery("INSERT INTO upload DEFAULT VALUES;", False) + execQuery("INSERT INTO upload DEFAULT VALUES", (), False) # Retrieve the ID of the row we just inserted ... - uploadId = execQuery("SELECT currval('upload_id_seq');")[0][0] + uploadId = execQuery("SELECT currval('upload_id_seq')", ())[0][0] hostId = findOrInsertId("host", host) platformId = findOrInsertId("platform", platform) @@ -213,9 +215,9 @@ def uploadToDatabase(host, platform, branch, sha1, results): contextId = execQuery( "INSERT INTO context" " (hostId, platformId, branchId, sha1Id)" - " VALUES (%d, %d, %d, %d)" - " RETURNING id;" - % (hostId, platformId, branchId, sha1Id))[0][0] + " VALUES (%s, %s, %s, %s)" + " RETURNING id", + (hostId, platformId, branchId, sha1Id))[0][0] # Append rows to the 'result' table ... for result in results: @@ -228,14 +230,12 @@ def uploadToDatabase(host, platform, branch, sha1, results): "metric", result['metric'], "lowerIsBetter", result['lowerIsBetter']) - query = ( + execQuery( "INSERT INTO result" " (contextId, benchmarkId, value, valid, metricId, uploadId)" - " VALUES (%d, %d, %f, %s, %d, %d);" - % (contextId, benchmarkId, result['value'], result['valid'], - metricId, uploadId)) - - execQuery(query, False) + " VALUES (%s, %s, %s, %s, %s, %s)", + (contextId, benchmarkId, result['value'], result['valid'], + metricId, uploadId), False) # Write to database: commit() @@ -250,7 +250,7 @@ def rankingsExist(options): textToId('sha1', options["sha1"])) matches = execQuery( - "SELECT id FROM ranking WHERE context2Id = %d LIMIT 1;" % context_id) + "SELECT id FROM ranking WHERE context2Id = %s LIMIT 1", (context_id,)) return len(matches) > 0 @@ -278,8 +278,8 @@ def contextComplete(options): context_id = getContextIdFromNames(options) sample_size = execQuery( "SELECT count(*) FROM" - " (SELECT DISTINCT uploadId from result where contextId=%d) AS foo;" - % context_id)[0][0] + " (SELECT DISTINCT uploadId from result where contextId = %s) AS foo", + (context_id,))[0][0] return sample_size >= max_sample_size @@ -321,7 +321,7 @@ def execComputeRankings(options, new_context): p = Popen(cmd, stdout = PIPE, stderr = PIPE) stdout, stderr = p.communicate() if (p.returncode != 0): - print "failed to execute command '" + cmd + "':" + print "failed to execute command '" + str(cmd) + "':" print " return code:", p.returncode print " stdout: >%s<" % stdout.strip() print " stderr: >%s<" % stderr.strip() -- cgit v1.2.3