Thread: Sanitizing MySQL Query?

  1. #1
    Registered User
    Join Date
    Oct 2019
    Posts
    5

    Question 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
    Last edited by Salem; 10-05-2019 at 03:47 AM. Reason: Removed crayola

  2. #2
    Registered User
    Join Date
    May 2012
    Location
    Arizona, USA
    Posts
    656
    I wouldn't try to "sanitize" an SQL query. It's much easier and foolproof to use prepared statements. See the documentation for how to use those.

  3. #3
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    27,181
    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.
    Quote Originally Posted by Bjarne Stroustrup (2000-10-14)
    I get maybe two dozen requests for help with some sort of programming or design problem every day. Most have more sense than to send me hundreds of lines of code. If they do, I ask them to find the smallest example that exhibits the problem and send me that. Mostly, they then find the error themselves. "Finding the smallest program that demonstrates the error" is a powerful debugging tool.
    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  4. #4
    Registered User
    Join Date
    Oct 2019
    Posts
    5
    Quote Originally Posted by laserlight View Post
    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.

    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.

    Code:
    char *mysql_sanitize(char *string)
    {
      unsigned long length = strlen(string);
      char *toStr = malloc(length * 2 + 1);
      mysql_real_escape_string(mysql_connection, toStr, string, length);
    
      return toStr;
    
    }
    basically just trying to escape user input values and this works.

  5. #5
    and the hat of int overfl Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    37,497
    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.
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.

  6. #6
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    27,181
    Quote 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.

    Quote 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).
    Quote Originally Posted by Bjarne Stroustrup (2000-10-14)
    I get maybe two dozen requests for help with some sort of programming or design problem every day. Most have more sense than to send me hundreds of lines of code. If they do, I ask them to find the smallest example that exhibits the problem and send me that. Mostly, they then find the error themselves. "Finding the smallest program that demonstrates the error" is a powerful debugging tool.
    Look up a C++ Reference and learn How To Ask Questions The Smart Way

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. MySQL query oddity between 2 OS's
    By xwielder in forum C Programming
    Replies: 8
    Last Post: 06-01-2010, 01:12 PM
  2. Help me with MySQL please!
    By SaTaNaMa in forum Windows Programming
    Replies: 3
    Last Post: 07-24-2006, 04:48 PM
  3. DateTime problem in template query (mysql++)
    By _Ramirez_ in forum C++ Programming
    Replies: 0
    Last Post: 04-07-2005, 09:01 AM
  4. mysql++
    By StinkyRyan in forum Linux Programming
    Replies: 1
    Last Post: 07-16-2004, 03:54 AM
  5. C and MySQL, possible?
    By Vber in forum C Programming
    Replies: 3
    Last Post: 11-14-2002, 10:42 AM

Tags for this Thread