Does mysql_real_escape_string() Have Unfixable Flaws?

Some skeptics contend that the mysql_real_escape_string() function is fundamentally flawed and cannot reliably protect SQL queries. They point to outdated articles as evidence.

Can It Be Used for Custom Prepared Statements?

Despite these concerns, it is still possible to harness mysql_real_escape_string() to create custom prepared statements. However, it requires careful attention to charset handling.

Solution:

According to the MySQL C API documentation for mysql_real_escape_string(), you should use mysql_set_character_set() to set the character set. This ensures it also affects the character set used by mysql_real_escape_string().

Code Example:

#include 

int main() {
  MYSQL *conn = mysql_init(NULL);
  mysql_real_connect(conn, "localhost", "user", "password", "database", 0, NULL, 0);

  // Change the encoding using mysql_set_charset()
  mysql_set_charset(conn, "utf8");

  // Create a custom prepared statement using mysql_real_escape_string()
  char query[1024];
  mysql_real_escape_string(conn, query, "SELECT * FROM users WHERE username='test'", sizeof(query));

  // Execute the query
  mysql_query(conn, query);

  mysql_close(conn);
  return 0;
}

By following this approach and avoiding SET NAMES/SET CHARACTER SET, you can effectively utilize mysql_real_escape_string() to protect your SQL queries from injections.