Question

我从Ive制造的磨难中抽空了SQ的储存程序,所有投入参数都是安全参数,但并没有像我这样迅速运行,因此,我想要使其具有活力,从而提高效率。

我知道,我可以保留对我所储存程序的投入参数,然后在其中形成一份动态的“结构”说明,然后我可以通过储存程序的投入参数,但我在这样做时是否需要意识到任何安全影响? I m 猜测不像是另外一套参数,因此,这些参数应当与现行储存程序的参数相同。

Obviously, producing code like this "WHERE OrderNo = + @orderno is asking for trouble - I will be doing WHERE OrderNo = @orderno in the dynamic SQL, but is there anything else I need to be aware of? Thx MH

PS——在任何人建议之前,我能够使用LINQ或类似方法,在客户方面动态地建立LQ——所有(出于各种原因)都必须在数据库一级加以控制和控制。

Answer 1

一种形式的卡片注射是,许多人在进行堆积程序时不思考:SQL Truncation attacks。

袭击者发动拖拉式袭击后,投放了长长的文字和平,使使用的文本变本,使库克失去部分麻烦。

http://msdn.microsoft.com/en-us/magazine/cc163523.aspx” rel=“nofollow”> 第条对此提供了更多信息。

Answer 2

Where your parameters are always Data Items, both when being passed to the StoredProc and when used in yor DynamicSQL, everything will stay safe.

Should any of your StoredProc s parameters end up being table or field names, and so forming part of the structure of the DynamicSQL itself, you introduce a new risk : That the parameter can be used to inject rogue SQL Code.

To prevent against such an injection attack you should always validate any such parameters.

如何做到这一点的一个例子是,将投入参数作为标语,而不是直接代之于动态SQL...。

SET @SQL = @SLQ + CASE targetTable WHEN  1   THEN  table1 
                                   WHEN  tx  THEN  tableX 
                  END

Some people suggest you only need to validate on the client application. But that means that if someone becomes able to execute you SP s directly, the SP has become a point of attack. I always prefer to validate both on the client AND in the server.

www.un.org/Depts/DGACM/index_spanish.htm

http://www.ohchr.org。业绩

Note that using DynamicSQL isn t always a guarnatee of performance increases. If you use parameterised queries, the execution plans can indeed be stored. But if the queries do vary greatly, you may still find a significant overhead in compiling the SQL.

还有事实是,依赖性跟踪损失了。不可能看看SP所依赖的是什么表格,因为该代码被掩盖为示意图。

我很少发现需要有动态的SQL。往往随着若干选择的问询,可以改革复杂的问题。或者可以调整数据,以满足新的需求。或者甚至通过数据重新思考数据和算法。甚至可以suggest,取决于动态SQL是另一个根本问题的一个指标。

Perhaps it s not in the scope of your question, but it would be interesting to see the actual puzzle you re facing; to see if anyone has any alternative approaches for you.

友情链接