Boost C++ Libraries

...one of the most highly regarded and expertly designed C++ library projects in the world. Herb Sutter and Andrei Alexandrescu, C++ Coding Standards

PrevUpHomeNext

Tutorial 3: queries with parameters

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.

Avoiding SQL injection

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:

  1. Compose the query dynamically in the client, using specialized tools to avoid SQL injection. This option is versatile, simple and appropriate for general use.
  2. Perform parameter substitution server side using prepared statements. This is more complex and better suited for cases involving lots of numeric data or executing same query repeatedly.

In this tutorial, we will use client-side generation (termed client-side SQL formatting throughout the documentation).

Using with_params

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.

Using the retrieved rows

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;
}

Connecting with database

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";

Creating the connection inside the coroutine

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] Note

co_await asio::this_coro::executor does not perform any I/O. It only retrieves the current coroutine's executor.

Wrapping up

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.


PrevUpHomeNext