AlliedModders - [Solved] Sql Query Insert Problem

AlliedModders (https://forums.alliedmods.net/index.php)

- Scripting (https://forums.alliedmods.net/forumdisplay.php?f=107)

- - Solved Sql Query Insert Problem (https://forums.alliedmods.net/showthread.php?t=313458)

Sql Query Insert Problem

Hello guys,

i have a little problem with my db manager here , the thing is sometimes my bans will not insert into the db for the players name . because of the chars or anything else dont matter.

here is what i do for my db manager

first i escape the names with " SQL_EscapeString " then i put it into my sql then insert ....

as i noticed it seems its not good enough ! every player with some special chars will cause an error which i cant see in my csgo logs :3 so here is my mysql config which i guess cause the problem or ...

PHP Code:


		
			
sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

+ If i create the problematic query and add it manually to my database it will insert without any error ! :grrr:

so what is the problem here ?

Re: Mysql Insert Problem

PHP Code:


		
			


    char query[256], buffer[65], name[65 * 2 + 1];

    

    GetClientName(client, buffer, sizeof(buffer));

    hDatabase.Escape(buffer, name, sizeof(name));

            

    Format(query, sizeof(query), "INSERT INTO name_table (name) VALUES ('%s') ON DUPLICATE KEY UPDATE name = VALUES(name);", name);

    hDatabase.Query(Handle_FastQuery, query);

this should be fine.

Re: Sql Query Insert Problem

will it escape characters like | or ıllıll or any others as well ?

Edit:

so basically using Escape function from database handler is better than the sql_escape thing ?

Edit 2:

Thanks it works great !

Re: Sql Query Insert Problem

Database.Format automatically escapes format specifiers for you.

PHP Code:


		
			
    char query[256], name[32];

    GetClientName(client, name, sizeof(name));

    hDatabase.Format(query, sizeof(query), "INSERT INTO name_table (name) VALUES ('%s') ON DUPLICATE KEY UPDATE name = VALUES(name);", name);
    hDatabase.Query(Handle_FastQuery, query);

Re: Sql Query Insert Problem

Quote:

Originally Posted by ThatKidWhoGames (Post 2634001)

Database.Format automatically escapes format specifiers for you.

PHP Code:


		
			
    char query[256], name[32];



    GetClientName(client, name, sizeof(name));



    hDatabase.Format(query, sizeof(query), "INSERT INTO name_table (name) VALUES ('%s') ON DUPLICATE KEY UPDATE name = VALUES(name);", name);

    hDatabase.Query(Handle_FastQuery, query);

What is the difference between format & escape then ? which one is better for my purpose ? :shock:

Re: Sql Query Insert Problem

Quote:

Originally Posted by arcticx2 (Post 2634023)

What is the difference between format & escape then ? which one is better for my purpose ? :shock:

Database.Format automatically escapes any strings that you want to format into a query. If you want to use the standard Format function for formatting an SQL query, you would have to escape the string first and then format the query. Someone can probably explain this better than I can.

Re: Sql Query Insert Problem

Quote:

Originally Posted by ThatKidWhoGames (Post 2634139)

Database.Format is just more convenient because you don't need to put the effort into escaping the string yourself before formatting the query string.

Thanks mate :bacon!: :) :bacon!:

Re: Sql Query Insert Problem

Quote:

Originally Posted by ThatKidWhoGames (Post 2634139)

Basically, it is really hard to do per-variable escaping correctly.

Taking the simple example of updating a players name stored in a database against a SteamID:

Using Database.Escape correctly looks like this:

PHP Code:


		
			
char name[MAX_NAME_LENGTH];

if (!GetClientName(client, name, sizeof(name))) {

  return false;

}



int safeNameLen = (strlen(name) * 2) + 1;

char[] safeName = new char[safeNameLen];

db.Escape(name, safeName, safeNameLen);



char steamId[32];

if (!GetClientAuthId(client, AuthId_Steam2, steamId, sizeof(steamId))) {

  return false;

}



int safeSteamIdLen = (strlen(steamId) * 2) + 1;

char[] safeSteamId = new char[safeSteamIdLen];

db.Escape(steamId, safeSteamId, safeSteamIdLen);



char buffer[512];

Format(buffer, sizeof(buffer), "UPDATE players SET name = '%s' WHERE steamid = '%s'", safeName, safeSteamId);

db.Query(OnQueryComplete, buffer);

Whereas using Database.Format looks like this:

PHP Code:


		
			
char steamId[32];

if (!GetClientAuthId(client, AuthId_Steam2, steamid, sizeof(steamid))) {

  return false;

}



char buffer[512];

db.Format(buffer, sizeof(buffer), "UPDATE players SET name = '%N' WHERE steamid = '%s'", client, steamId);

db.Query(OnQueryComplete, buffer);

It is a lot shorter and harder to get wrong.

Re: Sql Query Insert Problem

Quote:

Originally Posted by asherkin (Post 2634291)

PHP Code:


		
			
char name[MAX_NAME_LENGTH];

if (!GetClientName(client, name, sizeof(name))) {

  return false;

}



int safeNameLen = (strlen(name) * 2) + 1;

char[] safeName = new char[safeNameLen];

db.Escape(name, safeName, safeNameLen);



char steamId[32];

if (!GetClientAuthId(client, AuthId_Steam2, steamId, sizeof(steamId))) {

  return false;

}



int safeSteamIdLen = (strlen(steamId) * 2) + 1;

char[] safeSteamId = new char[safeSteamIdLen];

db.Escape(steamId, safeSteamId, safeSteamIdLen);



char buffer[512];

Format(buffer, sizeof(buffer), "UPDATE players SET name = '%s' WHERE steamid = '%s'", safeName, safeSteamId);

db.Query(OnQueryComplete, buffer);

Whereas using Database.Format looks like this:

PHP Code:


		
			
char steamId[32];

if (!GetClientAuthId(client, AuthId_Steam2, steamid, sizeof(steamid))) {

  return false;

}



char buffer[512];

db.Format(buffer, sizeof(buffer), "UPDATE players SET name = '%N' WHERE steamid = '%s'", client, steamId);

db.Query(OnQueryComplete, buffer);

It is a lot shorter and harder to get wrong.

nice explanation asherkin :) thanks for your post , have some :bacon!::bacon!: