Error when trying to INSERT INTO mysql with boost.beast

Question

I am trying to insert a string into a mysql database and am getting the error terminate called after throwing an instance of 'sql::SQLException' what(): Aborted (core dumped) Before I try insert into I'm getting a table from the database successfully so I don't understand why mysql isn't liking it when I try to insert into the table. Below is my server code.

void handle_request(http::request<Body, http::basic_fields<Allocator> > &&req, Send &&send) {
// Returns a bad request response
auto const bad_request = [&req](boost::beast::string_view why) {
    http::response<http::string_body> res{ http::status::bad_request, req.version() };
    res.set(http::field::server, BOOST_BEAST_VERSION_STRING);
    res.set(http::field::content_type, "text/html");
    res.keep_alive(req.keep_alive());
    res.body() = why.to_string();
    res.prepare_payload();
    return res;
};

// Make sure we can handle the method
if (req.method() != http::verb::get)
    return send(bad_request("Unsupported HTTP-method"));

// Request path must be absolute and not contain "..".
auto target = req.target();
if (target.empty() || target[0] != '/' || target.find("..") != boost::beast::string_view::npos)
    return send(bad_request("Illegal request-target"));

std::string data = "";
sql::Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
sql::ResultSet *resS;

sql::Driver *driver1;
sql::Connection *con1;
sql::Statement *stmt1;
sql::ResultSet *resS1;
// Create a connection 

driver = get_driver_instance();
con = driver->connect("tcp://111.111.111.111:3306", "root", "password");
con->setSchema("server");
stmt = con->createStatement();
resS = stmt->executeQuery("SELECT * FROM `address`");
while(resS->next()){
  data = data + " " + resS->getString("publicIP");
}
//parsing IP
int len = temppubIP.length();
while(temppubIP[len-1] != ':'){
    temppubIP.pop_back();
    len--;
}
temppubIP.pop_back();
driver1 = get_driver_instance();
con1 = driver1->connect("tcp://111.111.111.111:3306", "root", "password");
con1->setSchema("server");
stmt1 = con1->createStatement();
//~~~~~~~~~HERE~~~~~~~~~
stmt1->executeQuery("INSERT INTO address (publicIP) VALUES ('"+temppubIP+"')");
delete stmt;
delete con;
delete stmt1;
delete con1;
std::cout<<"t2"<<std::endl;
   // std::string pubIP = boost::lexical_cast<std::string> 
(socket.remote_endpoint());
//std::cout<<pubIP<<std::endl;

http::response<http::string_body> res{ http::status::ok, req.version() };
res.set(http::field::server, BOOST_BEAST_VERSION_STRING);
res.set(http::field::content_type, "text/html");
res.body() = data;
res.prepare_payload();
res.keep_alive(req.keep_alive());
std::cout<<"t4"<<std::endl;
return send(std::move(res));
}

The code works all the way up to where I have commented //~~~~~~~HERE~~~~~~~ Then the following line stmt1->executeQuery("INSERT INTO address (publicIP) VALUES ('"+temppubIP+"')"); causes the error. But even though the server crashes it still does save the string temppubIP to the mysql db table. If i comment out the INSERT INTO line the server doesn't crash when a client uses a GET request on it and receives the database table. So my goal here is to have the server be running, and when a client connects to it the server gets the database table then posts a string to the database then sends the database table back to the client. The INSERT INTO seems to be the only thing not working.

P.S. I'm new to boost.asio and beast. I'm pretty sure I only need to connect to mysql once but tried two connections when trying to debug this issue I have.

If anyone has any insight or suggestions it is much appreciated, Thank You!

What does the value of `temppubIP` look like when you try to use it to the `INSERT` statement? You might need to escape it. Better would be to use a parameterized query instead. Manually building SQL statements like this is an SQL Injection attack waiting to happen. Also, your "parsing IP" logic looks suspiciously error-prone, what does `temppubIP` look like before you start parsing it? What exactly are you "parsing"? — Remy Lebeau, Aug 13 '18 at 21:59
It is a string that's an IP so "111.111.111.111" can you explain more about escaping it and I should use a parameterized query instead of the executeQuerry I am using. Before the IP looks like "111.111.111.111:22222" — Jack47, Aug 13 '18 at 22:03
You should not be using a `while` loop to `pop_back()` the port number from the IP. Use [`temppubIP.find_last_of()`](https://en.cppreference.com/w/cpp/string/basic_string/find_last_of) to find the `':'` delimiter, and if found then use [`temppubIP.erase()`](https://en.cppreference.com/w/cpp/string/basic_string/erase) to remove the delimiter and port number. — Remy Lebeau, Aug 13 '18 at 22:11
len never becomes 0 because it exits when reaching ":" If I print temppubIP it is the correct string. Also as I explained above the temppubIP is successfully stored in the mysql database. The server just crashes immediately after before it finishes handling the HTTP request — Jack47, Aug 13 '18 at 22:12
Then you need to debug your code to find the culprit. StackOverflow is not a debugging service. And since you know the culprit is an SQL exception, you might consider adding some `try..catch` blocks to your code to handle DB errors instead of letting them terminate your app. — Remy Lebeau, Aug 13 '18 at 22:16
If I do a `try..catch` I get the error `# ERR: SQLException in asyncServer.cpp(handle_request) on line 99 # ERR: (MySQL error code: 0, SQLState: 00000 )` The server doesn't crash and works how I wanted it to I just don't understand why that error would be appearing — Jack47, Aug 13 '18 at 22:29
You are just going to have to debug into it and find out for yourself. — Remy Lebeau, Aug 13 '18 at 22:34

Error when trying to INSERT INTO mysql with boost.beast

0 Answers0