patternsqlMinor
Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state
Viewed 0 times
thesqljdbcdisableputexplicitdatabasecommitsstatereadonly
Problem
Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't inadvertently make the potential abuse worse, but what can you do? I trust you folks.
I would like to prevent any user from issuing explicit 'commit' calls within a given transaction block. From the context of SQL Fiddle, the transaction block is the code that is executed on the right-side panel. Basically, I'm looping over and executing a list of plaintext SQL commands, and I want to ensure that all changes they make will be rolled back at the end of the batch. Normally, their changes do get rolled back, but sometimes there is an explicit 'commit' statement within the text, and so of course my rollback won't work. This explicit commit is very likely from a user attempting to break the schema on SQL Fiddle, so other people working on it will see errors.
Main Desired Result: I would like to disable explicit commits at the JDBC level, if possible. This is because I have to support multiple database backend vendors, and of course each has their quirks at the low level.
Fallback option: If it's not possible to configure JDBC to disable explicit commits, then I'd be open to solutions for detecting explicit commits while processing a batch for each of the following backends: SQL Server, Oracle, MySQL, and PostgreSQL.
For SQL Server, I thought of this solution: parse through the XML query plan for the statement before I execute it, and check for the presence of an entry that matches this XPath:
I think this would work pretty well for SQL Server. However, this approach doesn't work for the other DB types. Oracle's XML execution plan output for explicit commits makes no reference to the fact that that you're running a commit statement (but rather simply repeats the execution plan output from the queries it's committin
I would like to prevent any user from issuing explicit 'commit' calls within a given transaction block. From the context of SQL Fiddle, the transaction block is the code that is executed on the right-side panel. Basically, I'm looping over and executing a list of plaintext SQL commands, and I want to ensure that all changes they make will be rolled back at the end of the batch. Normally, their changes do get rolled back, but sometimes there is an explicit 'commit' statement within the text, and so of course my rollback won't work. This explicit commit is very likely from a user attempting to break the schema on SQL Fiddle, so other people working on it will see errors.
Main Desired Result: I would like to disable explicit commits at the JDBC level, if possible. This is because I have to support multiple database backend vendors, and of course each has their quirks at the low level.
Fallback option: If it's not possible to configure JDBC to disable explicit commits, then I'd be open to solutions for detecting explicit commits while processing a batch for each of the following backends: SQL Server, Oracle, MySQL, and PostgreSQL.
For SQL Server, I thought of this solution: parse through the XML query plan for the statement before I execute it, and check for the presence of an entry that matches this XPath:
//*[@StatementType="COMMIT TRANSACTION"]I think this would work pretty well for SQL Server. However, this approach doesn't work for the other DB types. Oracle's XML execution plan output for explicit commits makes no reference to the fact that that you're running a commit statement (but rather simply repeats the execution plan output from the queries it's committin
Solution
For Oracle, this seems like a good sneaky way of catching COMMITs:
https://stackoverflow.com/a/6463800/790702
What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring.
https://stackoverflow.com/a/6463800/790702
What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring.
Context
StackExchange Database Administrators Q#18971, answer score: 3
Revisions (0)
No revisions yet.