Ruben's Q1 2024 Update

By Ruben Perez on
Apr 19, 2024

Client-side SQL formatting

A new grand feature has arrived to Boost.MySQL’s city: client-side SQL formatting.

If you’ve worked with MySQL C API before, you may be familiar with mysql_real_escape_string: a function that takes a string and escapes it, enabling the user to compose queries dynamically without the risk of running into SQL injection vulnerabilities. We didn’t have a matching function in Boost.MySQL… until now.

mysql_real_escape_string is a pretty low-level construct. While we have an equivalent function, I also wanted to build higher-level functionality to allow composing dynamic SQL queries in a simple way.

Let’s say you want to insert a bunch of employee records into a table. Until now, the only way to go was use prepared statements, like this:

asio::awaitable<void> insert_employees(
    mysql::any_connection& conn,
    span<const employee> employees
)
{
    assert(!employees.empty());

    // Prepare a statement
    auto stmt = co_await conn.async_prepare_statement("INSERT INTO employee (name, company) VALUES (?, ?)", asio::deferred);

    // Execute it as many times as records we want to insert.
    // Note that this performs a round-trip to the server for each record
    for (const auto& emp: employees)
    {
        co_await conn.async_execute(stmt.bind(emp.name, emp.company), asio::deferred);
    }
}

This can be pretty inefficient, as we’re inserting records one by one. If you’re experienced with SQL, you may be thinking: “why don’t you batch the inserts in a single query”? We can’t do this with prepared statements. But if we had a way to securely compose a query client-side… Fortunately, we now have this!

asio::awaitable<void> insert_employees(
    mysql::any_connection& conn,
    span<const employee> employees
)
{
    assert(!employees.empty());

    // Compose a query string client-side, using fmtlib-like format strings.
    // Formatting will take care of escaping your string values
    mysql::format_context ctx(opts);
    ctx.append_raw("INSERT INTO employee (name, company) VALUES ");
    bool is_first = true;
    for (const auto& emp : employees)
    {
        // Comma separator
        if (!is_first) ctx.append_raw(", ");
        is_first = false;

        // Actual record
        mysql::format_sql_to(ctx, "({}, {})", emp.name, emp.company);
    }
    std::string query = std::move(ctx).get().value();

    co_await conn.async_execute(query, asio::use_awaitable);
}

And if you’re issuing simple queries, you may be able to avoid prepared statements altogether:

asio::awaitable<employee> get_employee_by_id(
    mysql::any_connection& conn,
    std::int64_t id
)
{
    mysql::static_results<employee> r;
    co_await conn.async_execute(
        mysql::format_sql(conn.format_opts().value(), "SELECT * FROM employee WHERE id = {}", id),
        asio::deferred
    );
    co_return std::move(r.rows().at(0));
}

This feature enables lots of other complex use cases, like dynamic filters, patch-like updates and pipelining.

Boost.PFR integration and maintenance work

Boost.PFR users may be happy to know that static_results and friends now support PFR types! This will become available in Boost 1.86.

Additionally, I’ve been doing some extra maintenance work to attempt to deliver a Boost 1.85 as high-quality as possible.

Boost.Charconv

Many of you may have heard that Boost.Charconv, by Matt Borland, is now a proud member of Boost! Charconv is a high-quality polyfill library that provides functionality like std::to_chars and std::from_chars in C++11. I desperately needed this for Boost.MySQL, as text queries parse ints and doubles, and client-side formatting serializes them. Locale-independence is particularly important for the latter, as SELECT 4.2 is valid SQL, while SELECT 4,2 may open the door to a SQL injection vulnerability. Not fun.

I feel that just reading the documentation and playing with a library is sometimes not enough to emit a useful review. I thought I could do better this time, and took a different approach to the review: I’d try to use the library in Boost.MySQL and report my experience.

I’m pretty satisfied with the results, as focusing on a real use case avoids bike-shedding and provides good feedback. As Boost.MySQL has an extensive test suite, rewriting part of it makes the library build and run under many different scenarios, usually uncovering subtle integration issues (e.g. with CMake files). I’ve been very happy with the quality of the library and the author’s response to my feedback, and I’ve decided to be an early adopter - Boost.MySQL 1.85 uses Charconv extensively.

Boost.Parser

Since the new review approach had worked great, I decided to repeat it for Boost.Parser. I rewrote the client-side SQL formatting feature, which parses format strings, using Boost.Parser. Again, it by uncovering a couple of issues.

I can’t use Boost.Parser in MySQL because it requires C++17 (and is an overshot, considering my simple use case). But I feel the review was helpful for Boost’s overall quality.

Other contributions

I’ve also helped Boost.Redis’ author to set up part of his integration testing CI, following what I learnt setting up Boost.MySQL CIs. I’ve also refactored MySQL’s build scripts to be more compliant with Boost best-practices, which has provided me some exposure to Boost’s internal machinery.

All Posts by This Author