[TheDiamant953]

Hello,

I'm here for try to have your generous help about a fail on my server.

I use a plugin who use sql. This is the part of the code who have a problem:

public SaveC(client)
{
	if(g_hDatabase == INVALID_HANDLE)
	{
		LogError("Database connection is lost or not yet initialized.");
		return;
	}
	
	if(!g_eClients[client][bLoaded])
		return;
		
	if(0 >= client < MAXPLAYERS+1)
		return;
		
	if(g_eClients[client][iId] <= 0)
		return;
		
	if(g_hDatabase)
		SQL_EscapeString(g_hDatabase, g_eClients[client][szName], g_eClients[client][szNameEscaped], 128);
		
	decl String:m_szQuery[256];
	Format(STRING(m_szQuery), 
	"UPDATE xxx SET `date_of_last_join`=%d, `username`=\"%s\", `title`=\"%s\", `playtime`=%d WHERE `id`=%d AND 'authid'=\"%s\"",
	g_eClients[client][iDateOfLastJoin], 
	g_eClients[client][szNameEscaped], 
	g_eClients[client][sTitre], 
	g_eClients[client][iPlaytime], 
	g_eClients[client][iId],
	g_eClients[client][szAuthId]);	
		
	decl String:m_szQuery2[256];
	Format(STRING(m_szQuery2), 
	"UPDATE xxx SET `value1`=%d, `value2`=%d, `value3`=%d WHERE `id`=%d AND 'authid'=\"%s\"",
	g_eClients[client][value1], 
	g_eClients[client][value2], 
	g_eClients[client][value3],
	g_eClients[client][iId],
	g_eClients[client][szAuthId]);
		
	SQL_TQuery(g_hDatabase, SQLCallback_Void, m_szQuery);
	SQL_TQuery(g_hDatabase, SQLCallback_Void, m_szQuery2);
}

The problem:

When a user have in their username "▄︻̷̿┻̿═━一 ***", all the lines of the table will save with his informations, but i don't understand why !

In my case, i use two sql insert, but the problem is only for the first won. The first insert will replace all the informations in it with the problem user infos, but not the second one!

The username is escaped sql and save like other string, and it's not a condition, but all the line of the table is save with his infos.



This is some past random users values, but they are changed with the user who have ▄︻̷̿┻̿═━一 *** in his username.

Thanks.

[Impact123]

On the plugin side make sure you set the charset to utf8 (utf8mb4) right after you're connected to the database. I recommend you search for "mysql utf8mb4" to find out how to handle the database side (creation of databases / tables, converting to utf8mb4, etc).

Kinda unrelated

I think your whole code looks weird but you probably want to use ' instead of using " in your queries and change

PHP Code:

AND 'authid'=... 


to

PHP Code:

AND `authid`=... 


Some other suggestions

• There is no need to check if the database handle is "valid" twice
• Use MaxClients instead of MAXPLAYERS
• Use sizeof

[TheDiamant953]

For make my plugin, i help myself with the store plugin by Zephirus.

In his plugin, i have for the same user :



And there is no problem with other lines.

In mine, i don't know why, maybe a mistake or a fail, it's replace all the lines.

In the Zephirus's store, there isn't any set to utf8 after connecting, and the query seems to be the same (with ").

I really don't understand what is the problem, and why the problem is only on the FIRST query, and not the second, because there is nothing between the querys!

Thanks.

[TheDiamant953]

Nobody can help me ?

[friagram]

He already said, set the chars etc of the dB on connection, and see the table chars etc to UTF8 as well.

[TheDiamant953]

So,

I have change my sql tables to utf8mb4, and i have add
SQL_SetCharset(g_hDatabase, "utf8mb4");
In the sql callback connect, it's still working after a save test, only the good row is saved.

Thanks for your help and sorry for my bad english.

[Impact123]

I'm not really sure if you fixed your problem or not. If not i suggest you print / log your query before you send it to the database and post it here.

PHP Code:

PrintToServer("Executing query: '%s'", m_szQuery) 


[TheDiamant953]

So,

After my last message, i have test the plugin in a test server with a name with special chars like ▄︻̷̿┻̿═━一 ***.
After a save, only my line of value was saved.

So i have post it on my server, with 30 servers every time.

After 5 days, a guy with special char have make the same bug, and all lines are deleted, changed.

I don't know what is the problem, i'm really out.

I try your idea @Impact123, and i come back with the query.

If i don't find any solution, maybe can i block the querys for users with specials chars ?

Thanks.

[Miu]

afaik the strings should be delimited with single quotes ', not double quotes "

[TheDiamant953]

That what i think too, but it was the same query type that in the Zephyrus's store and the bug is not in the store's database.