Thread: Sanitizing MySQL Query?

Code:

MYSQL_OBJ *_mysql_query(char *query, bool MULTI)
{
  if (mysql_connection) {
    MYSQL_ROW mysqlRow;
    MYSQL_FIELD *mysqlFields;
    MYSQL_RES *mysqlResult = NULL;
    my_ulonglong numRows;
    unsigned int numFields;
    char *queryString = malloc(MAX_BUFFER);
    MYSQL_OBJ *mysqlOBJ = malloc(sizeof(*mysqlOBJ));

    sprintf(queryString, "%s", query);

    int mysqlStatus = mysql_query(mysql_connection, queryString);

    if (mysqlStatus) {
      bug("MySQL Error: %s", mysql_error(mysql_connection));
    } else {
      mysqlResult = mysql_store_result(mysql_connection);
    }

    if (mysqlResult) {
      numRows = mysql_num_rows(mysqlResult);
      numFields = mysql_field_count(mysql_connection);
      numFields = mysql_num_fields(mysqlResult);
      mysqlFields = mysql_fetch_fields(mysqlResult);
      mysqlRow = MULTI ? NULL : mysql_fetch_row(mysqlResult);
    }

    mysqlOBJ->queryString = query;
    mysqlOBJ->mysqlResult = mysqlResult;
    mysqlOBJ->mysqlFields = mysqlFields;
    mysqlOBJ->mysqlRow = mysqlRow;
    mysqlOBJ->numFields = numFields;
    mysqlOBJ->numRows = numRows;

    return mysqlOBJ;
  }

  return NULL;

}

How would I go about this?

I'm new to C so bare with me :P

christop's advice is sound, though there is a general case where you would have to manually sanitise certain values: when those values are used as quoted identifiers (and hence cannot be bound to parameters in a prepared statement), in which case you have to double any quotes (backticks in normal MySQL dialect; double quotes if you're being ANSI compliant) within the value to escape it.

Can you please use "copy-as-text" in your IDE, or "paste-as-text" in your browser.
I'm fed up of fixing the colour eyesore that you post by default.

Also, malloc and mysql_real_escape_string return error values you should check for.

Originally Posted by thunderz1337

Yeah I that's exactly what I was trying to do anyway I figured it out and come up with this seems to work fine...need to read more lol.

No, that was not what you were trying to do. If it was, then mysql_real_escape_string would be wrong because it escapes strings for use as string values in an SQL statement. It doesn't escape values for use in a quoted identifer in an SQL statement.

Originally Posted by thunderz1337

basically just trying to escape user input values and this works.

Don't escape: bind them to parameters in a prepared statement. It's better because you separate the SQL statement that is executed from the data that it operates on. This can be more efficient because if you have multiple sets of data, you only need to prepare the statement once and then bind the different sets of values for execution; it is more secure because SQL injection involves modifying the SQL statement in unexpected ways, but with prepared statements data is just data, not part of the SQL statement, and hence cannot modify it (unless the prepared statement is emulated).