SQL Injection via Unicode

Question

My team currently uses ASP.NET with MS SQL Server for our software. Security has only become important since I started, in which there is injection vulnerabilities everywhere.

Due to current integration with Oracle and MS SQL the business decision was never to use parameterized queries. This of course has been an issue.

Implementing Find and replace along with whitelisting of parameters has reduced this issue strongly.

My only issue is, I have read a lot about unicode and other encodings being the cause of sql injection. I dont quite understand this. Currently we sanitise everything like this:

        Const pattern As String = "^[a-zA-Z0-9.=,:\s\\\/\']*$"
        term = term.Replace("'", "''")

        If Not Tools.ValidString(term, pattern) Then
            term = String.Empty
        End If 

    Public Shared Function ValidString(ByVal source As String, ByVal pattern As String) As Boolean
        If source = String.Empty Then Return True

        Dim params As Text.RegularExpressions.RegexOptions = Text.RegularExpressions.RegexOptions.None
        Dim regex As New Text.RegularExpressions.Regex(pattern, params)
        Dim match As Text.RegularExpressions.Match = regex.Match(source, pattern, params)
        Return match.Success
    End Function

Does anyone have an example where unicode/encoded injection could be used, or just a plain example where this regular expression would fail to prevent sql injection.

Thanks

UPDATE

Can I please not have answers related to standard SQL Injection. I am strongly familiar with this already. ALSO please stop posting saying don't use string sanitisation. There is zero resources in the company to shift all queries to parameterised queries with ADO.NET while also building in logic for it to use ODP.NET if the client uses oracle. OWASP mention the use of whitelisting of characters if parameterising is out of question, so like in the regex, only few characters are allowed. I am not blacklisting characters, as this is stupid.

There is no compliance required for the data we hold. The security is for database integrity, as it would be a nightmare if content was changed.

Our software is a very large cloud application CMS and DMS in one, where 99% of the software is used internal, and only a minority is external and is only used for public review and commenting on the documents.

From my new understanding of Unicode injection. It can only occur if the data is being encoded before being placed into the query, and therefore unicode injection only really occurs in applications with globalisation of data. I am passing raw string fields straight into the string query after the sanitisation above.

Can I please only have an answer from an expert in injection, who can back up my claim that Unicode will not apply in my circumstance?

Answer 1

There are cases of SQL Injections leveraging the implicit conversion of Unicode homoglyphs from Unicode character string types (NCHAR, NVARCHAR) to character string types (CHAR, VARCHAR). A character such as ʼ (U+02BC) in NVARCHAR may slip through the escaping routine and get translated to ' (U+0027) in VARCHAR, which may result in an SQL Injection when such a string is used to build an SQL statement dynamically.

However, your validation is pretty strict (only characters from the Basic Latin Unicode block and Unicode whitespace characters) and I can’t think of any case where this would fail.

Answer 2

The one thing you need most to stop SQL injection is a semi-colon (;). However, it isn't always simple to just eliminate them. You will have situations where a semi-colon is used in a text field as a character, not as a SQL command terminator.

There are plenty of articles that go into detail about how to both inject and prevent SQL in your queries.

• Preventing SQL Injection Attack

• How To: Protect From SQL Injection in ASP.NET

• How To: Protect From Injection Attacks in ASP.NET

If you write text output to a Web page, encode it using HttpUtility.HtmlEncode. Do this if the text came from user input, a database, or a local file.

This brings to mind one of the methods used to protect from SQL injection: encode characters that will cause problems with ones that can't.

Specifically for Unicode versus any other encoding, you will still have to use the same techniques for finding, and either removing or replacing the offending characters in your strings.

UPDATE

If you encode Unicode strings to something like /u0000, then you can leave the string encoded and safely put it into your database without worrying about SQL injection. You will have to write the routine that performs the sanitization though, as it isn't built into SQL Server.

Answer 3

If you're looking at how unicode can be exploited, see this question, if you're looking for a solution, there are really only two that can be considered really secure: parameterized queries and encoding all of your input into hex or base64 or some other encoding that doesn't leave open the possibility changing the context of the value.

I would seriously reconsider the use of parameterized queries -- thay are in fact remarkably easy to use, while I don't know your code base, It's generally fairly easy to switch over. Particulaly since the introduction of extension methods.

Answer 4

Please, please, please, please do not use a handmade regex for preventing SQL injection. You should never be writing your own escaping, filtering, or sanitizing functions to prevent SQL injection, XSS, shell injection or the like. These are things you rely on built in and vetted libraries for.

Where you can avoid it, don't even use a standard library escaping function. Use parameterized queries.

For one, that escaping won't help if you're using an unquoted integer value at the end of a query. In pseudocode:

check ValidString(input)
query("SELECT * FROM table WHERE id=" + input)

The user can easily enter 1 OR 1=1 to see all rows in table.

...Or they can enter 1 UNION ALL SELECT password,1,1 FROM password_table or the like. And now the output contains all user passwords.

Are you saying it is impossible to go back and convert all SQL code to use paramaterized queries? If so, I suppose you better be 100% sure you always quote SQL values and never do any integer comparisons or insertions...

If I were you I would just convince your management and/or team that if security is desired in even the slightest, then time needs to be taken to actually rewrite the code in a secure manner.