DBMS_ASSERT is one of the keys to prevent SQL injection attacks in Oracle. I tried a cursory search...is there any SQL Server 2005/2008 equivalent for this functionality?
DBMS_ASSERT是防止Oracle中的SQL注入攻击的关键之一。我粗略地搜索了一下……是否有任何SQL Server 2005/2008的版本对应这个功能?
I am looking for a specific implementation that has a counterpart of all the respective Oracle package members of DBMS_ASSERT.
我正在寻找一个特定的实现，该实现具有DBMS_ASSERT的所有Oracle包成员的对应关系。
- NOOP
- 无操作
- SIMPLE_SQL_NAME
- SIMPLE_SQL_NAME
- QUALIFIED_SQL_NAME
- QUALIFIED_SQL_NAME
- SCHEMA_NAME
- SCHEMA_NAME
I know the best-practices of preventing injection...bind variables...being one of them.
But,in this question I am specifically looking for a good way to sanitize input...in scenarios where bind-variables were not used.
我知道预防注射的最佳方法……绑定变量……就是其中之一。但是，在这个问题中，我特别想寻找一种好的方法来净化输入……在不使用绑定变量的场景中。
Do you have any specific implemetations?
Is there a library that actually is a SQL Server Port of the Oracle package?
你有什么具体的实现吗?是否有一个库实际上是Oracle包的SQL服务器端口?
6 个解决方案
#1
1
The only likely option you have is QUOTENAME which is used to escape object names (and thus may be an equivalent for SIMPLE_SQL_NAME or ENQUOTE_NAME and possibly others. So table names (providing they are not qualified with owner or database) and column names can be escaped.
惟一可能的选项是QUOTENAME，它用于转义对象名(因此可能与SIMPLE_SQL_NAME或ENQUOTE_NAME以及其他名称等价。因此可以转义表名(如果表名不符合所有者或数据库的要求)和列名。
There isn't a mechanism for fully qualifying an object (e.g., turning table 'bob' into 'database.owner.bob'), so you'd have to put this together manually, optionally using QUOTENAME to escape the values, e.g.:
没有一种机制可以完全限定一个对象(例如，将表“bob”转换为“database.owner.bob”)，因此您必须手工地将其组合起来，可选择使用QUOTENAME来避免值，例如:
QUOTENAME(@database) + '.' + QUOTENAME(@owner) + '.' + QUOTENAME(@tableName)
QUOTENAME(@database)+”。' + QUOTENAME(@owner) + '。' + QUOTENAME(@tableName)
If the object is in the existing database, then you could use DB_NAME(), and assume that the owner's going to be passed in as a variable:
如果对象在现有数据库中，那么可以使用DB_NAME()，并假设所有者将作为变量传入:
DB_NAME() + '.' + QUOTENAME(@owner) + '.' + QUOTENAME(@tablename)
DB_NAME()+ '。' + QUOTENAME(@owner) + '。' + QUOTENAME(@tablename)
In a really convoluted way, you can get owner out as well:
在一种非常复杂的方式中，你也可以让所有者出局:
USER_NAME(OBJECTPROPERTY(OBJECT_ID(@tablename), 'ownerid')))
USER_NAME(OBJECTPROPERTY(OBJECT_ID(@tablename)ownerid)))
Yes I realise all of these may be considered workarounds, but they are options.
是的，我知道所有这些可能都被认为是解决办法，但它们都是选择。
However for escaping values you really are on your own: there is no built-in SQL Server equivalent, so would be all manual string manipulation. You might be able to create a UDF to sit in place to do this, although if you're going to that effort, it's probably also worth looking at rewriting the sproc using SQL Server sp_ExecuteSQL semantics.
但是，对于转义值，您实际上是自己的:没有内置的SQL Server对等物，所以所有的手动字符串操作都是这样的。您可能可以创建一个UDF来实现这一点，但是如果您要做这方面的工作，那么也应该考虑使用SQL Server sp_ExecuteSQL语义重写sproc。
#2
2
Don't do dynamic queries by building strings and the EXECuting them.
不要通过构建字符串并执行它们来执行动态查询。
Use sp_executesql and pass parameters as parameters.
使用sp_executesql并将参数作为参数传递。
You'll find that sql injection is no more.
您将发现，sql注入已不复存在。
EDIT: sorry, I was in a hurry and wrote the wrong command. it's not sp_execute, it's sp_executesql; it takes a string and a set of parameters: all the encoding and escaping of the parameters is done by SQL Server.
编辑:对不起，我赶时间，写错了命令。它不是sp_execute，而是sp_executesql;它接受一个字符串和一组参数:所有参数的编码和转义都由SQL Server完成。
EDIT2: sp_executesql statement explaination
EDIT2:sp_executesql声明解释
#3
2
The closest thing I have is TSQLAssert for TSQLMacro but it only supports TSQL Stored Procedures. It's free.
最接近的是TSQLMacro的TSQLAssert，但它只支持TSQL存储过程。这是免费的。
TSQLAssert is an assertion framework built on top of TSQLMacro. It is intended to provide debug-time assertion failures similar to assertions in languages like C++ -- with an additional logging component not found in those languages. TSQLAssert can be used only within stored procedures and triggers -- unfortunately, user-defined functions and views do not support many of the keywords that allow it to work.
TSQLAssert是一个构建在TSQLMacro之上的断言框架。它的目的是提供与c++等语言中的断言类似的调试时间断言失败——并提供那些语言中没有的附加日志组件。TSQLAssert只能在存储过程和触发器中使用——不幸的是，用户定义的函数和视图不支持允许它工作的许多关键字。
#4
0
There is no magic "prevent injection" command. The methodology is a combination of:
没有魔法“防止注射”命令。方法是:
- Using parameterized queries to ensure type safety
- 使用参数化查询确保类型安全
- Sanitize inputs before passing them to the database layer
- 在将输入传递到数据库层之前对其进行清理
- When you can't do 1. and 2., replace ' with '' in all input, and other sanitize methods before blindly executing dynamic SQL.
- 当你做不到1。和2。在盲目执行动态SQL之前，在所有输入和其他方法中替换' with '。
#5
0
I also was once looking for something similar to DBMS_ASSERT for Sql Server, but to no avail. So I ended up writing a collection of PROCs that we needed.
我也曾为Sql Server寻找类似DBMS_ASSERT的东西，但没有任何用处。所以我最后写了一组我们需要的PROCs。
Microsoft should really ship something similar, but till then, you're on your own.
微软真的应该推出类似的产品，但在那之前，你只能靠自己。
#6
0
As such there is no equivalent of DBMS_ASSERT in SQL SERVER.
因此，在SQL SERVER中没有对应的DBMS_ASSERT。
However enhancing the answer of Aaron Bertrand by this link SQL Injection
但是通过这个链接SQL注入增强Aaron Bertrand的答案
#1
1
The only likely option you have is QUOTENAME which is used to escape object names (and thus may be an equivalent for SIMPLE_SQL_NAME or ENQUOTE_NAME and possibly others. So table names (providing they are not qualified with owner or database) and column names can be escaped.
惟一可能的选项是QUOTENAME，它用于转义对象名(因此可能与SIMPLE_SQL_NAME或ENQUOTE_NAME以及其他名称等价。因此可以转义表名(如果表名不符合所有者或数据库的要求)和列名。
There isn't a mechanism for fully qualifying an object (e.g., turning table 'bob' into 'database.owner.bob'), so you'd have to put this together manually, optionally using QUOTENAME to escape the values, e.g.:
没有一种机制可以完全限定一个对象(例如，将表“bob”转换为“database.owner.bob”)，因此您必须手工地将其组合起来，可选择使用QUOTENAME来避免值，例如:
QUOTENAME(@database) + '.' + QUOTENAME(@owner) + '.' + QUOTENAME(@tableName)
QUOTENAME(@database)+”。' + QUOTENAME(@owner) + '。' + QUOTENAME(@tableName)
If the object is in the existing database, then you could use DB_NAME(), and assume that the owner's going to be passed in as a variable:
如果对象在现有数据库中，那么可以使用DB_NAME()，并假设所有者将作为变量传入:
DB_NAME() + '.' + QUOTENAME(@owner) + '.' + QUOTENAME(@tablename)
DB_NAME()+ '。' + QUOTENAME(@owner) + '。' + QUOTENAME(@tablename)
In a really convoluted way, you can get owner out as well:
在一种非常复杂的方式中，你也可以让所有者出局:
USER_NAME(OBJECTPROPERTY(OBJECT_ID(@tablename), 'ownerid')))
USER_NAME(OBJECTPROPERTY(OBJECT_ID(@tablename)ownerid)))
Yes I realise all of these may be considered workarounds, but they are options.
是的，我知道所有这些可能都被认为是解决办法，但它们都是选择。
However for escaping values you really are on your own: there is no built-in SQL Server equivalent, so would be all manual string manipulation. You might be able to create a UDF to sit in place to do this, although if you're going to that effort, it's probably also worth looking at rewriting the sproc using SQL Server sp_ExecuteSQL semantics.
但是，对于转义值，您实际上是自己的:没有内置的SQL Server对等物，所以所有的手动字符串操作都是这样的。您可能可以创建一个UDF来实现这一点，但是如果您要做这方面的工作，那么也应该考虑使用SQL Server sp_ExecuteSQL语义重写sproc。
#2
2
Don't do dynamic queries by building strings and the EXECuting them.
不要通过构建字符串并执行它们来执行动态查询。
Use sp_executesql and pass parameters as parameters.
使用sp_executesql并将参数作为参数传递。
You'll find that sql injection is no more.
您将发现，sql注入已不复存在。
EDIT: sorry, I was in a hurry and wrote the wrong command. it's not sp_execute, it's sp_executesql; it takes a string and a set of parameters: all the encoding and escaping of the parameters is done by SQL Server.
编辑:对不起，我赶时间，写错了命令。它不是sp_execute，而是sp_executesql;它接受一个字符串和一组参数:所有参数的编码和转义都由SQL Server完成。
EDIT2: sp_executesql statement explaination
EDIT2:sp_executesql声明解释
#3
2
The closest thing I have is TSQLAssert for TSQLMacro but it only supports TSQL Stored Procedures. It's free.
最接近的是TSQLMacro的TSQLAssert，但它只支持TSQL存储过程。这是免费的。
TSQLAssert is an assertion framework built on top of TSQLMacro. It is intended to provide debug-time assertion failures similar to assertions in languages like C++ -- with an additional logging component not found in those languages. TSQLAssert can be used only within stored procedures and triggers -- unfortunately, user-defined functions and views do not support many of the keywords that allow it to work.
TSQLAssert是一个构建在TSQLMacro之上的断言框架。它的目的是提供与c++等语言中的断言类似的调试时间断言失败——并提供那些语言中没有的附加日志组件。TSQLAssert只能在存储过程和触发器中使用——不幸的是，用户定义的函数和视图不支持允许它工作的许多关键字。
#4
0
There is no magic "prevent injection" command. The methodology is a combination of:
没有魔法“防止注射”命令。方法是:
- Using parameterized queries to ensure type safety
- 使用参数化查询确保类型安全
- Sanitize inputs before passing them to the database layer
- 在将输入传递到数据库层之前对其进行清理
- When you can't do 1. and 2., replace ' with '' in all input, and other sanitize methods before blindly executing dynamic SQL.
- 当你做不到1。和2。在盲目执行动态SQL之前，在所有输入和其他方法中替换' with '。
#5
0
I also was once looking for something similar to DBMS_ASSERT for Sql Server, but to no avail. So I ended up writing a collection of PROCs that we needed.
我也曾为Sql Server寻找类似DBMS_ASSERT的东西，但没有任何用处。所以我最后写了一组我们需要的PROCs。
Microsoft should really ship something similar, but till then, you're on your own.
微软真的应该推出类似的产品，但在那之前，你只能靠自己。
#6
0
As such there is no equivalent of DBMS_ASSERT in SQL SERVER.
因此，在SQL SERVER中没有对应的DBMS_ASSERT。
However enhancing the answer of Aaron Bertrand by this link SQL Injection
但是通过这个链接SQL注入增强Aaron Bertrand的答案