AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   insert into (or update) mysql fails because of special characters (https://forums.alliedmods.net/showthread.php?t=314183)

TheBladerX 02-07-2019 10:38

insert into (or update) mysql fails because of special characters
 
Hey, how do I fix that problem? For example player's name is -*KURCZACY*- tesak
and console pops
Code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''5' at line 1
. (All players with normal names are uploaded correctly to database, so it is because of these special chars)

I have following codes to insert, update and select:

PHP Code:

new table1[] = "CREATE TABLE IF NOT EXISTS `codexp` ( `user` VARCHAR(33) NOT NULL , `trieda` INT NOT NULL , `level` INT NOT NULL , PRIMARY KEY (`user`)) ENGINE = InnoDB;" 

formatex(Querycharsmax(Query), "INSERT INTO `codexp` (`user`, `trieda`, `level`) VALUES ('%s-%i', '%i', '%i') ON DUPLICATE KEY UPDATE `level` = '%i'"nazwa_gracza[id], klasa_gracza[id], klasa_gracza[id], poziom_gracza[id], poziom_gracza[id]) 

formatex(Querycharsmax(Query), "SELECT * FROM codexp WHERE user='%s-%i'"nazwa_gracza[id], klasa_gracza[id])

formatex(Querycharsmax(Query), "UPDATE `codexp` SET `level` = '%i', `trieda` = '%i' WHERE `user` = '%s-%i';"poziom_gracza[id], klasa_gracza[id], nazwa_gracza[id], klasa_gracza[id]) 

I am also aware of that the problem can be caused by %s-%i dash, but how can I fix it, except by adding other character than dash?

Airkish 02-07-2019 11:51

Re: insert into (or update) mysql fails because of special characters
 
PHP Code:

new szText[256];

formatex(szTextsizeof(szText), "%s"nazwa_gracza[id]);
replace_all(szTextsizeof(szText), "'""\'");
formatex(Querycharsmax(Query), "SELECT * FROM codexp WHERE user='%s-%i'"szTextklasa_gracza[id]);
formatex(Querycharsmax(Query), "UPDATE `codexp` SET `level` = '%i', `trieda` = '%i' WHERE `user` = '%s-%i';"poziom_gracza[id], klasa_gracza[id], szTextklasa_gracza[id]) 


TheBladerX 02-07-2019 12:11

Re: insert into (or update) mysql fails because of special characters
 
Quote:

Originally Posted by Airkish (Post 2638560)
PHP Code:

new szText[256];

formatex(szTextsizeof(szText), "%s"nazwa_gracza[id]);
replace_all(szTextsizeof(szText), "'""\'");
formatex(Querycharsmax(Query), "SELECT * FROM codexp WHERE user='%s-%i'"szTextklasa_gracza[id]);
formatex(Querycharsmax(Query), "UPDATE `codexp` SET `level` = '%i', `trieda` = '%i' WHERE `user` = '%s-%i';"poziom_gracza[id], klasa_gracza[id], szTextklasa_gracza[id]) 


Okay, seems like everything works perfectly. Thank you for now.

eat1k 02-07-2019 12:46

Re: insert into (or update) mysql fails because of special characters
 
PHP Code:

mysql_escape_string(dest[],len)
{
    
//copy(dest, len, source);
    
replace_all(dest,len,"\\","\\\\");
    
replace_all(dest,len,"\0","\\0");
    
replace_all(dest,len,"\n","\\n");
    
replace_all(dest,len,"\r","\\r");
    
replace_all(dest,len,"\x1a","\Z");
    
replace_all(dest,len,"'","''");
    
replace_all(dest,len,"^"","^"^"");



TheBladerX 02-07-2019 12:50

Re: insert into (or update) mysql fails because of special characters
 
Quote:

Originally Posted by eat1k (Post 2638572)
PHP Code:

mysql_escape_string(dest[],len)
{
    
//copy(dest, len, source);
    
replace_all(dest,len,"\\","\\\\");
    
replace_all(dest,len,"\0","\\0");
    
replace_all(dest,len,"\n","\\n");
    
replace_all(dest,len,"\r","\\r");
    
replace_all(dest,len,"\x1a","\Z");
    
replace_all(dest,len,"'","''");
    
replace_all(dest,len,"^"","^"^"");



Should I put it that way as it's shown before?
Under this?
PHP Code:

replace_all(szTextsizeof(szText), "'""\'"); 

And one more question, what is the difference between these two?
"'", "\'" and "'","''"

eat1k 02-07-2019 12:55

Re: insert into (or update) mysql fails because of special characters
 
Add this function to your code and use it instead of "replace_all(...".
PHP Code:

mysql_escape_string(szTextcharsmax(szText); 


TheBladerX 02-07-2019 14:09

Re: insert into (or update) mysql fails because of special characters
 
Quote:

Originally Posted by TheBladerX (Post 2638573)
And one more question, what is the difference between these two?
"'", "\'" and "'","''"



All times are GMT -4. The time now is 07:38.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.