2

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.

moooeeeep
  • 31,622
  • 22
  • 98
  • 187

2 Answers2

3

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

For reference:

moooeeeep
  • 31,622
  • 22
  • 98
  • 187
0

Actually in order to give a better view, I was having an issue with this kind of things this week and we started using std::string pqxx::transaction_base::esc

You just have to add it in the argument you going to insert or update, and it will do the job. The quote function mentioned up there, its add the quote to the argument, but it does not fix the problem.

For example; if you do something like UPDATE person set name = w.quote(name) where id = 1; There you are using the quote correctly in order to put between quotes the argument. So in order to insert a single quote or avoid SQL Injection, you have to do: UPDATE person set name = + "'" + w.esc(name) + "'" where id = 1 OR UPDATE person set name = w.quote(w.esc(name)) where id = 1; Being W the pqxx::work variable already initialized with the connection to the database.

thecatbehindthemask
  • 413
  • 1
  • 6
  • 15
  • I still don't see why you would want escaping without the quotes there. – moooeeeep Mar 26 '19 at 16:47
  • Don't get what you say. I am saying that for me the esc function made the job instead the quote function + boost. If I need to update an string coming from an untrusted source like: plan'. What I want to do is update the column name for example with the word plan', and quote function did not solve my problem, I used the esc function to be able to update the column in the db. It's written here after all the functions explanation. http://pqxx.org/devprojects/libpqxx/doc/4.0/html/Reference/a00203.html – thecatbehindthemask Mar 29 '19 at 11:36
  • 1
    As I understand, it depends on whether you have the quotes in the query string already or not. If you have `select %s` you need `quote(input)` to quote and escape, if you have `select '%s'` you need `esc(input)` to only escape. – moooeeeep Mar 29 '19 at 13:45