Bad Code: Why is this dangerous? [duplicate]

Question

Possible Duplicate:
Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?

     String badInput = rawInput.replace("'","''");
     ResultSet rs = statement.executeQuery("SELECT * FROM records WHERE col1 = '"+badInput+"'";

Is there any way to do a "Bobby Tables"-like attack on this code?

Answer 1

Depending on the different steps along the way that all have to interpret the command, there may be some possibility to pass %27 (for instance) and have it act as a single quote, passing unnoticed through your replace.

But even if all such cases could be covered, and it was actually safe for this single question, it is lacking in that it cannot be uniformely implemented. Somebody else may come along and want to add AND int1 = var1, and notices that you have thought about SQL injection, so they just modify the code in the exact manner that you have

String badInput = rawInput.replace("'","''");
String badInteger = rawInteger.replace("'","''");
ResultSet rs = statement.executeQuery("SELECT * FROM records WHERE" +
 "int1 = " + badInteger + " OR col1 = '"+badInput+"'");

...only with integers it is no longer quotes you want to protect yourself from! Here, it is plain to see that anything could go wrong. So while that's a problem that requires somebody to implement it poorly, I think it's the biggest problem of the design - it only covers a narrow set of cases.

It will always be better to be able to just say "the following is a variable. whatever it contains, treat it as a value, and do not try to use parts of it as code and execute that code."