什么是SQL Server中的EXPLAIN表单SQLite？

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.

我使用SQLite数据库并在执行实际查询之前运行EXPLAIN语句,以验证是否有任何尝试在数据库上写入。

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.

现在,我们已经迁移到SQL Server,我需要知道查询是否尝试在数据库上写入,或者只是一个简单的SELECT语句。我基本上试图避免任何恶意声明。

2 个解决方案

#1

You can see the estimated query plan of any query in SSMS by clicking the estimated query plan button.

通过单击估计的查询计划按钮,您可以在SSMS中查看任何查询的估计查询计划。

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.

但是,如果用户不应该写入数据库,则不应该具有这样做的权限。确保它属于具有受限权限的角色。

#2

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)

这将返回包含单列XML的3个结果集。第二个结果集是实际查询的查询计划(在这种情况下,从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.

也可以制作“仅”选择但非常恶意的语句。我可以轻松地编写一个选择,专门锁定数据库中的每个表,并运行一个小时。

#1