I have a web page written in PHP (version 7.3.12), where an user can write any SQL statement and then execute it (I am using MariaDB version 10.4.10, included in XAMPP). When user writes DDL statements, most of them are unfortunately autocommited, hence there is no space for rollbacking a transaction. I don't want them to e.g. create 200 tables in a database that would wihtout any intervention persist in the database forever. Is there a way to workaround this problem?
Also I was wondering if besides temporary tables exists something like a temporary database? I was thinking about this, because if I could create some sort of a temporary database (that would be visible to session only), user can practically do anything without me worrying any leftovers would stay in the DB.
Thanks for any advice
Data definition language (DDL) statements in MariaDB are not part of its transaction semantics, so there's no way to roll back DDL using ROLLBACK.
With the appropriate code in your php application, you can create a uuniquely named database for each user session, then drop it at the end of the session. This is how the various database fiddle sites work.