11

The SQL phrase looks like this,

"SELECT * from XX where id = '" + id + "'"

The id variable comes directly from the GET parameter named id.

And the Java web app explicitly disallowed single quote. If a single quote was found in that parameter, the server stop processing it immediately and returns an error.

So, is this still exploitable? With postgresql and tomcat environment.

galoget
  • 1,514
  • 1
  • 11
  • 15
daisy
  • 2,067
  • 7
  • 31
  • 44
  • 2
    Well - why not do it properly instead of wondering whether or not it's exploitable? – 1615903 Jun 20 '13 at 05:06
  • Also - yes it is: https://websec.wordpress.com/2010/03/19/exploiting-hard-filtered-sql-injections/ – 1615903 Jun 20 '13 at 05:14
  • @user1615903 it's a different situation where $id is not surrounded by single quote, am I wrong about it? – daisy Jun 20 '13 at 05:45
  • What language? Is it a strongly typed language where id is guaranteed to be a valid integer, or a dynamic language where id could be a String or anything? – GlenPeterson Jun 20 '13 at 12:35

3 Answers3

16

You may want to take a look at this paper (wayback machine link). It details some methods of bypassing such filters.

For example, some SQL filters replace all single quotes with a pair of single quotes. However, feeding them an input containing \' can bypass this.

Also, there is Unicode smuggling, where you use a Unicode character that Java (PHP/Ruby/Python/whatever) understands as being different from a quote, but the database doesn't.

Unless you have a really, really good reason, I recommend you use the built-in escaping.

Manishearth
  • 8,317
  • 5
  • 37
  • 56
  • 7
    Or parameterised queries / prepared statements. – Polynomial Jun 20 '13 at 15:22
  • Just wanting to add that these techniques don't work in Postgres. I don't argue about the necessity of using built-in escaping and parameterized queries though. – chefarov Apr 11 '20 at 16:11
11

You are talking about "prevent known bad" which only works until someone thinks of a new bad thing. As @Manishearth says, \' is one technique. For a URL, %27 is another. In HTML, you can have ', ', ', ', ' are others. What does your server do if these techniques are combined as in, %40%230039%3B (' url encoded) or %5C%27 (\' url encoded) or \\\\'?

Prevent Known Bad is always a risky proposition. Allow Known Good is always better when it is an option. Thus, converting to an integer type will always provide more safety than trying to prevent specific strings. Prepared Statements provide an excellent layer of protection.

Check out the Wikipedia article about SQL Injection Mitigation.

GlenPeterson
  • 349
  • 1
  • 5
2

Assuming that GET parameter 'id' in digits-only, the best thing to do is to check if ID really contains digits only, by for example converting it into an INT (and catch the exception if any), and not some nasty things like quotes/slashes/encoded chars/etc.

In general I prefer having a real integer variable as I perfectly know it contains only digits, rather than an escaped string which might contains some strange characters..

Jeremy
  • 91
  • 8