Insert JSON format in Mysql query using C++

Question

I am using JSON format to save data in my c++ program , i want to send it to MySql database (the table tab has one column with type : TEXT) but the query failed (tested also VARCHAR and CHAR )

this is a part of the code since we are not interrested in the rest

     string json_example = "{\"array\":[\"item1\",\"item2\"], \"not an array\": \"asdf\"}";
   mysql_init(&mysql);  //initialize database connection
   string player="INSERT INTO tab values (\"";
    player+= json_example; 
    player += "\")"; 
    connection = mysql_real_connect(&mysql,HOST,USER,PASSWD,DB,0,NULL,0); 
// save data to database
   query_state=mysql_query(connection, player.c_str());  // use player.c_str()

to show the final query that will be used : cout << player gives :

INSERT INTO tab values ("{"array":["item1","item2"], "not an array": "asdf"}")

using for example string json_example = "some text"; is working but with the json format it is not working , maybe the problem came from the use of curly bracket {} or double quotes "" but i haven't find a way to solve it .

i'm using : mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (armv7l) under raspberry pi 2

Any help will be appreciated , thanks .

Answer 1

Use a prepared statement. See prepared statements documentation in the MySQL reference manual.

Prepared statements are more correct, safer, possibly faster, and keep your code cleaner. You get all those benefits and don't need to escape anything. There is hardly a reason not to use them.

---

Something like this might work. But take it with a grain of salt, because I have not tested or compiled it. It should just give you the general idea:

MYSQL_STMT* const statement = mysql_stmt_init(&mysql);
std::string const query = "INSERT INTO tab values(?)";
mysql_stmt_prepare(statement, query, query.size());
MYSQL_BIND bind[1] = {};
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = json_example.c_str();
bind[0].buffer_length = json_example.size();
mysql_stmt_bind_param(statement, bind);
mysql_stmt_execute(statement);