5

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+?

closed as unclear what you're asking by Erik Darling, mustaccio, hot2use, SqlWorldWide, Marco Sep 19 '17 at 6:27

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Assuming that this is a field a user can type anything into? – Sean Gallardy Sep 15 '17 at 18:51
  • Yes, inputSQL is entered by the user (attacker) so it could be anything. – James Sep 15 '17 at 19:22
1

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.

  • This is one of the other injection points I was referring to. This code wouldn't actually go through the function above anyway--why escape quotes in a string that can't have them? The real solution to this problem is actually in actually demonstrated in Writing Secure Code, even though it says it won't work--put your numeric inputs into a numeric type. The documentation says it's not secure because of the large number of characters that may pose problems, but I can't find ANY such characters that apply to Unicode strings using Microsoft SQL. I'll clarify my question a little. – James Sep 16 '17 at 13:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.