1

I have been shown one regex which, per the one who wrote it, should protect against SQL injections on SQL server 2008. It doesn't really look like it, but I'm wondering exactly what code I can get past it to prove it doesn't protect.

value = Regex.Replace(userInput, "[\n\r\t\']", " ");
sqlCmd = "Select names from mytable where mycolumn like '%" + value + %'";
SQLDataAdapter results = new SQLDataAdapter(sqlCmd, connString);

Now, I've figured out that if I can get a single quote past, I can do something like

a' union all select passwords as names from myusertable;--

The problem is, the regex does catch the single quote, and I haven't a clue how to escape/encode/ect. it to get it past the regex. I've been looking for something, but everything seems to assume that you can get a single quote past. Obviously it can't be that simple to stop all sql injection attacks, but I personally don't know what to put to get past it. Any help?

Edit:

To be a bit more specific, I've heard that one can use unicode characters to slip a single quote past a check in many languages as there are unicode characters that are treated as such by the programming language, but when they get to the database, they are just treated as a quote. But I've found nothing but theory on this. When I try to explain it, it sounds like 'Well, people think this could happen, but it has never been shown to actually be a vulnerability' and that doesn't convince the senior individual who programmed this.

Lawtonfogle
  • 981
  • 7
  • 11
  • Looks like this is actually a dup here on SEC.se: http://security.stackexchange.com/questions/37749/no-single-quotes-is-allowed-is-this-sql-injection-point-still-exploitable – Eric G Aug 23 '13 at 22:56
  • I already tried the exploits listed there and nothing broke it. If I can't break it, then I'm not sure the one who implemented this is going to change his mind, no matter what else I show him saying it may be a bad idea. – Lawtonfogle Aug 23 '13 at 23:08
  • Have you tried any of the items cited below, the parenthesis example in the Exploiting hard filtered SQL Injections may be a good fit or the Homoglyphic Tranformation methods? – Eric G Aug 24 '13 at 01:43
  • This is either a dupe, or a too-specific question. You're asking us to help you break the security of a specific system. Both cases are worthy of a vote to close. – Adi Aug 24 '13 at 06:34
  • @Adnan I would say it isn't specific as the actual question is how to get a single quote in. One answer was given on how, if there are multiple inputs instead of one, it may be possible to escape out of it without needing a single quote, but the core question is that, given that single quotes are removed, how can one get a single quote past a check (any form of removing single quote would be valid, not just this one regex). – Lawtonfogle Aug 24 '13 at 12:42
  • @Lawtonfogle The unicode quote trick relies on homoglyphic transformation which is explained in the OWASP paper below, and requires certain system configurations to be set to exploit. – Eric G Aug 24 '13 at 14:44

2 Answers2

3

For complex regular expressions you should use a regex debugger. However this regex is dead simple. In short, you need to find a query that is susceptible to injection without using single quotes. lets assume the following query:

value = Regex.Replace(userInput, "[\n\r\t\']", " ");
sqlCmd = "Select names from mytable where mycolumn = '" + value1 + ' and yourcolumn= '"+value2+"';

now this can be exploited using the following PoC:

let value1 = \
let value2 = union select password from myusertable-- 1

This will produce the following query:

Select names from mytable where mycolumn = '\' and yourcolumn=' union select password from myusertable-- 1'

A couple of modifications, most connection managers do not allow query stacking, thus ; is just garbage, the -- comment is actually a three character comment that ends with a space so -- 1 will ensure the comment is respected even if white-space is truncated. By replacing single quotes, this sanitation method is better than addslashes, as it is not vulnerable to multi-byte attacks see: addslashes vs mysql_real_escape_string

Community
  • 1
rook
  • 47,238
  • 10
  • 96
  • 182
  • So what you are saying is that for the given example, it offers great security (no currently known exploits)? – Lawtonfogle Aug 23 '13 at 23:06
  • @Lawtonfogle you could specify arbitrary wildcards which is prevented by mysql_real_escape_string(), but yes this is immune to sql injection. – rook Aug 24 '13 at 05:30
1

It's possible in some contexts that there might be other string manipulation or things with html entity encoding or url encoding that will allow the single quote to come in later.

However, if the last thing you do is to remove the single quote, it will be removed. If you remove single quotes first then do some type of decoding, there is a chance it might get through. You may also consider encoding your data in the database so that matching is on encoded strings and not on the plain English.

In some cases, there are systems which do weird things with special characters or non-ASCII UTF characters that may cause issues with injection. Techniques in this area are often referred to as SQL Smuggling and Homoglyphic Tranformation, See this OWASP Paper.

There might be other use case scenarios. See this related question on SO: SQL Injection after removing all single-quotes and dash-characters.

Other References:

Community
  • 1
Eric G
  • 9,771
  • 5
  • 33
  • 60
  • This method of sanitation is immune to multi-byte attacks. There is no way to "hide" the single quote, or "consume" an escape character. Replacing single quotes is better than addslashes() for this reason. – rook Aug 24 '13 at 05:32