SqlServer 可以配置为不执行作为字符串提交的存储过程吗？
场景 A：
SqlConnection con = new SqlConnection(myConnString);
SqlDataAdapter adp = new SqlDataAdapter("EXEC spGetUserInfo 42", con);
DataSet ds;
adp.Fill(ds);
场景 B：
SqlConnection con = new SqlConnection(myConnString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spGetUserInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", 42));
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds;
adp.Fill(ds);
问题
在讨论如何加强 SqlServer 抵御 SQL 注入攻击，而无需修改调用数据库中，有人提出这样的问题：是否可以将 SqlServer 配置为简单地不执行以场景 A 的方式编写的存储过程，而只允许以场景 B 的方式编写的执行请求。理论是，如果场景 A 很容易受到注入，底层应用程序没有执行输入验证，允许执行诸如“EXEC spGetUserInfo 42; drop database foo; --”之类的内容，而当SqlServer无法转换“42; drop database foo; -”时，场景B将无法执行-” 为整数。
是否可以配置SqlServer不执行场景A方式调用的存储过程？
Scenario A:
SqlConnection con = new SqlConnection(myConnString);
SqlDataAdapter adp = new SqlDataAdapter("EXEC spGetUserInfo 42", con);
DataSet ds;
adp.Fill(ds);
Scenario B:
SqlConnection con = new SqlConnection(myConnString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spGetUserInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserID", 42));
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds;
adp.Fill(ds);
The question
In a discussion of how to fortify SqlServer against sql injections attacks separately from modifying the underlying application code calling the database, the question was raised whether SqlServer could be configured to simply not execute stored procs written in the manner of Scenario A, only allowing execution requests written in the manner of Scenario B. The theory being that Scenario A would be vulnerable to injection if the underlying app didn't perform input validation, allowing something like "EXEC spGetUserInfo 42; drop database foo; --" to possibly be executed, whereas Scenario B would simply fail to execute when SqlServer fails to convert "42; drop database foo; --" to an integer.
Is it possible to configure SqlServer to not execute stored procs called in the manner of Scenario A?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
据我所知，还没有，而且我已经非常努力地寻找一种方法来做到这一点。
我能想到的最接近的事情是以某种方式对网络进行一些操作，以便仅允许与 SQL Server 的 RPC 连接，因为“.StoredProcedure”调用通常通过 RPC 进行，但动态 SQL请求不能。但是，我对网络管理和服务器管理了解不够，不知道这是否可行。
另一种（也是更标准的）方法是简单地授予用户/应用程序仅对数据库的 CONNECT 访问权限，但不能访问任何数据库对象。然后在自己的 SCHEMA 中创建存储过程，并授予其所有者/模式对数据库对象（表、视图、任何其他存储过程等）的正常访问权限。最后，授予用户对该架构中存储过程的 EXECUTE 访问权限。这仍然无法阻止所有可能的动态 SQL 场景，但它确实消除了其中的危险（以及大多数情况下的要点）。
Not so far as I know, and I have looked pretty hard for a way to do it.
The closest thing that I can think of would be to somehow do something with the network so that only RPC connections to the SQL Server were allowed as the ".StoredProcedure" invocations normally come through RPC, but dynamic SQL requests cannot. However, I do not know enough about network management and server management to know if that is doable.
The other (and more standard) approach is to simply give users/apps only CONNECT access to your database, but not access to ANY of the database's objects. Then create stored procedures in their own SCHEMA and grant their owner/schema normal access to the database objects (tables, views, any other stored procedures, etc.). Finally then, grant the users EXECUTE access to the stored procedures in that Schema. This still would not prevent every possible dynamic SQL scenario, but it does take the danger out of them (and the point out of most of them).
SQL Server 数据库引擎很可能永远无法阻止这种情况发生。原因是，就 SQL Server 所知，这些命令何时进入 SQL Server，这些命令基本上是相同的。客户端计算机上的驱动程序负责格式化。
保护数据库引擎免受 SQL 注入的唯一方法是在应用程序层处理它。
Odds are the SQL Server database engine will never prevent that from happening. The reason for this is that as far as the SQL Server knows when those commands come into the SQL Server the commands are basically the same. The driver on the client machine is what does the formatting.
The only way to protect the database engine from SQL Injection is to handle it at the application layer.
编辑：我已纠正，我回去阅读了此处的 TDS 标准：
http://www.sybase.com/content/1040983/Sybase-tds38-102306.pdf
TDS_LANGUAGE 命令确实支持网络上的本机参数，因此这实际上就是 SQL 的方式服务器接收参数化查询。
至于是否有办法强制使用参数而不接受CommandText中的参数，我不知道有什么办法可以做到这一点。
Edited: I stand corrected, I went back and read the TDS standard here:
http://www.sybase.com/content/1040983/Sybase-tds38-102306.pdf
The TDS_LANGUAGE command does support native parameters over the wire, so that is in fact how SQL Server receives parameterized queries.
As to whether there's a way to force use of parameters and not accept arguments in the CommandText, I don't know of a way to do so.