CODEWITHSUNDEEP
javadatabasejdbc
Konstantin Milyutin
Konstantin Milyutin

Reputation: 12366

Determining whether query will change DB

How can I determine that particular query will change the state of DB? I use JDBC. Should I use parsing searching for key words like INSERT, DELETE or there are some other ways?

Upvotes: 0

Views: 517

Answers (5)

Ryan Stewart
Ryan Stewart

Reputation: 128849

One way:

  1. Execute the query
  2. Check the update count
  3. If updates are made, do your user check
  4. On a positive response, commit the transaction. On negative, roll it back.

There would be variations. Explore the Statement API to get a feel for your options.

Edit: Ensure autocommit is off, or transactionality is out the window!

More edit: As pointed out by commenters, this isn't a scalable solution. For scalability, transactions have to be kept short, and you don't want to have user think time during an open transaction. If there will be multiple users, find a way to do this that doesn't involve holding a transaction open, perhaps with some creative use of statement caching and rollbacks/commits. For better advice, explain more about your requirements.

Upvotes: 1

jches
jches

Reputation: 4527

Depends. An UPDATE or DELETE might change the DB. If you want to know whether it might change the database, a case-insensitive search for the INSERT, UPDATE, DELETE, or ALTER keywords will tell you there's a possibility of the query changing the database. Although there's nothing preventing something like SELECT * FROM sometable WHERE something = 'update', so watch out for that.

If you want to know whether a change will actually be made, before running the query, you're going to have to know something about the data already in the database. Do a SELECT using the same WHERE clause as the potentially data-altering UPDATE or DELETE statement, and compare the data, for example (this could get messy for queries that use JOIN or other more advanced features of SQL). An easier way would be to use transactions to make the change, check for change, and then roll back the transaction, undoing the changes.

If you want to prevent changes, as other answers suggest you should make use of your DBMS's role-management, and don't grant permission to execute altering queries to the user or role running these queries.

Upvotes: 1

Amir Raminfar
Amir Raminfar

Reputation: 34169

I suggest doing this with MySQL permissions. Have two users, one with read only access and with read/write.

  1. Execute the query with read only user
  2. Check for SQLException and if one is thrown then show your message if they really want to write
  3. Then use the second user.

This I think is the safest way because you are pushing the permission on the database. Otherwise you would have to have a sophisticated sql parser which could be difficult.

Upvotes: 2

Jaime de los Hoyos M.
Jaime de los Hoyos M.

Reputation: 133

I'm not a JDBC expert, but I had seen this and could be helpful to you:

http://www.techfeed.net/blog/index.cfm/2006/4/20/Obtaining-Affected-rows-from-SQL-Query

It gives some techniques for retrieving the number of rows affected by INSERT/UPDATE/DELETE statements. Of course, that isn't of much help for CREATE/DROP TABLE, etc. But it's a start.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

Don't check the query. Instead, make sure the query is run under user credentials that are not allowed to modify the database. You can create multiple users and grant each limited rights. This application should connect using the user that can only select data.

The exact way (and if this is possible at all) depends on the database you use, but I think every modern DBMS supports this.

You could execute the query, catch the exception that you'll probably get when the user has insufficient rights, and after confirmation you can connect using a different user. This way you leave it all to the database and its rights management, which it much more secure and less error prone than parsing the queries yourself.

Upvotes: 3

Related Questions