Modified escape function and used it in more places

1 files changed, 10 insertions, 14 deletions

diff --git a/weblog-model.lua b/weblog-model.lua
index bb1bff1..0128c47 100644
--- a/weblog-model.lua
+++ b/weblog-model.lua
@@ -64,7 +64,7 @@ end
 local escape = function(sql, length)
 	sql = sql or ""
 	if length then sql = string.sub(sql, 1, length) end
-	return string.gsub(sql, "'", "''")
+	return con:escape(sql)
 end
 
 -- List the postgres databases on this system
@@ -273,11 +273,11 @@ local groompublogs = function()
 	logme("Purgedate is " .. temp .. ". Nothing will exist in pubweblog from before purgedate.")
 
 	-- Move flagged records to histoy and then purge anything older than purgedate
-	sql = "Insert into pubweblog_history select * from pubweblog where logdatetime < '" .. temp .."' and (badyesno > 0 or deniedyesno > 0 or bypassyesno > 0 or selected = 'true')"
+	sql = "Insert into pubweblog_history select * from pubweblog where logdatetime < '" .. escape(temp) .."' and (badyesno > 0 or deniedyesno > 0 or bypassyesno > 0 or selected = 'true')"
 	res = assert (con:execute(sql))
 	logme("Moved " .. res .. " old records to history")
 
-	sql = "Delete from pubweblog where logdatetime < '" .. temp .."'"
+	sql = "Delete from pubweblog where logdatetime < '" .. escape(temp) .."'"
 	res = assert (con:execute(sql))
 	logme("Deleted " .. res .. " old records from pubweblog")
 	
@@ -330,9 +330,9 @@ end
 local listlogentries = function(activelog, clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, sortby, selected)
 	local entries = {}
 	-- retrieve a cursor
-	local sql = "SELECT * FROM "..activelog
+	local sql = "SELECT * FROM "..escape(activelog)
 	sql = sql .. generatewhereclause(clientuserid, starttime, endtime, clientip, badyesno, deniedyesno, bypassyesno, score, urisearch, selected)
-	sql = sql .. " ORDER BY "..sortby
+	sql = sql .. " ORDER BY "..escape(sortby)
 	cur = assert (con:execute(sql))
 	row = cur:fetch ({}, "a")
 	while row do
@@ -354,16 +354,12 @@ local groupflaggedlogentries = function(starttime, endtime, groupby)
 	groupby = groupby or "clientuserid"
 	local entries = {}
 	-- retrieve a cursor
-	--local sql = "SELECT "..groupby..", count(*) AS numblock, max(score) AS maxscore FROM pubweblog"
-	local sql = "SELECT "..groupby..", COUNT(*) as numrecords, SUM(CASE WHEN (bypassyesno > '0' OR deniedyesno > '0' OR badyesno > '0') THEN 1 ELSE 0 END) as numflagged, sum(score) AS numhits, sum(CASE WHEN deniedyesno > '0' THEN 1 ELSE 0 END) AS numdenied, sum(CASE WHEN bypassyesno > '0' THEN 1 ELSE 0 END) AS numbypassed, max(score) as maxscore from pubweblog"
-	--sql = sql .. generatewhereclause(nil, starttime, endtime) .. " AND deniedyesno > '0'"
+	local sql = "SELECT "..escape(groupby)..", COUNT(*) as numrecords, SUM(CASE WHEN (bypassyesno > '0' OR deniedyesno > '0' OR badyesno > '0') THEN 1 ELSE 0 END) as numflagged, sum(score) AS numhits, sum(CASE WHEN deniedyesno > '0' THEN 1 ELSE 0 END) AS numdenied, sum(CASE WHEN bypassyesno > '0' THEN 1 ELSE 0 END) AS numbypassed, max(score) as maxscore from pubweblog"
 	sql = sql .. generatewhereclause(nil, starttime, endtime)
-	--sql = sql .. " GROUP BY "..groupby.. " ORDER BY numblock DESC"
-	sql = sql .. " GROUP BY " ..groupby.. " ORDER BY numflagged DESC"
+	sql = sql .. " GROUP BY " ..escape(groupby).. " ORDER BY numflagged DESC"
 	cur = assert (con:execute(sql))
 	row = cur:fetch ({}, "a")
 	while row do
-		--entries[#entries+1] = {numblock=row.numblock, maxscore=row.maxscore}
 		entries[#entries+1] = {numrecords=row.numrecords, numflagged=row.numflagged, numhits=row.numhits, numdenied=row.numdenied, numbypassed=row.numbypassed, maxscore=row.maxscore}
 		entries[#entries][groupby] = row[groupby]
 		row = cur:fetch (row, "a")
@@ -392,7 +388,7 @@ end
 local testdatabaseentry = function(datatype, value)
 	local success = true
 	local errtxt
-	local sql = "CREATE TEMP TABLE testing ( test "..datatype.." DEFAULT '"..escape(value).."' ) ON COMMIT DROP"
+	local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." DEFAULT '"..escape(value).."' ) ON COMMIT DROP"
 	local res, err = pcall(function()
 		assert (con:execute(sql))
 	end)
@@ -408,7 +404,7 @@ local convertdatabaseentry = function(datatype, value)
 	local errtxt
 	local result = value
 	local res, err = pcall(function()
-		local sql = "CREATE TEMP TABLE testing ( test "..datatype.." )"
+		local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." )"
 		assert (con:execute(sql))
 		sql = "INSERT INTO testing VALUES ('"..escape(value).."')"
 		assert (con:execute(sql))
@@ -433,7 +429,7 @@ end
 local printtableentries = function(tablename)
 	-- retrieve a cursor
 	local count = 0
-	cur = assert (con:execute("SELECT * from "..tablename))
+	cur = assert (con:execute("SELECT * from "..escape(tablename)))
 	-- print all rows, the rows will be indexed by field names
 	row = cur:fetch ({}, "a")
 	while row do