Re: Review of Boost.MySql

From: Dominique Devienne (ddevienne_at_[hidden])
Date: 2022-05-13 13:47:35

Meant to send this to the list, not just Ruben. Re-sending. --DD

On Fri, May 13, 2022 at 11:59 AM Dominique Devienne <ddevienne_at_[hidden]> wrote:
>
> On Thu, May 12, 2022 at 9:02 PM Ruben Perez <rubenperez038_at_[hidden]> wrote:
> > > > The server sends several resultsets after that. I haven't focused a lot on
> > > > this because it sounded risky (in terms of security) for me.
> > >
> > > Not sure what's risky here. Maybe I'm missing something.
> >
> > I was just imagining users concatenating queries. May be a misconception,
> > yours is a legitimate use case.
>
> I've thought more about this, and I'm guessing you were thinking
> about this XKCD https://xkcd.com/327/ SQL Injection.
>
> The proper and ideal remedy is not the XKCD one though,
> it is using prepared statements and binding values.
>
> The still proper but less ideal next remedy is a "safe" way to concatenate
> user inputs into textual SQL. I.e. the equivalent of
> SQLite: https://www.sqlite.org/printf.html with %q and %Q
> PostgreSQL: https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQESCAPELITERAL
> Since you want to avoid the MySQL client, which I suppose has this
> too, you have to provide your own.
>
> Third and finally, this XKCD hack indeed does only work is one can
> execute several semi-colon separated statements.
> So on second thought, it is *definitely* be a good thing to have
> separate APIs, to execute single statement, as opposed from executing
> "scripts" of statements, plural. With a single-statement API, *even*
> the XKCD hack won't work, since it would yield an invalid single
> statement.
> This is what you had in mind IMHO :).
>
> So the ability to execute multi-statement is important, especially in
> my DDL use-case,
> but it should be a separate API from the more regular single-statement
> APIs, and come
> with extra warnings in the document about such SQL injections (with
> obligatory link to XKCD ;)
>
> PS: Fourth, the XKCD hack is possible only of the SQL is executed as
> the owner of the schema,
>   or a DBA/Superuser, which is of course a mistake and a big no-no,
> yet all too common...
>   Users of the DB, including mid-tier services, should NOT have DDL
> privileges on the schema and its objects.
>   Change the DROP into a DELETE, and that's still nasty, but then you
> use ROLEs to restrict some DMLs,
>   and possibly also RLS (Row Level Security) for finer-grained
> authorization. But we are getting off-topic :)