SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)

3 files changed, 72 insertions, 74 deletions

diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua
index 216b3be4..5e93db57 100644
--- a/plugins/mod_storage_sql.lua
+++ b/plugins/mod_storage_sql.lua
@@ -58,9 +58,9 @@ local function keyval_store_get()
 	local haveany;
 	local result = {};
 	local select_sql = [[
-	SELECT `key`,`type`,`value`
-	FROM `prosody`
-	WHERE `host`=? AND `user`=? AND `store`=?;
+	SELECT "key","type","value"
+	FROM "prosody"
+	WHERE "host"=? AND "user"=? AND "store"=?;
 	]]
 	for row in engine:select(select_sql, host, user or "", store) do
 		haveany = true;
@@ -80,14 +80,14 @@ local function keyval_store_get()
 end
 local function keyval_store_set(data)
 	local delete_sql = [[
-	DELETE FROM `prosody`
-	WHERE `host`=? AND `user`=? AND `store`=?
+	DELETE FROM "prosody"
+	WHERE "host"=? AND "user"=? AND "store"=?
 	]];
 	engine:delete(delete_sql, host, user or "", store);
 
 	local insert_sql = [[
-	INSERT INTO `prosody`
-	(`host`,`user`,`store`,`key`,`type`,`value`)
+	INSERT INTO "prosody"
+	("host","user","store","key","type","value")
 	VALUES (?,?,?,?,?,?);
 	]]
 	if data and next(data) ~= nil then
@@ -130,9 +130,9 @@ end
 function keyval_store:users()
 	local ok, result = engine:transaction(function()
 		local select_sql = [[
-		SELECT DISTINCT `user`
-		FROM `prosody`
-		WHERE `host`=? AND `store`=?;
+		SELECT DISTINCT "user"
+		FROM "prosody"
+		WHERE "host"=? AND "store"=?;
 		]];
 		return engine:select(select_sql, host, self.store);
 	end);
