1

I am working on an education project for students to test their skills on SQL; we create some questions and ask students to solve them.

The problem comes here:

  1. How do I create SQL questions? I mean, suppose I asked a student to create a table with some constraints and data types, where is this table going to be created? In my production database? I guess not; if I exposed my database to enduser [evils], they will try to damage it.
  2. How do I validate the result sets [Create/Alter/Insert/Update/Rename]?
  3. How can I establish isolation for answers for different users?

I don't know how SQL Fiddle handles these scenarios, but my requirement is the same as SQL Fiddle.

I found Validation of Scripts but this specific to SQL Server, and my question is not restricted to one platform (DBMS).

Community
  • 1
  • 1
joshua
  • 2,371
  • 2
  • 29
  • 58
  • Why not just use SQL Fiddle? Also, if you're curious as to how SQL Fiddle does something, feel free to take a look at my source on GitHub - it's all out there: https://github.com/jakefeasel/sqlfiddle – Jake Feasel Dec 26 '12 at 05:52
  • i don't know there is support by you,API ? – joshua Dec 26 '12 at 05:54
  • What do you need to provide that isn't available simply from the UI? Surely you could just give your students links to a started schema fiddle (something like http://sqlfiddle.com/#!2/a2581 ) and then have them send you links back with their completed query. – Jake Feasel Dec 26 '12 at 05:55
  • i appreciate your suggestion,but i am restricted on this, – joshua Dec 26 '12 at 05:59
  • That's unfortunate. See my answer for a suggestion on building it yourself. You might consider cloning a copy of sql fiddle and running it locally for this problem, if building your own app is too much to expect. – Jake Feasel Dec 26 '12 at 06:08
  • Jake Feasel thanks, by your answer i got some insight. how to proceed, sorry i don't know cold fusion. :) – joshua Dec 26 '12 at 06:17
  • I doubt you'd really need to modify any of the CF code, and you don't need to know it at all to get it running locally - just see the readme on github. It's pretty straightforward MVC stuff. – Jake Feasel Dec 26 '12 at 06:19
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21699/discussion-between-joshua-and-jake-feasel) – joshua Dec 26 '12 at 06:20

1 Answers1

2

If you're looking to provide a free-form window into your SQL server that allows students to execute queries but not destroy the database (as SQL Fiddle does), then the simplest thing to do is to build a webapp that takes their SQL as input and runs it within a transaction. Capture the result sets from each query as you execute it. After executing each of their SQL statements, roll back the transaction; none of their changes will then be committed to the server.

If you suspect your students are incredibly mischievous and will stop at nothing to break the database (as I have with SQL Fiddle users), then you will have to guard against explicit transaction commits, such as commit transaction;. Guarding against this is highly database-specific; I recommend looking through my code on github to see how I protect the various databases for SQL Fiddle (hint - the easiest server to protect is PostgreSQL; use it if you can). You may also find it interesting to read through my question on dba.se on the subject of preventing explicit commits: Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Community
  • 1
  • 1
Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
  • 2
    Although is *all* DDL transaction-safe? Linked execution? Preventing unwanted transactions? Somehow I think that this might not be "safe" enough .. also, don't forget DOS/resource attacks. –  Dec 26 '12 at 06:11
  • @pst - It is highly database specific. Here's a good article comparing transactional ddl in various databases: http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis . Preventing resource attacks is also vendor specific: MS SQL uses a cost governor; Oracle has resource limits for a profile; others have to simply use a connection timeout. – Jake Feasel Dec 26 '12 at 06:12
  • (My point is the initial paragraph in this reply is not sufficient and glosses over many issues and implies some "safety"; it is first but not as important as the rest.) –  Dec 26 '12 at 06:13
  • @pst yes there are a lot of potential things to consider. This is really why my first suggestion (in comments) was for him to use SQL Fiddle. Second, it was for him to build a local copy of sql fiddle (for some reason directly using it isn't an option). There are a lot of issues to deal with in this realm that I've already tried to address in my app. – Jake Feasel Dec 26 '12 at 06:17