[Release] mysql_direct_query, get_table_postfix, mysql_escape_string written in c lua

[lollo_9_1]

Intro:
You could use this code (it works fine either via well-done libs or via game's sources) to create the relative functions in lua: mysql_direct_query, get_table_postfix, mysql_escape_string

The mysql_direct_query returns two values: the first one contains the count of how many rows had been affected (works fine for select, insert, update queries, and so on) and the second one a table containing all the information retrieved by a select query (empty if not).

The field type will be auto-detected, which means:

• A numeric field will be pushed as lua number
• A BLOB one will be pushed as table byte per byte
• A NULL field will be pushed as nil (not displayed in iteration)
• The other ones will be pushed as strings (be aware of this)

Example Quest #1: (Use CTRL+Q and CTRL+K on notepad++ to de/comment quickly)

-- local res1, res2 = mysql_direct_query("select player.name, player.level from player.player limit 2;")
local res1, res2 = mysql_direct_query("select * from player.player limit 1;")
-- local res1, res2 = mysql_direct_query("select name, skill_level from player.player limit 1;")
syschat(res2[1].name)
-- syschat(string.format("count(%d)", res1))
-- for i1=1, res1 do
	-- syschat(string.format("\tindex(%d)", i1))
	-- syschat(string.format("\t\tname(%s), level(%d)", res2[i1].name, res2[i1].level))
-- end
-- local res1, res2 = mysql_direct_query("select * from player.guild_comment;")
-- local res1, res2 = mysql_direct_query("INSERT INTO `guild_comment` (`id`) VALUES ('1');")
-- local res1, res2 = mysql_direct_query("INSERT INTO `guild_comment` (`guild_id`, `name`, `notice`, `content`, `time`) VALUES ('1', 'martytest', '0', 'blabla', NOW());")
syschat(string.format("count(%d)", res1))
for num1, str1 in ipairs(res2) do
	syschat(string.format("\tindex(%d)", num1))
	for key1, val1 in pairs(str1) do
		-- syschat(string.format("\t\tkey(%s), value(%s)", key1, val1))
		-- syschat(string.format("\t\tkey(%s), value(%s) type(%s)", key1, val1, type(val1)))
		if (type(val1)=="table") then
			syschat(string.format("\t\tkey(%s), size(%d), type(%s)", key1, table.getn(val1), type(val1)))
			-- syschat(string.format("\t\tkey(%s), type(%s)", key1, type(val1)))
			-- for num2, str2 in ipairs(val1) do
				-- syschat(string.format("\t\t\tidx(%s), value(%s), type(%s)", num2, str2, type(num2)))
			-- end
			-- syschat(string.format("\t\tkey(%s), value(%s), type(%s)", key1, table.concat(val1, ", "), type(val1))) --client will crash
		else
			syschat(string.format("\t\tkey(%s), value(%s), type(%s)", key1, val1, type(val1)))
		end
	end
end

-- syschat(mysql_escape_string("abyy"))
-- syschat(mysql_escape_string("'schure'")) --\'schure\'
syschat(mysql_escape_string("'\"lewd'\"")) --\'\"lewd\'\"
-- syschat(mysql_escape_string("`\n``\t`"))
syschat(mysql_escape_string([["aww'omg"<?'3]])) --\"aww\'omg\"<?\'3

How To:

Paste 'em into questlua_global.cpp outside the namespace quest:

#include "db.h"

Paste 'em into questlua_global.cpp inside the namespace quest:

	int _get_table_postfix(lua_State* L)
	{
		lua_pushstring(L, get_table_postfix());
		return 1;
	}

#ifdef _MSC_VER
#define INFINITY (DBL_MAX+DBL_MAX)
#define NAN (INFINITY-INFINITY)
#endif
	int _mysql_direct_query(lua_State* L)
	{
		if (!lua_isstring(L, 1))
			return 0;

		int i=0, m=1;
		MYSQL_ROW row;
		MYSQL_FIELD * field;
		MYSQL_RES * result;

		std::auto_ptr<SQLMsg> pMsg(DBManager::instance().DirectQuery("%s", lua_tostring(L, 1)));
		if (pMsg.get())
		{
			// ret1 (number of affected rows)
			lua_pushnumber(L, pMsg->Get()->uiAffectedRows);
			//-1 if error such as duplicate occurs (-2147483648 via lua)
			//   if wrong syntax error occurs (4294967295 via lua)
			// ret2 (table of affected rows)
			lua_newtable(L);
			if ((result = pMsg->Get()->pSQLResult) &&
					!(pMsg->Get()->uiAffectedRows == 0 || pMsg->Get()->uiAffectedRows == (uint32_t)-1))
			{

				while((row = mysql_fetch_row(result)))
				{
					lua_pushnumber(L, m);
					lua_newtable(L);
					while((field = mysql_fetch_field(result)))
					{
						lua_pushstring(L, field->name);
						if (!(field->flags & NOT_NULL_FLAG) && (row[i]==NULL))
						{
							// lua_pushstring(L, "NULL");
							lua_pushnil(L);
						}
						else if (IS_NUM(field->type))
						{
							double val = NAN;
							lua_pushnumber(L, (sscanf(row[i],"%lf",&val)==1)?val:NAN);
						}
						else if (field->type == MYSQL_TYPE_BLOB)
						{
							lua_newtable(L);
							for (DWORD iBlob=0; iBlob < field->max_length; iBlob++)
							{
								lua_pushnumber(L, row[i][iBlob]);
								lua_rawseti(L, -2, iBlob+1);
							}
						}
						else
							lua_pushstring(L, row[i]);

						lua_rawset(L, -3);
						i++;
					}
					mysql_field_seek(result, 0);
					i=0;

					lua_rawset(L, -3);
					m++;
				}
			}
		}
		else {lua_pushnumber(L, 0); lua_newtable(L);}

		return 2;
	}

	int _mysql_escape_string(lua_State* L)
	{
		char szQuery[1024] = {0};

		if (!lua_isstring(L, 1))
			return 0;

		DBManager::instance().EscapeString(szQuery, sizeof(szQuery), lua_tostring(L, 1), strlen(lua_tostring(L, 1)));
		lua_pushstring(L, szQuery);
		return 1;
	}

Inside global_functions:

			{	"get_table_postfix",			_get_table_postfix				},
			{	"mysql_direct_query",			_mysql_direct_query				},
			{	"mysql_escape_string",			_mysql_escape_string			},

Changelog:

• 2013/02/?: First draft was written by xXDarkAnimaXx (v0.1)
• 2013/02/?: A fixed and working draft was released by me (v1.0)
• 2014/07/03: Added: Auto-detecting mysql types, a return value of how many rows had been affected, and other two functions for general purpose (v3.0)
• 2016/07/28: Solved the little "%%" issue for mysql_direct_query and cleaned some comments (v4.810)

What To Know:

• 
• 

[Lord iRemix]

I don't understand why nobody likes such releases but if somebody (Vanilla) inserts it in his game the same people give many thanks for the work of other people.

Thanks for your work, lollo_9_1.

€: I think the most important reason for that is that the people here doesn't know how to handle with such codes.
@All: Even if you don't know how to use something like that you could leave a thanks for the work.

[2tmsuiris]

I would never use mysql in quests. There is a reason why official servers also recommend to do not use mysql in quest querys.

[Lord iRemix]

Originally Posted by 2tmsuiris

I would never use mysql in quests. There is a reason why official servers also recommend to do not use mysql in quest querys.

And why do they recommend not to use mysql in quests?
What should happen if i would use a mysql query in one of my quests?
They don't use mysql query functions because they have nothing which could use them.

[lollo_9_1]

Originally Posted by 2tmsuiris

I would never use mysql in quests. There is a reason why official servers also recommend to do not use mysql in quest querys.

Official servers don't talk about which feature they use or not, so you got no point.

Regardless of what they say:
1) You won't get slow performance from this feature (as long as you know how to create a decent query)
2) You won't get security issues if you correctly escape potentially dangerous things (such as inputs sent by players that directly affects your query)
3) At least, avoid to set full permission to your game's mysql account (select, insert, update, delete are more than enough; more of them require only select for few fields)

Note:
1) the first return value could contain numbers such as -2147483648 (duplicate entry) and 4294967295 (syntax error) when an error occurs.
2) The max limit of a query is 4096 characters and % should be escaped as %% (va_arg issue) #fixed in v4

