Unicode Smuggling

We’ve all heard that when trying to prevent SQL injection it’s not enough to escape your single quotes as in the example below.  It’s necessary to parameterize your queries.

		string sqlTemplate="SELECT * FROM TABLE WHERE ID='{0}'";
		string ID="1";
		string sql=String.Format(sqlTemplate, ID.Replace("'","''"));

 

The reason for this is something called Unicode Smuggling or SQL Smuggling.  The gist of it is that there are other characters that can be used in place of the single quote, such as U+02BC, that will not be replaced in the code but be converted into a single quote when they are passed to the database via a translation slip up in the character encoding schemes.  I had previously believed was an obscure vulnerability that would actually not occur in my situation.  (You know the ‘it can never happen to me’ syndrome.)

Well, I underestimated the seriousness of this possibility and it turned out that some of my code was vulnerable.  Rather than doing the honorable thing and fixing my code to support parameterized queries, I’m going to take another stab at a work around due to the size of the code base.

My example is below.  The last line is kind of the nuclear option, to remove all high level characters from the string after I’ve replaced all others that I can think of with reasonable substitutes.  Obviously, this isn’t going to work well in a monolingual environment due to fancy Word characters (i.e. smart quotes) and it will be a complete disaster in a multilingual environment.

 s = Convert.ToString(s).Replace("'", "''");
 s = Convert.ToString(s).Replace("\u02BC", "''");//https://en.wikipedia.org/wiki/Quotation_mark
 s = Convert.ToString(s).Replace("\uFF07", "''");
 s = Convert.ToString(s).Replace("\u2018", "''");
 s = Convert.ToString(s).Replace("\u2019", "''");

 s = Convert.ToString(s).Replace("\u201C", "\"");
 s = Convert.ToString(s).Replace("\u201D", "\"");
 s = Convert.ToString(s).Replace("\u201E", "\"");
 s = Convert.ToString(s).Replace("\u201F", "\"");
 s = Convert.ToString(s).Replace("\u301D", "\"");
 s = Convert.ToString(s).Replace("\u301E", "\"");
 s = Convert.ToString(s).Replace("\u301F", "\"");
 s = Convert.ToString(s).Replace("\uFF02", "\"");

 s = Convert.ToString(s).Replace("\u2014", "-");

 s = System.Text.Encoding.ASCII.GetString(System.Text.Encoding.ASCII.GetBytes(Convert.ToString(s)));