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@gmail.com> wrote:

On Thu, May 12, 2022 at 9:02 PM Ruben Perez <rubenperez038@gmail.com> 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-PQESCAPELITERA... 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 :)