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

TheBladerX · **Author**

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(Query, charsmax(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(Query, charsmax(Query), "SELECT * FROM codexp WHERE user='%s-%i'", nazwa_gracza[id], klasa_gracza[id])

formatex(Query, charsmax(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 · *Last edited by Airkish; 02-07-2019 at 12:03.*

PHP Code:


			
new szText[256];

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

TheBladerX · *Last edited by TheBladerX; 02-07-2019 at 12:36.*

Quote:

Originally Posted by Airkish

PHP Code:


			
new szText[256];

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

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

eat1k · *Last edited by eat1k; 02-07-2019 at 12:47.*

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 · *Last edited by TheBladerX; 02-07-2019 at 12:54.*

Quote:

Originally Posted by eat1k

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(szText, sizeof(szText), "'", "\'");

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

eat1k

Add this function to your code and use it instead of "replace_all(...".

PHP Code:


			
mysql_escape_string(szText, charsmax(szText);

TheBladerX

Quote:

Originally Posted by TheBladerX

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