c++libpqlibpqxx

Input Sanitization with libpq / libpqxx

I'm just getting started implementing some client software for a PostgreSQL database.

The queries will allow input parameters that come from an untrusted source. Therefore I need to sanitize my transactions before actually commiting them.

As for libpq I've found PQescapeStringConn, that may do want I need. However, as my code will be written in C++, I'd prefer to use a libpqxx equivalent. I could not find anything related. (Except probably the Escaper, which however lies in the internal namespace...)

I'd appreciate any suggestions on best practices, reading, links to the documentation, whatever.

Solution

  • Using pqxx::transaction_base::quote is the way to go.

    Here's a simple example:

    // connection to the database
std::string login_str = "TODO: add credentials";
pqxx::connection conn(login_str);
pqxx::work txn(conn);

// a potentially dangerous input string
std::string input = "blah'; drop table persons; --";

// no proper escaping is used
std::string sql_1 = "select * from persons where lastname = '%s'";
std::cout << boost::format(sql_1) % input << std::endl;

// this is how it's done
std::string sql_2 = "select * from persons where lastname = %s";
std::cout << boost::format(sql_2) % txn.quote(input) << std::endl;

    The output is:

    select * from persons where lastname = 'blah'; drop table persons; --'
select * from persons where lastname = 'blah''; drop table persons; --'

    Alternatively, you could consider to rewrite the queries as prepared statements, passing the variables as parameters. Explicit quoting is not needed then.

    For reference: