[arcticx2]

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 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 !

so what is the problem here ?

[Ilusion9]

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.

[arcticx2]

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 !

[ThatKidWhoGames]

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); 


[arcticx2]

Originally Posted by ThatKidWhoGames 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 ?

[ThatKidWhoGames]

Originally Posted by arcticx2 What is the difference between format & escape then ? which one is better for my purpose ?

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.

[arcticx2]

Originally Posted by ThatKidWhoGames 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

[asherkin]

Originally Posted by ThatKidWhoGames 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.

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.

[arcticx2]

Originally Posted by asherkin 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.

nice explanation asherkin thanks for your post , have some