@@ -149,9 +149,9 @@ map_store.remove = {};
 function map_store:get(username, key)
 	local ok, result = engine:transaction(function()
 		local query = [[
-		SELECT `type`, `value`
-		FROM `prosody`
-		WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
+		SELECT "type", "value"
+		FROM "prosody"
+		WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
 		LIMIT 1
 		]];
 		local data;
@@ -177,18 +177,18 @@ end
 function map_store:set_keys(username, keydatas)
 	local ok, result = engine:transaction(function()
 		local delete_sql = [[
-		DELETE FROM `prosody`
-		WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
+		DELETE FROM "prosody"
+		WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
 		]];
 		local insert_sql = [[
-		INSERT INTO `prosody`
-		(`host`,`user`,`store`,`key`,`type`,`value`)
+		INSERT INTO "prosody"
+		("host","user","store","key","type","value")
 		VALUES (?,?,?,?,?,?);
 		]];
 		local select_extradata_sql = [[
-		SELECT `type`, `value`
-		FROM `prosody`
-		WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
+		SELECT "type", "value"
+		FROM "prosody"
+		WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
 		LIMIT 1;
 		]];
 		for key, data in pairs(keydatas) do
@@ -227,12 +227,12 @@ function archive_store:append(username, key, value, when, with)
 	with = with or "";
 	local ok, ret = engine:transaction(function()
 		local delete_sql = [[
-		DELETE FROM `prosodyarchive`
-		WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
+		DELETE FROM "prosodyarchive"
+		WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
 		]];
 		local insert_sql = [[
-		INSERT INTO `prosodyarchive`
-		(`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`)
+		INSERT INTO "prosodyarchive"
+		("host", "user", "store", "when", "with", "key", "type", "value")
 		VALUES (?,?,?,?,?,?,?,?);
 		]];
 		if key then
@@ -253,27 +253,27 @@ local function archive_where(query, args, where)
 	-- Time range, inclusive
 	if query.start then
 		args[#args+1] = query.start
-		where[#where+1] = "`when` >= ?"
+		where[#where+1] = "\"when\" >= ?"
 	end
 
 	if query["end"] then
 		args[#args+1] = query["end"];
 		if query.start then
-			where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive?
+			where[#where] = "\"when\" BETWEEN ? AND ?" -- is this inclusive?
 		else
-			where[#where+1] = "`when` <= ?"
+			where[#where+1] = "\"when\" <= ?"
 		end
 	end
 
 	-- Related name
 	if query.with then
-		where[#where+1] = "`with` = ?";
+		where[#where+1] = "\"with\" = ?";
 		args[#args+1] = query.with
 	end
 
 	-- Unique id
 	if query.key then
-		where[#where+1] = "`key` = ?";
+		where[#where+1] = "\"key\" = ?";
 		args[#args+1] = query.key
 	end
 end
@@ -282,11 +282,11 @@ local function archive_where_id_range(query, args, where)
 	-- Before or after specific item, exclusive
 	if query.after then  -- keys better be unique!
 		where[#where+1] = [[
-		`sort_id` > COALESCE(
+		"sort_id" > COALESCE(
 			(
-				SELECT `sort_id`
-				FROM `prosodyarchive`
-				WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ?
+				SELECT "sort_id"
+				FROM "prosodyarchive"
+				WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
 				LIMIT 1
 			), 0)
 		]];
@@ -295,16 +295,16 @@ local function archive_where_id_range(query, args, where)
 	end
 	if query.before then
 		where[#where+1] = [[
-		`sort_id` < COALESCE(
+		"sort_id" < COALESCE(
 			(
-				SELECT `sort_id`
-				FROM `prosodyarchive`
-				WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ?
+				SELECT "sort_id"
+				FROM "prosodyarchive"
+				WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
 				LIMIT 1
 			),
 			(
-				SELECT MAX(`sort_id`)+1
-				FROM `prosodyarchive`
+				SELECT MAX("sort_id")+1
+				FROM "prosodyarchive"
 			)
 		)
 		]]
@@ -318,19 +318,19 @@ function archive_store:find(username, query)
 	local total;
 	local ok, result = engine:transaction(function()
 		local sql_query = [[
-		SELECT `key`, `type`, `value`, `when`, `with`
-		FROM `prosodyarchive`
+		SELECT "key", "type", "value", "when", "with"
+		FROM "prosodyarchive"
 		WHERE %s
-		ORDER BY `sort_id` %s%s;
+		ORDER BY "sort_id" %s%s;
 		]];
 		local args = { host, user or "", store, };
-		local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
+		local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
 
 		archive_where(query, args, where);
 
 		-- Total matching
 		if query.total then
-			local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE "
+			local stats = engine:select("SELECT COUNT(*) FROM \"prosodyarchive\" WHERE "
 				.. t_concat(where, " AND "), unpack(args));
 			if stats then
 				for row in stats do
@@ -365,9 +365,9 @@ function archive_store:delete(username, query)
 	query = query or {};
 	local user,store = username,self.store;
 	local ok, stmt = engine:transaction(function()
-		local sql_query = "DELETE FROM `prosodyarchive` WHERE %s;";
+		local sql_query = "DELETE FROM \"prosodyarchive\" WHERE %s;";
 		local args = { host, user or "", store, };
-		local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
+		local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
 		if user == true then
 			table.remove(args, 2);
 			table.remove(where, 2);
@@ -401,7 +401,7 @@ function driver:open(store, typ)
 end
 
 function driver:stores(username)
-	local query = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" ..
+	local query = "SELECT DISTINCT \"store\" FROM \"prosody\" WHERE \"host\"=? AND \"user\"" ..
 		(username == true and "!=?" or "=?");
 	if username == true or not username then
 		username = "";
@@ -415,7 +415,7 @@ end
 
 function driver:purge(username)
 	return engine:transaction(function()
-		local stmt,err = engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username);
+		local stmt,err = engine:delete("DELETE FROM \"prosody\" WHERE \"host\"=? AND \"user\"=?", host, username);
 		return true, err;
 	end);
 end
@@ -467,7 +467,7 @@ local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore
 				changes = true;
 				if apply_changes then
 					module:log("info", "Upgrading database schema...");
-					engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
+					engine:execute("ALTER TABLE prosody MODIFY COLUMN \"value\" MEDIUMTEXT");
 					module:log("info", "Database table automatically upgraded");
 				end
 			end
@@ -482,9 +482,9 @@ local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore
 
 		-- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
 		local check_encoding_query = [[
-		SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME`
-		FROM `information_schema`.`columns`
-		WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );
+		SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
+		FROM "information_schema"."columns"
+		WHERE "TABLE_NAME" LIKE 'prosody%%' AND ( "CHARACTER_SET_NAME"!='%s' OR "COLLATION_NAME"!='%s_bin' );
 		]];
 		check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
 		-- FIXME Is it ok to ignore the return values from this?
@@ -495,8 +495,8 @@ local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore
 				changes = true;
 				if apply_changes then
 					module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
-					local fix_column_query1 = "ALTER TABLE `%s` CHANGE `%s` `%s` BLOB;";
-					local fix_column_query2 = "ALTER TABLE `%s` CHANGE `%s` `%s` %s CHARACTER SET '%s' COLLATE '%s_bin';";
+					local fix_column_query1 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" BLOB;";
+					local fix_column_query2 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" %s CHARACTER SET '%s' COLLATE '%s_bin';";
 					for row in result:rows() do
 						local column_name, column_type, table_name  = unpack(row);
 						module:log("debug", "Fixing column %s in table %s", column_name, table_name);
diff --git a/tools/migration/migrator/prosody_sql.lua b/tools/migration/migrator/prosody_sql.lua
index 642b74c2..2e902fbb 100644
--- a/tools/migration/migrator/prosody_sql.lua
+++ b/tools/migration/migrator/prosody_sql.lua
@@ -92,9 +92,9 @@ local function needs_upgrade(engine, params)
 
 			-- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
 			local check_encoding_query = [[
-			SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME`
-			FROM `information_schema`.`columns`
-			WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );
+			SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
+			FROM "information_schema"."columns"
+			WHERE "TABLE_NAME" LIKE 'prosody%%' AND ( "CHARACTER_SET_NAME"!='%s' OR "COLLATION_NAME"!='%s_bin' );
 			]];
 			check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
 			local result = engine:execute(check_encoding_query);
@@ -116,7 +116,7 @@ local function reader(input)
 	end));
 	local keys = {"host", "user", "store", "key", "type", "value"};
 	assert(engine:connect());
-	local f,s,val = assert(engine:select("SELECT `host`, `user`, `store`, `key`, `type`, `value` FROM `prosody`;"));
+	local f,s,val = assert(engine:select("SELECT \"host\", \"user\", \"store\", \"key\", \"type\", \"value\" FROM \"prosody\";"));
 	-- get SQL rows, sorted
 	local iter = mtools.sorted {
 		reader = function() val = f(s, val); return val; end;
@@ -157,8 +157,8 @@ local function writer(output, iter)
 		create_table(engine);
 	end));
 	assert(engine:connect());
-	assert(engine:delete("DELETE FROM prosody"));
-	local insert_sql = "INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)";
+	assert(engine:delete("DELETE FROM \"prosody\""));
+	local insert_sql = "INSERT INTO \"prosody\" (\"host\",\"user\",\"store\",\"key\",\"type\",\"value\") VALUES (?,?,?,?,?,?)";
 
 	return function(item)
 		if not item then assert(engine.conn:commit()) return end -- end of input
diff --git a/util/sql.lua b/util/sql.lua
index eb562eb2..a859ddc9 100644
--- a/util/sql.lua
+++ b/util/sql.lua
@@ -128,8 +128,8 @@ function engine:onconnect()
 end
 
 function engine:prepquery(sql)
-	if self.params.driver == "PostgreSQL" then
-		sql = sql:gsub("`", "\"");
+	if self.params.driver == "MySQL" then
+		sql = sql:gsub("\"", "`");
 	end
 	return sql;
 end
@@ -242,27 +242,26 @@ function engine:transaction(...)
 	return ok, ret;
 end
 function engine:_create_index(index)
-	local sql = "CREATE INDEX `"..index.name.."` ON `"..index.table.."` (";
+	local sql = "CREATE INDEX \""..index.name.."\" ON \""..index.table.."\" (";
 	for i=1,#index do
-		sql = sql.."`"..index[i].."`";
+		sql = sql.."\""..index[i].."\"";
 		if i ~= #index then sql = sql..", "; end
 	end
 	sql = sql..");"
-	if self.params.driver == "PostgreSQL" then
-		sql = sql:gsub("`", "\"");
-	elseif self.params.driver == "MySQL" then
-		sql = sql:gsub("`([,)])", "`(20)%1");
+	if self.params.driver == "MySQL" then
+		sql = sql:gsub("\"([,)])", "\"(20)%1");
 	end
 	if index.unique then
 		sql = sql:gsub("^CREATE", "CREATE UNIQUE");
 	end
+	sql = self:prepquery(sql);
 	if self._debug then
 		debugquery("create", sql);
 	end
 	return self:execute(sql);
 end
 function engine:_create_table(table)
-	local sql = "CREATE TABLE `"..table.name.."` (";
+	local sql = "CREATE TABLE \""..table.name.."\" (";
 	for i,col in ipairs(table.c) do
 		local col_type = col.type;
 		if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then
@@ -271,7 +270,7 @@ function engine:_create_table(table)
 		if col.auto_increment == true and self.params.driver == "PostgreSQL" then
 			col_type = "BIGSERIAL";
 		end
-		sql = sql.."`"..col.name.."` "..col_type;
+		sql = sql.."\""..col.name.."\" "..col_type;
 		if col.nullable == false then sql = sql.." NOT NULL"; end
 		if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
 		if col.auto_increment == true then
@@ -284,11 +283,10 @@ function engine:_create_table(table)
 		if i ~= #table.c then sql = sql..", "; end
 	end
 	sql = sql.. ");"
-	if self.params.driver == "PostgreSQL" then
-		sql = sql:gsub("`", "\"");
-	elseif self.params.driver == "MySQL" then
+	if self.params.driver == "MySQL" then
 		sql = sql:gsub(";$", (" CHARACTER SET '%s' COLLATE '%s_bin';"):format(self.charset, self.charset));
 	end
+	sql = self:prepquery(sql);
 	if self._debug then
 		debugquery("create", sql);
 	end
@@ -316,7 +314,7 @@ function engine:set_encoding() -- to UTF-8
 	local charset = "utf8";
 	if driver == "MySQL" then
 		self:transaction(function()
-			for row in self:select"SELECT `CHARACTER_SET_NAME` FROM `information_schema`.`CHARACTER_SETS` WHERE `CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;" do
+			for row in self:select"SELECT \"CHARACTER_SET_NAME\" FROM \"information_schema\".\"CHARACTER_SETS\" WHERE \"CHARACTER_SET_NAME\" LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;" do
 				charset = row and row[1] or charset;
 			end
 		end);