Microsoft SQL Server SQL Injection Through .NET Even With Quote Escaping I've been reading up on SQL Injection as part of a security audit for a fairly large web service.  I've been googling and read all the posts I could find here and on SO and have a fairly solid understanding of how dynamic queries really should be constructed and some of the differences between MySQL and MSSQL.  I eventually came across this page published earlier this year by Microsoft titled SQL Injection.  In the section titled 'Filtering Input', it states:


  Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems, this is not a reliable defense. The following example searches for the character string delimiter.  


private string SafeSqlLiteral(string inputSQL)  
{  
  return inputSQL.Replace("'", "''");  
}  


Writing Secure Code Second Edition also discusses this on page 401-402, but it looks like the author didn't actually try the C# examples (or maybe the SQL version he used back in 2003 doesn't handle the escaped strings correctly).  It states that the string input will lead to an invalid query (in reality the escaping just queries for a value with a single quote in it), and then states that it could also be attacked with using the value for an unquoted numeric field (though it can't because the field in the example comes through a local variable of type int, which can't possibly contain the information needed for an injection).

I don't doubt that there is a possible attack vector here, but I haven't been able to find any explanations of how this might be accomplished.  From my other research, it looks like one possible way might involve strange byte sequences in non-Unicode strings.  In my case, I'm working with .NET, so there is no possibility of such sequences, and I would think that either ADO.NET, SQL Server, or both would properly handle Unicode strings with any other types of characters inside the single-quotes.  (Though interestingly the sample code appears to be in C#).

I'm also well aware of the other methods of SQL injection listed on Microsoft's page, and for the purposes of this question, I'm not worried about those.  Also, I'm only concerned about properly typed string literal inputs here.  This code always parses the incoming types into their appropriate runtime type before composing the SQL queries, so the quote escaping only applies when the data is literal string data.  So in my case, the function actually looks more like:

private string SafeSqlStringLiteral(string inputString)  
{  
  return "'" + inputString.Replace("'", "''") + "'";  
}  


This thing that concerns me most is the following phrase in the documentation:


  However, because of the large number of characters that may pose problems, this is not a reliable defense.


Is this just referring to strange single-byte encodings, or is there a Unicode character that this applies to (I haven't been able to find one programmatically)?

The code I'm reviewing is extensive, and uses this type of escaping in many places (and in a way that's not trivial to fix). I'm not asking for an exploit directly (that's frowned upon IIRC), but I'd like to get some more-expert-than-me clarification on this so that I might be able to convince myself that this isn't an issue, or convince the team leader that it is an issue so that we can budget the significant amount of time necessary to fix these issues.

So, my question is, assuming we're not playing any games with PInvoke or unsafe code to create single-byte strings and call SQL Server with those, is there any way using .NET to inject SQL through this type of string literal escaping (as in SafeSqlStringLiteral above) when the string literals are only used in comparisons to string columns and don't involve LIKE clauses, making queries against Microsoft SQL Server 2012+?
Single-quote escaping can be circumvented if there is a numeric field input that is not checked for only numeric data (and, as always, if the field isn't length limited.) Here's the basic method:

Enter a valid number, followed by a semicolon to terminate the existing command. Then enter something like below, after the semicolon - this will execute the command SELECT * FROM users;

DECLARE @o varchar(max);SELECT @o= CHAR(83)+CHAR(69)+CHAR(76)+CHAR(69)+CHAR(67)+CHAR(84)+CHAR(32)+CHAR(42)+ CHAR(32)+CHAR(70)+CHAR(82)+CHAR(79)+CHAR(77)+CHAR(32)+CHAR(117)+CHAR(115)+ CHAR(101)+CHAR(114)+CHAR(115)+CHAR(59);EXEC(@o);

Nowadays, having code that would allow this through your input field would be pretty unlikely - a simple IsNumeric() check would blow this right out of the water, and a rational fieldsize limit on the numeric input leaves you trying to execute DECLAR or some such, which will of course blow up. But this is theoretically possible.