与 Oracle 的 DBMS_ASSERT 等效的 Sql Server 是什么? [英] What is the Sql Server equivalent for Oracle's DBMS_ASSERT?
问题描述
DBMS_ASSERT 是 Oracle 中防止 SQL 注入攻击的关键之一.我粗略地搜索了一下……是否有与此功能等效的 SQL Server 2005/2008?
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 包成员的对应物.
I am looking for a specific implementation that has a counterpart of all the respective Oracle package members of DBMS_ASSERT.
- NOOP
- SIMPLE_SQL_NAME
- QUALIFIED_SQL_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.
你们有什么具体的实现吗?
是否有一个库实际上是 Oracle 包的 SQL Server 端口?
Do you have any specific implemetations?
Is there a library that actually is a SQL Server Port of the Oracle package?
推荐答案
您唯一可能的选择是 QUOTENAME 用于转义对象名称(因此可能等效于 SIMPLE_SQL_NAME 或 ENQUOTE_NAME 以及可能的其他名称.因此可以对表名(前提是它们没有所有者或数据库限定)和列名进行转义.
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.
没有完全限定对象的机制(例如，将表 'bob' 转换为 'database.owner.bob')，因此您必须手动将其组合在一起，可选择使用 QUOTENAME 转义值，例如:
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.:
QUOTENAME(@database) + '.'+ QUOTENAME(@owner) + '.'+ QUOTENAME(@tableName)
如果对象在现有数据库中，那么您可以使用 DB_NAME()，并假设所有者将作为变量传入:
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() + '.'+ QUOTENAME(@owner) + '.'+ QUOTENAME(@tablename)
以一种非常复杂的方式，您也可以让所有者退出:
In a really convoluted way, you can get owner out as well:
USER_NAME(OBJECTPROPERTY(OBJECT_ID(@tablename), 'ownerid')))
是的，我意识到所有这些都可能被视为解决方法，但它们是选项.
Yes I realise all of these may be considered workarounds, but they are options.
然而，对于转义值，您实际上是靠自己的:没有内置的 SQL Server 等效项，因此所有手动字符串操作都是如此.您也许可以创建一个 UDF 来执行此操作，但如果您打算这样做，可能还值得考虑使用 SQL Server sp_ExecuteSQL 语义重写 sproc.
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.
这篇关于与 Oracle 的 DBMS_ASSERT 等效的 Sql Server 是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
