Recent conversation at SQLServerCentral.com:

Garadin Said:

In ASP for example, you can do:

sql="SELECT * FROM TABLE WHERE Field ='" & MyVariable & "'"
rs.open sql conn2

Instead of

sql ="Exec dbo.MySP @Variable =" & MyVariable
rs.open sql conn2

That said, I see how the hard coded one has injection vulnerabilities, but how is the SP vulnerable if that variable is used within a structured select statement?

My Response:

Well, let's say that MyVariable was set to " '; DROP TABLE USERS --", then the sql string in your client becomes:

SELECT * FROM TABLE WHERE Field =' '; DROP TABLE USERS --

, which might be a problem .  Effecitvely, the injection happened in the client code, instead of in the SQL Server, but the result is the same.

The question that has to be asked in cases like these is "Where is MyVariable coming from?"  If it is from a user who shouldn't be allowed to drop the Users table then you (both client & server) should take steps to prevent this.  If on the other hand, it is coming from you or another DBA or Admin who could do this anyway, then you do not normally have to be concerned.  The threat with injection is that an unprivliged user can play "stupid string tricks" to hijack the SQL Server rights of the server process, either directly or indirectly (through the client).

That's why protecting against injection is really a two tier defense;

1) The client should never allow user-supplied text to become part of a SQL command that it executes, and...

2) The server should never allow client-supplied text to become part of a SQL command that it executes

#2 is most easily accomplished by forcing the client to use stored procedures and then in those stored procedures, either never use dynamic SQL or by being extremely careful in how you construct the dynamic sql, again, following the rule that client-supplied text (parameters) must never become part of the actual SQL command.  (additionally, you should use different users/schemas to firewall the access rights of each piece of the execution chain).

If you do this, then #1 can be accomplished on the client by never trying to "compose" SQL commands (effectively, dynamic SQL) but rather always calling stored procedures and passing the user-supplied text as parameters.

is pretty much exactly what the term "customizable report" means in any application feature list (not to be confused with "Ad-Hoc Reporting" which is a much more general thing).. 

To be clear: the SQL code above is a typical example of a bad use of dynamic SQL.  Not because Dynamic SQL is not needed here (it probably is to get decent performance) but because it is extremely unsafe, and there is virtually nothing that can be done within the stored procedure to remedy that.

Client-supplied text should never be allowed to become part of an executable SQL command.  That is how SQL Injection happens and that is what "Safe" dynamic SQL must never allow.

Something that I have been thinking about lately is how you can write stored procedures that use Dynamic SQL with user-supplied text and still be safe and secure.  The previous post on Dynamic SQL "Levels" covers much of my thinking on this, but I had some related ideas today that I wanted to get down also.

Use Case 1:  The archetypical use-case for dynamic SQL using user or client supplied text is the "Dynamic Report" scenario, wherein the use specifies a table (or view) to report on, the columns (and column order), selection criteria and sorting. 

Use Case 2:  Additional complications to Use Case #1, might include functions, operators or expressions based on columns.

Thoughts:  The columns in (1) should be manageable through Level 1 transformations.  Likewise the sorting.  The overall query structure would be set by the particular stored procedure called.

The match criteria are slightly more difficult, and are usually handled with a fixed AND clause type of syntax like:

"Operand1 Operator1 Operand2 [Operator2 Operand3]"

Where the Operands and Operators are passed by the client.  The operators can be validated with Level 1 techniques, and the operators could be validated from a specific Operator List table.  Call this the "Single Template" method.

Allowing more flexibilty is complicated, but could be handled by a "template and substiution" approach, where the client would pass a table of criteria like this:

 Create Table #MatchClauses(
    ClauseText NVarchar(255),
    Subs1,
    Subs2,
    .... (up to 5?)
)

"AND @1 @2 @3", "C:col1", "O:<", "L:'Jones'"

Here, the embedded parameters are inteneded to be replaced, in order, by the passed parameter values.  The passed parameters are encoded with their "Type" (C=column name, O=operator, L=literal value).  Then the following table colud be defined to validate it:

 Create Table MatchTemplates(
    TemplateID int,
    TemplateText NVarchar(255)
)

Create Table MatchTemplateSubs(
    TemplateID int,
    SubsNumber int,
    AllowColumns bit,
    AllowOperators bit,
    AllowLiterals bit,
    ...
)

So that a table of pre-defined allowable Match templates could be used to transform the user text into lower level dynamic SQL.

The real exciting promise of this approach is to make one of the substitution types be (X:= expression) which could then substitute other MatchTemplate expressions or special ExpressionTemplates.  This would allow the client to specify "nested" query expressions. 

The difficulty of this is that the client woudl have to take some kind of input form the user and then transform it into a strict post-op or pre-op form and then construct the complicated strings and calling sequence from that.  Fortunately, client code is good at custom parsing (unlike SQL).

I wish I did, but I have not seen a really good one myself.For the purpose of discussion I like to classify different SQL code into levels according to the degree to which they incorporate user or client-supplied text into SQL statements:

  Level 0: No user text is used to construct dynamic SQL commands.
  Level 1: User text is only used to specify the values of columns, variables and expressions (i.e.: "something = client-text").
  Level 2: User text is used to specify Column Names.
  Level 3: User text is used to specify Table & View Names.
  Level 4: User text is used as expressions of a specific and limited type to be directly included in the SQL Command.
  Level 5: User text used as SQL commands or is directly incorporated as SQL commands.

Level 0 may have a lot of dynamic SQL in it but is perfectly safe, because it never includes user text in a SQL command.

Level 1 is not safe when done directly, but can be made safe by transforming it into a parametized call to sp_executesql that is then Level 0 Dynamic SQL.  Level 1 is as high as I have seen any article go, and I have seen many articles incorrectly assert (or imply) that this technique is applicable to and safe for all Dynamic SQL (it isn't).

The trick to making Levels 2 and 3 safe is called "Keying" wherein, instead of using the user text directly in creating the SQL string, it is used as a "Key" into a table of acceptable/allowable strings.  For instance, if the user text is supposed to specify a column name from a specific table then you do something like this:

Select @SafeColumnName = COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'mySpecificTable'
  And COLUMN_NAME = @UsersColumnText

  From this point you can transform the Dynamic SQL to Level 1 or 0, because the dynamic SQL text no longer includes any direct user-supplied text.  I have seen some (very few) responses in SQL support forums that discuss Level 2 techniques.  I have never seen or heard anything higher on the Internet (though of course, the Internet is a very big place).

Level 4 can be validated only by parsing the user text to insure that it stays strictly within the bounds of what it is supposed to be expressing.  Level 4 is distinct from Level 5 because Level 4 is still reasonably possible (if nonetheless extravagant).

Level 5 can only be validated through a full SQL parser.  Obviously unreasonable for anyone other than the vendor to attempt.

And all of this should be firewalled in special-purpose roles/schemas/db user accounts whose rights and permissions have been designed for this designated purpose.

This list is not 100% inclusive as there are some unusual schemes that do not quite fit into one of these levels and in particular, there is arguably enough room between Level 3 and Level 4 that you could squeeze in one or two more meaningful levels.  Hmm, maybe I should write an article...