This is a simple ranking example:

local query=[[
SELECT player.player.name as m_name, player.player.level as m_level, player.player.exp as m_exp
FROM player.player, account.account
WHERE
	player.player.name not like '[%]%' AND
	date_sub(now(), interval 1 day) < player.player.last_play AND
	player.player.account_id=account.account.id AND
	account.account.status='OK'
ORDER BY player.player.level DESC, player.player.exp DESC, player.player.name ASC
LIMIT 10;
]]
local res1, res2 = mysql_direct_query(query)
say_title("Top "..res1.." players.")
for num1, str1 in ipairs(res2) do
	say_reward(num1.."\t"..str1.m_name.."\t"..str1.m_level.."\t"..str1.m_exp)
end

[MrLibya]

Very Nice Function My Friend

+ About Use UPDATE :

             local res1, res2 = mysql_direct_query("select * from player.pet_system;")
             local res1, res2 = mysql_direct_query("UPDATE `pet_system` (`pet_name`) VALUES ('"..NewPetNm.."') WHERE id=('"..pc.get_player_id().."');")

That's Correct ? + How Much Time It's Take To Update In The Table .

[Lefloyd]

Some nice functions but also some new ways to destroy someone's own server.
FIRST: @2tmsuiris: this is not really correct. Also the official servers are using mysql querys in quests - just "hidden" in game functions (but also there are direct querys). So it's nearly the same.
SECOND: You won't get really slow performance if you write not too bad querys... but if you do querys like in your example lollo_9_1 and you write a just bit bigger query maybe 10 players in ranking it could be a problem. You can use this quests as DDoS if you just send X-Requests to the server for the quest (if there is no "wait()" in the quest or checking of last use time it will work) the Game-Core won't do anything until all the mysql querys has been answered. So he send maybe 100 requests and wait for 100 answers before he will do ANYTHING for ANY player - he won't compute packets anymore until the result has arrived. That's the only problem with direct querys imho.

