What is the Sql Server equivalent for Oracle's DBMS_ASSERT?

Question

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

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?

Answer 1

There is no magic "prevent injection" command. The methodology is a combination of:

1. Using parameterized queries to ensure type safety
2. Sanitize inputs before passing them to the database layer
3. When you can't do 1. and 2., replace ' with '' in all input, and other sanitize methods before blindly executing dynamic SQL.

Answer 2

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.

Answer 3

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

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

Answer 4

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

Answer 5

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.

Answer 6

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.