How safe is T-SQL after replacing an escape character?

Question

I fully understand that the best practice for disinfecting SQL queries is to parameterize them.

I am working on a lot of pre-existing code in which the sanitation measure was to replace all the strings with ' on '' in dynamic strings. I'm trying to figure out how interested I should be.

Here's what: this code runs exclusively on T-SQL (SQL Server 2008R2 and later), and as far as I can tell, ' is the only escape character for T-SQL.

So, how would you carry out an injection attack to overcome the above measure? That is, that very "naive" sanitation is actually quite solid in T-SQL, what does it look like?

Answer 1

Yes, a single quote is the only escape character, so you are basically, but not completely alright.

Using parameters, although best of all, basically does the ' by '' replacement, which you do manually. BUT, they also provide the maximum string length. Of course, if we were talking about non-string parameters, they would have the advantage of providing a data type (i.e. ' no need to escape for numeric, date / time, etc., since this is not valid for them to begin with).

The problem that you can still leave is a subset of SQL Injection called SQL Truncation. The idea is to make part of dynamic sql leave the end of the line. I'm not sure how practical this is, but depending on how and where you are building dynamic sql, you need to make sure that the variable containing the dynamic SQL to execute is large enough to hold the static fragments in your code plus everything variables suggesting that they are represented at maximum length.

Here is an article from the MSDN journal, New SQL Truncation Attacks and How to Avoid Them , which shows both regular SQL injection and SQL Truncation. In this article, you will see that to avoid SQL Injection, they basically just use the REPLACE(@variable, '''', '''''') method REPLACE(@variable, '''', '''''') , but also show the use of QUOTENAME(@variable, '[') situations.

EDIT (2015-01-20): Here is a good resource, albeit not specific to SQL Server, that describes the various types of SQL Injection: https://www.owasp.org/index.php/Testing_for_SQL_Injection_(OTG-INPVAL-005)

The following article is related to the above. This approach is specific to SQL Server, but more general in terms of overall security. There are sections related to SQL Injection:
https://www.owasp.org/index.php/Testing_for_SQL_Server

Answer 2

(Insert comments on the dangers of ruined sanitation and escapes here. See marc_s comment.)

Here you propose the same method that uses Microsoft SQL Server Managed Objects (SMOs). These are .NET DLLs that can be checked using a decompiler. For instance:

 internal static string MakeSqlString(string value) { StringBuilder builder = new StringBuilder(); builder.Append("N'"); builder.Append(EscapeString(value, '\'')); builder.Append("'"); return builder.ToString(); } public static string EscapeString(string value, char escapeCharacter) { StringBuilder builder = new StringBuilder(); foreach (char ch in value) { builder.Append(ch); if (escapeCharacter == ch) { builder.Append(ch); } } return builder.ToString(); } 

So yes, just doing Replace("'", "''") enough according to Microsoft. I am sure that this is not only a code, but also an audit to ensure security. They always do this because of the SDL.

Also note that this code seems to be designed to work with Unicode (see prefix N ). Apparently, this is also Unicode-safe.

In a more subjective note: I avoid T-SQL string literals as if it were necessary. I trust this method.

Answer 3

This is in .NET
Now I notice that the TSQL tag is specified in the question, but not .NET.

Mark implied that you could trick him with unicode or hexadecimal representation. I tested, and neither Unicode nor six tricked him into .NET.

As for cheating with the correct apostrophe number. If you always replace one with two, I don’t see how the right number can trick him.

u0027 and x0027 are an apostrophe, and it was replaced by two apostrophes 2018 and 2019 - these are left and right quotes, and SQL just processed them as literals

  string badString = "sql inject \' bad stuff here hex \x0027 other bad stuff unicode \u0027 other bad stuff left \u2018 other bad stuff right \u2019 other bad stuff"; System.Diagnostics.Debug.WriteLine(badString); System.Diagnostics.Debug.WriteLine(SQLclean(badString)); } public string SQLclean(string s) { string cleanString = s.Replace("\'", "\'\'"); return "N\'" + cleanString + "\'"; } 

I believe options are best practice