What is the Sql Server equivalent for Oracle's DBMS_ASSERT?_问答_开发者

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?

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

开发者_Python百科

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?

Don't do dynamic queries by building strings and the EXECuting them.

Use sp_executesql and pass parameters as parameters.

You'll find that sql injection is no more.

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.

EDIT2: sp_executesql statement explaination

The closest thing I have is TSQLAssert for TSQLMacro but it only supports TSQL Stored Procedures. It's free.

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.

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.

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)

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.

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.

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.

As such there is no equivalent of DBMS_ASSERT in SQL SERVER.

However enhancing the answer of Aaron Bertrand by this link SQL Injection

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.

Microsoft should really ship something similar, but till then, you're on your own.