...one of the most highly
regarded and expertly designed C++ library projects in the
world.
— Herb Sutter and Andrei
Alexandrescu, C++
Coding Standards
Until now, our SQL queries were hard-coded string literals. However, most real-world use cases involve running queries containing user-supplied parameters.
In this tutorial, we will be using an employee database. You can obtain this
sample database by sourcing the example/db_setup.sql
file
in Boost.MySQL source code repository.
The employee table is defined as:
CREATE TABLE employee( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, ... -- other fields not relevant for us );
We will write a program that retrieves an employee by ID and prints their full name. The employee ID will be supplied by the user as a command line argument. In more realistic examples, you may get it from a file or HTTP request.
We need to build a query like the following:
SELECT first_name, last_name FROM employee WHERE id = <actual-id>
Replacing <actual-id>
by the value passed by the user.
Since we don't control the employee ID, we must consider it untrusted. We must never use raw string concatenation to build queries. Otherwise, malicious values can cause SQL injection vulnerabilities, which are extremely severe.
Boost.MySQL offers two options to deal with this:
In this tutorial, we will use client-side generation (termed client-side SQL formatting throughout the documentation).
any_connection::async_execute
can also deal with queries with parameters. We need to replace the string literal
by a call to with_params
,
passing a query template and the actual values of the parameters:
// Execute the query with the given parameters. When executed, with_params // expands the given query string template and sends it to the server for execution. // {} are placeholders, as in std::format. Values are escaped as required to prevent // SQL injection. mysql::results result; co_await conn.async_execute( mysql::with_params("SELECT first_name, last_name FROM employee WHERE id = {}", employee_id), result );
The query template uses a syntax similar to std::format
.
You can use numbers, strings, dates, times and many other types as parameters.
More information about client-side SQL formatting is available in this
page.
Our query might return either one row (if an employee is found) or none (if no employee with the given ID exists). Accounting for this:
// Did we find an employee with that ID? if (result.rows().empty()) { std::cout << "Employee not found" << std::endl; } else { // Print the retrieved details. The first field is the first name, // and the second, the last name. mysql::row_view employee = result.rows().at(0); std::cout << "Employee's name is: " << employee.at(0) << ' ' << employee.at(1) << std::endl; }
If you've run example/db_setup.sql
, the employee
table exists within the boost_mysql_examples
database. To use this table without qualification, we need to specify a database
name when connecting. This is achieved by setting connect_params::database
:
// The hostname, username, password and database to use. mysql::connect_params params; params.server_address.emplace_host_and_port(std::string(server_hostname)); params.username = username; params.password = password; params.database = "boost_mysql_examples";
Since we're connecting and closing the connection in our coroutine, it makes
sense to make it a local variable, instead of passing it as parameter. Recall
that we need a reference to an execution context (i.e. io_context
)
to build a connection. We could pass the io_context
as a parameter to our coroutine, but there's a simpler way: coroutines already
hold a reference to where they are executing:
// The connection will use the same executor as the coroutine mysql::any_connection conn(co_await asio::this_coro::executor);
The expression co_await asio::this_coro::executor
retrieves the executor that our coroutine
is using. An executor is a lightweight handle to an execution context, and
can be used to create our connection.
![]() |
Note |
---|---|
|
With all these changes, this is how our coroutine looks like:
asio::awaitable<void> coro_main( std::string_view server_hostname, std::string_view username, std::string_view password, std::int64_t employee_id ) { // The connection will use the same executor as the coroutine mysql::any_connection conn(co_await asio::this_coro::executor); // The hostname, username, password and database to use. mysql::connect_params params; params.server_address.emplace_host_and_port(std::string(server_hostname)); params.username = username; params.password = password; params.database = "boost_mysql_examples"; // Connect to the server co_await conn.async_connect(params); // Execute the query with the given parameters. When executed, with_params // expands the given query string template and sends it to the server for execution. // {} are placeholders, as in std::format. Values are escaped as required to prevent // SQL injection. mysql::results result; co_await conn.async_execute( mysql::with_params("SELECT first_name, last_name FROM employee WHERE id = {}", employee_id), result ); // Did we find an employee with that ID? if (result.rows().empty()) { std::cout << "Employee not found" << std::endl; } else { // Print the retrieved details. The first field is the first name, // and the second, the last name. mysql::row_view employee = result.rows().at(0); std::cout << "Employee's name is: " << employee.at(0) << ' ' << employee.at(1) << std::endl; } // Close the connection co_await conn.async_close(); }
Full program listing for this tutorial is here.
You can now proceed to the next tutorial.