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.
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.
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.
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..
