MySQL Connector C/C API - Query with special characters

Question

I a C program I have a function that takes in parameter a domain name:

void db_domains_query(char *name);

With mysql_query() I test if the domain name is existing in a database. If it's not the case, I insert the new domain name:

...
char *query[400];

sprintf(query, "SELECT Id, DomainName FROM domains WHERE domainName LIKE '%s'", name);
if (mysql_query(con, query))
finish_with_error(con);

MYSQL_RES *result = mysql_store_result(con);
    if (result == NULL)
        finish_with_error(con);

    MYSQL_ROW row;
    if ((row = mysql_fetch_row(result)))
        printf("Element exists : %s %s\n", row[0], row[1]);
    else
        printf("Element %s doesn't found\n", name);
        // Then insert the new domain name ...

This portion of code works perfectly if name contains only "normal characters". However, for domain names that contain "special characters" the query seems incorrect even if those are in the database for instance :

• name = undaben.de : Element exists : 100 undaben.de

• name = ®here.com : Element ®here.com is not found.

• name = §travel.us : Element §travel.us is not found.

  Extract of the table :

  id	domainname
  100	undaben.de
  162	§travel.us
  197	®here.com

The collation of the field domainname is utf8_unicode_ci. So how can I pass to mysql_query all domain names including the "special" ones ?

Answer 1

I recommend you to avoid the C API unless you have a compelling reason to use it. The C++ API es way more usable.

You are embedding your arguments within your query string. This has a number of problems, including security risks. If you insist in this approach, in order to prevent problems with parameters messing with your query, you need to ensure a few things:

• Make sure that your data encoding matches the encoding of the MySQL Client connection (this may be different from your database encoding). If your connection is set up as UTF-8, then you need to make sure that special characters such as © are encoded also in UTF-8 when used as input to the sprintf function.
• You also need to protect from other SQL escape characters (like '). For this you can use the mysql_real_escape_string function, as mentioned in Efficiently escaping quotes in C before passing to mysql_query.

However, you should very likely be using prepared statements which circumvent these issues. You still need to make sure that your input data encoding matches the encoding of your client connection, but everything else shall be easier to handle.

I paste an example of a parameterized query using the C API with prepared statements looks like (example from http://lgallardo.com/2011/06/23/sentencias-preparadas-de-mysql-en-c-ejemplo-completo/). Note the example is for integers, not strings, you need to adapt to your use case.

 sql = "select count(*) from addresses where id = ?";

 // Open Database
 openDB(&conn);

 // Allocate statement handler
 stmt = mysql_stmt_init(conn);

 if (stmt == NULL) {
  print_error(conn, "Could not initialize statement handler");
  return;
 }

 // Prepare the statement
 if (mysql_stmt_prepare(stmt, sql, strlen(sql)) != 0) {
  print_stmt_error(stmt, "Could not prepare statement");
  return;
 }

 // Initialize the result column structures
 memset (param, 0, sizeof (param)); /* zero the structures */
 memset (result, 0, sizeof (result)); /* zero the structures */

 // Init param structure
 // Select
 param[0].buffer_type     = MYSQL_TYPE_LONG;
 param[0].buffer         = (void *) &myId;
 param[0].is_unsigned    = 0;
 param[0].is_null         = 0;
 param[0].length         = 0;

 // Result
 result[0].buffer_type     = MYSQL_TYPE_LONG;
 result[0].buffer         = (void *) &myNumAddresses;
 result[0].is_unsigned    = 0;
 result[0].is_null         = &is_null[0];
 result[0].length         = 0;

 // Bind param structure to statement
 if (mysql_stmt_bind_param(stmt, param) != 0) {
  print_stmt_error(stmt, "Could not bind parameters");
  return;
 }

 // Bind result
 if (mysql_stmt_bind_result(stmt, result) != 0) {
  print_stmt_error(stmt, "Could not bind results");
  return;
 }

 // Set bind parameters
 myId            = id;

 // Execute!!
 if (mysql_stmt_execute(stmt) != 0) {
  print_stmt_error(stmt, "Could not execute statement");
  return;
 }

 if (mysql_stmt_store_result(stmt) != 0) {
  print_stmt_error(stmt, "Could not buffer result set");
  return;
 }

 // Init data
 (*numAddresses) = 0;

 // Fetch
 if(mysql_stmt_fetch (stmt) == 0){
  (*numAddresses) = myNumAddresses;
 }

 // Deallocate result set
 mysql_stmt_free_result(stmt); /* deallocate result set */

 // Close the statement
 mysql_stmt_close(stmt);

 // Close Database
 closeDB(conn);

Again, if you can use some other client library (like the C++ client) your code will be way shorter and readable.

Answer 2

My bad, as @jjmontes mentioned it seems that the sent string was encoded in 'latin1'.

Using the function mysql_set_character_set(conn, "utf8") before doing the query solved this problem. Now, I will try to use prepared statements instead of query strings.

thanks again!