Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

what is the equivalent of EXPLAIN form SQLite in SQL Server?

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.

like image 517
Gabriel Diaconescu Avatar asked Feb 02 '11 09:02

Gabriel Diaconescu


2 Answers

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.

like image 66
Oded Avatar answered Sep 25 '22 16:09

Oded


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.

like image 39
Damien_The_Unbeliever Avatar answered Sep 26 '22 16:09

Damien_The_Unbeliever