How does mysql_real_escape_string() work?

Question

I have to send some string (in C) as a query to MySQL, so i used mysql_real_escape_string() to escape some characters like \0 or \n:

#include <mysql/mysql.h>
int loginQuery(char *Nickname, char *Password)
{

    char bufferutility[READBUFSIZE]="SELECT * FROM user WHERE Nickname='";
    char bufferutility2[READBUFSIZE*2+1];
 
    strcat(bufferutility,Nickname);
    strcat(bufferutility,"' AND Password='");
    strcat(bufferutility,Password);
    strcat(bufferutility,"';");
  
    if(mysql_real_escape_string(conn,bufferutility2,bufferutility,strlen(bufferutility))==(unsigned long)-1){
        printf("\nEscaping error\n");
    }
 
    //code for mysql_real_query() here  
}

But I got this error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\'Hello\n\' AND Password=\'World\n\'' at line 1

What have I done wrong?

Answer 1

mysql_real_escape_string() API function is used to escape certain characters in column values depending on the character set used by the current connection.

So instead of escaping the entire SQL statement, you have to escape the values for Nickname and Password before adding them to the SQL statement string.

Example (untested and without error checking):

char *escNickname, *escPassword;

escNickname= alloca(strlen(Nickname) * 2 + 1);
escPassword= alloca(strlen(Password) * 2 + 1);

mysql_real_escape_string(conn, escNickname, Nickname, strlen(Nickname));
mysql_real_escape_string(conn, escPassword, Password, strlen(Password));

sprintf(bufferUtility, "SELECT * FROM user WHERE Nickname='%s' AND Password='%s'. escNickname, escPassword);