Anyways thanks for the release (event if I think I've seen a mysql_query function on EPvP shortly after the game source has been released in any "Sammelthread" but I don't remember which it was ....^^ and it wasn't exactly the same function).

€dit: @MrLibya: I'd recommend you to write

UPDATE `pet_system` SET `pet_name`='"..NewPetNm.."' WHERE id='"..pc.get_player_id().."'"

And yes, it's correct. But you will get no result if you write an update query so you can remove the

local res1, res2 =

Kind Regards

[lollo_9_1]

Originally Posted by Lefloyd

1) maybe 10 players in ranking it could be a problem. You can use this quests as DDoS if you just send X-Requests to the server for the quest

2) Anyways thanks for the release (event if I think I've seen a mysql_query function on EPvP shortly after the game source has been released in any "Sammelthread" but I don't remember which it was ....^^ and it wasn't exactly the same function).

3) But you will get no result if you write an update query so you can remove the

local res1, res2 =

Kind Regards

1. A top 10 rank query is still quick to run (0.068ms in a vps)
   The trivialest solution ever is the following:
   

   if get_time() > pc.getqf("antiflood") then
   	local antiflood_time=30 -- 30 seconds
   	pc.setqf("antiflood", get_time()+antiflood_time)
   	-- do stuff
   else
   	local antiflood_diff=pc.getqf("antiflood")-get_time()
   	say_reward("You are not allowed to view the rank yet[ENTER]Wait... "..antiflood_diff.." second/s")
   end

2. Check the dates inside the Changelog
3. The first return value could be used to check whether the query got errors or not. (even for insert and update ones)

[baran860]

Why don't working query:
"SELECT `name`, `level` FROM player WHERE name NOT LIKE '[%' ORDER BY level DESC, `exp` DESC limit 20;"

And it works:
"SELECT `name`, `level` FROM player WHERE name NOT LIKE '[' ORDER BY level DESC, `exp` DESC limit 20;"?

% - A substitute for zero or more characters


Sorry - %% :P

[baran860]

Hello
How to get the value when the relationship in query:

Not work:

local count, value = mysql_direct_query("SELECT guild_member.guild_id, player.name FROM `guild_member`, `player` WHERE guild_member.pid == player.id AND guild_member.grade = '1';")
say("Leader: "..tostring(value[1].name).." - Guild: "..guild.name(tonumber(value[1].guild_id)))

Result query:

[lollo_9_1]

Updated:

2016/07/28: Solved the little "%%" issue for mysql_direct_query and cleaned some comments (v4.810)

[VegaS ♆]