Re: Boost MySQL Review Starts Today

From: Rainer Deyke (rdeyke_at_[hidden])
Date: 2022-05-12 05:59:28

On 11.05.22 23:06, Ruben Perez via Boost wrote:
> On Wed, 11 May 2022 at 07:52, Rainer Deyke via Boost
> <boost_at_[hidden]> 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_at_[hidden])