Make MySQL literals safe in C++

Question

In my C++ code I get events about data that has to be inserted in the database.

The events produce different threads and I have a BlockingConcurrentQueue that is used in a producer consumer model. Every thread writes (produces) in the queue something like:

INSERT INTO CHAT_COMMENTS (chat_comment_id, comment) values (3,'This is a comment';

The above string generated as

sprintf(insert_statement, "INSERT INTO CHAT_COMMENTS (chat_comment_id, comment) values (%d,'%s')",e->id,e->comment);

A scheduler runs every a while and executes all of these insert statements in a MySQL database. Now, the problem is that comments might have some MySQL special characters as shown below:

cout << comment; // produces "this_% LIKE 'a comment """\m/'DROP TABLE USERS"

INSERT INTO CHAT_COMMENTS (chat_comment_id, comment) values (3,'this_% LIKE 'a comment """\m/'DROP TABLE USERS');

Is there a way to handle these case?

I know that one can use prepared statements, but I am looking for something different as PreparedStatements are not thread safe and also because I want to execute the queries above in batches.

There must be several thousands resources about *SQL Injection* and how to solve it, all over the Internet. — Some programmer dude, Mar 20 '18 at 10:35
@Someprogrammerdude thanks :p Of course I know that. To post here with specific code and my case it seems that I am not satisfied with what I have read so far. — andreas, Mar 20 '18 at 10:37
@RaymondNijland Thanks. as previously said: "I know that one can use prepared statements, but I am looking for something different as PreparedStatements are not thread safe" — andreas, Mar 20 '18 at 12:05
Why not use this https://stackoverflow.com/a/43894724/2548147 makes code in the function synchronized thread safe and you have clear code. — Raymond Nijland, Mar 20 '18 at 13:02

score 1 · Answer 1 · answered Mar 20 '18 at 10:36

1

You're probably looking for the mysql_real_escape_string() function.

answered Mar 20 '18 at 10:36

aaaaaa123456789

5,541
1
20
33

function mysql_real_escape_string can be misused by the programmer that the function isn't safe annymore. – Raymond Nijland Mar 20 '18 at 11:40
@RaymondNijland absolutely. But it can be used to implement this properly, too. It _is_ a valid way to avoid SQL injections, when used properly and carefully. – aaaaaa123456789 Mar 20 '18 at 17:49

score 0 · Answer 2 · answered Mar 20 '18 at 12:36

Don't try and code around SQL injection.

Use prepared statements. If you need to ensure that the prepared statement (or connection) is only used on one thread at a time, associate it with a std::mutex (or similar) that you lock while it is being used.

class threaded_statement;

class threaded_connection
{
    std::unique_ptr<sql::Connection> conn;
    std::mutex mut;
public:
    threaded_statement prepareStatement(const char * stmt)
    {
        std::lock_guard<std::mutex> guard(mut);
        return { conn->prepareStatement(stmt) };
    }
};

class threaded_statement
{
    std::unique_ptr<sql::PreparedStatement> stmt;
    std::mutex mut;
public:
    threaded_statement(sql::PreparedStatement * stmt) : stmt(stmt) {}
    void setInt(int pos, int value) 
    { 
        std::lock_guard<std::mutex> guard(mut);
        stmt->setInt(pos, value);
    }
    void setString(int pos, std::string value) 
    { 
        std::lock_guard<std::mutex> guard(mut);
        stmt->setString(pos, value);
    }
    // etc ...
    void execute()
    { 
        std::lock_guard<std::mutex> guard(mut);
        stmt->execute();
    }
}

That's a lot off code simply `std::recursive_mutex m_mutex; std::unique_lock lk(m_mutex);` before using the prepared statement code would also do the trick. — Raymond Nijland, Mar 20 '18 at 13:05
The point is that the user of `threaded_statement` doesn't see any of it. Specifically they can't forget to lock the associated mutex, or lock the wrong one, or use `std::mutex::lock` directly and be exception-unsafe — Caleth, Mar 20 '18 at 13:06

Make MySQL literals safe in C++

2 Answers2

Don't try and code around SQL injection.