Re: [boost] Boost MySQL Review Starts Today

On 11.05.22 23:06, Ruben Perez via Boost wrote:

On Wed, 11 May 2022 at 07:52, Rainer Deyke via Boost wrote:

...

On 10.05.22 16:59, Rainer Deyke via Boost wrote:

...

I took a quick look, and my first impression is that the library doesn't do enough to prevent SQL injection attacks. Yes, text queries are convenient when the full query is known at compile-time. Yes, security is ultimately the responsibility of those who use the API. Yes, this is C++, where far worse security flaws are a constant threat. Even so, connection::query gives me shivers.

So, I've been thinking about what the library can do to prevent SQL injection attacks. Ideas: - connection::query can be renamed to connection::unsafe_query.

That reminds me of React naming. It inspires me "don't use this function unless you're really knowledgeable of the library's internals and you know what you are doing". I don't think that's the case. For example, if you're using transactions, you'll be calling `conn.query("COMMIT")`. I'm not very keen on making this `conn.unsafe_query("COMMIT")`.

Two possible workarounds: - Allow conn.query("COMMIT") but disallow conn.query("SELECT * FROM employee WHERE company_id = 'HGS'"), somehow. - Provide conn.commit() which calls conn.unsafe_query("COMMIT"). I'm not really too fond of either of them.

...

- Syntax sugar for a one-off parametrized query wouldn't hurt either.

This requires a decent amount of work, as it requires implementing SQL sanitizing client-side. I'm not very keen on it, as it's very possible to get it wrong and end up introducing a vulnerability that wouldn't have existed with prepared statements. I can have a look at how complex would this be if the community thinks it really adds a lot of value.

What if this worked by creating a prepared statement behind the scenes? The idea of a one-off query is that it is only used once per program invocation, so the performance cost of constructing a prepared statement should be negligible. -- Rainer Deyke (rainerd@eldwood.com)