Raised This Month: $90 Target: $400
 22% 

Solved Sql Query Insert Problem


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
arcticx2
Member
Join Date: Nov 2011
Old 01-10-2019 , 10:18   Sql Query Insert Problem
Reply With Quote #1

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 ?

Last edited by arcticx2; 01-10-2019 at 12:08.
arcticx2 is online now
Ilusion9
Senior Member
Join Date: Jun 2018
Location: Romania
Old 01-10-2019 , 11:07   Re: Mysql Insert Problem
Reply With Quote #2

PHP Code:

    char query
[256], buffer[65], name[65 1];
    
    
GetClientName(clientbuffersizeof(buffer));
    
hDatabase.Escape(buffernamesizeof(name));
            
    
Format(querysizeof(query), "INSERT INTO name_table (name) VALUES ('%s') ON DUPLICATE KEY UPDATE name = VALUES(name);"name);
    
hDatabase.Query(Handle_FastQueryquery); 
this should be fine.
__________________
Ilusion9 is offline
arcticx2
Member
Join Date: Nov 2011
Old 01-10-2019 , 11:26   Re: Sql Query Insert Problem
Reply With Quote #3

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 !

Last edited by arcticx2; 01-10-2019 at 12:05. Reason: edit 2
arcticx2 is online now
ThatKidWhoGames
Veteran Member
Join Date: Jun 2013
Location: IsValidClient()
Old 01-10-2019 , 13:37   Re: Sql Query Insert Problem
Reply With Quote #4

Database.Format automatically escapes format specifiers for you.
PHP Code:
    char query[256], name[32];

    
GetClientName(clientnamesizeof(name));

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


I take custom plugin requests, add me on Steam for details.

Last edited by ThatKidWhoGames; 01-10-2019 at 13:38.
ThatKidWhoGames is offline
arcticx2
Member
Join Date: Nov 2011
Old 01-10-2019 , 15:26   Re: Sql Query Insert Problem
Reply With Quote #5

Quote:
Originally Posted by ThatKidWhoGames View Post
Database.Format automatically escapes format specifiers for you.
PHP Code:
    char query[256], name[32];

    
GetClientName(clientnamesizeof(name));

    
hDatabase.Format(querysizeof(query), "INSERT INTO name_table (name) VALUES ('%s') ON DUPLICATE KEY UPDATE name = VALUES(name);"name);
    
hDatabase.Query(Handle_FastQueryquery); 
What is the difference between format & escape then ? which one is better for my purpose ?
arcticx2 is online now
ThatKidWhoGames
Veteran Member
Join Date: Jun 2013
Location: IsValidClient()
Old 01-11-2019 , 09:54   Re: Sql Query Insert Problem
Reply With Quote #6

Quote:
Originally Posted by arcticx2 View Post
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.
__________________


I take custom plugin requests, add me on Steam for details.

Last edited by ThatKidWhoGames; 01-11-2019 at 09:56.
ThatKidWhoGames is offline
arcticx2
Member
Join Date: Nov 2011
Old 01-11-2019 , 09:55   Re: Sql Query Insert Problem
Reply With Quote #7

Quote:
Originally Posted by ThatKidWhoGames View Post
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
arcticx2 is online now
asherkin
SourceMod Developer
Join Date: Aug 2009
Location: OnGameFrame()
Old 01-12-2019 , 07:55   Re: Sql Query Insert Problem
Reply With Quote #8

Quote:
Originally Posted by ThatKidWhoGames View Post
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(clientnamesizeof(name))) {
  return 
false;
}

int safeNameLen = (strlen(name) * 2) + 1;
char[] safeName = new char[safeNameLen];
db.Escape(namesafeNamesafeNameLen);

char steamId[32];
if (!
GetClientAuthId(clientAuthId_Steam2steamIdsizeof(steamId))) {
  return 
false;
}

int safeSteamIdLen = (strlen(steamId) * 2) + 1;
char[] safeSteamId = new char[safeSteamIdLen];
db.Escape(steamIdsafeSteamIdsafeSteamIdLen);

char buffer[512];
Format(buffersizeof(buffer), "UPDATE players SET name = '%s' WHERE steamid = '%s'"safeNamesafeSteamId);
db.Query(OnQueryCompletebuffer); 
Whereas using Database.Format looks like this:
PHP Code:
char steamId[32];
if (!
GetClientAuthId(clientAuthId_Steam2steamidsizeof(steamid))) {
  return 
false;
}

char buffer[512];
db.Format(buffersizeof(buffer), "UPDATE players SET name = '%N' WHERE steamid = '%s'"clientsteamId);
db.Query(OnQueryCompletebuffer); 
It is a lot shorter and harder to get wrong.
__________________
asherkin is online now
arcticx2
Member
Join Date: Nov 2011
Old 01-12-2019 , 11:03   Re: Sql Query Insert Problem
Reply With Quote #9

Quote:
Originally Posted by asherkin View Post
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(clientnamesizeof(name))) {
  return 
false;
}

int safeNameLen = (strlen(name) * 2) + 1;
char[] safeName = new char[safeNameLen];
db.Escape(namesafeNamesafeNameLen);

char steamId[32];
if (!
GetClientAuthId(clientAuthId_Steam2steamIdsizeof(steamId))) {
  return 
false;
}

int safeSteamIdLen = (strlen(steamId) * 2) + 1;
char[] safeSteamId = new char[safeSteamIdLen];
db.Escape(steamIdsafeSteamIdsafeSteamIdLen);

char buffer[512];
Format(buffersizeof(buffer), "UPDATE players SET name = '%s' WHERE steamid = '%s'"safeNamesafeSteamId);
db.Query(OnQueryCompletebuffer); 
Whereas using Database.Format looks like this:
PHP Code:
char steamId[32];
if (!
GetClientAuthId(clientAuthId_Steam2steamidsizeof(steamid))) {
  return 
false;
}

char buffer[512];
db.Format(buffersizeof(buffer), "UPDATE players SET name = '%N' WHERE steamid = '%s'"clientsteamId);
db.Query(OnQueryCompletebuffer); 
It is a lot shorter and harder to get wrong.
nice explanation asherkin thanks for your post , have some
arcticx2 is online now
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 16:32.


Powered by vBulletin®
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Theme made by Freecode