I used an SQLite database and run an EXPLAIN statement before executing the actual query to verify if there was any attempt to write on the database.
Now, we have migrated to SQL Server and I need to know if a query tries to write on the database or is just a simple SELECT statement. I basically try to avoid any malicious statement.
You can see the estimated query plan of any query in SSMS by clicking the estimated query plan button.
See MSDN.
However, if the user shouldn't be writing to the database, is shouldn't have the permissions to do so. Ensure it belongs to a role that has restricted permissions.
If you do decide to go this route, you could do the following:
set showplan_xml on
go
set noexec on
go
select * from sysobjects
go
set noexec off
go
set showplan_xml off
go
This will return 3 result sets containing a single column of XML. The 2nd result set is the query plan for the actual query (in this case, select * from sysobjects
)
But as noted in my comment, you'd be better off preventing the user having permissions to make any changes.
It's also possible to craft statements that are "only" selects but that are also pretty malicious. I could easily write a select that exclusively locks every table in the database and takes an hour to run.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With