Eliminating the problem [LWN.net]

Eliminating the problem

Posted Jun 1, 2006 14:50 UTC (Thu) by mrshiny (subscriber, #4266)
In reply to: Eliminating the problem by iabervon
Parent article: SQL injection vulnerabilities in PostgreSQL

I'd still feel better using a prepared statement, even if there are optional clauses in the statement. In such cases I normally do something like this:

List args = new ArrayList();
String sql = "select * from daily_revenue where transaction type = ? ";
args.add(transType);
if (sinceLastLogin) {
  sql += " and trans_date >= ? "; // for lots of optional clauses, use StringBuffer
  args.add(lastLoginDate);
}

Then later on you just bind all the variables in the order they appeared in the list. Also you can use a var-args function (in Java 1.5 and other languages that support var-args) to automate things like date converstion; if the type of one of the objects in the args list is Date or Calendar or some other non-SQL-friendly type, you can convert it automatically.

(Log in to post comments)

Eliminating the problem

Posted Jun 1, 2006 15:12 UTC (Thu) by iabervon (subscriber, #722) [Link]

My version is using a prepared statement. My SQLBuffer contains a StringBuffer and a List, and SQLBuffer.add() appends a "?" to the buffer, and adds the argument to its list, which it goes through in fill() using the loop that you omitted from the end of your example. My version is really identical to yours, except that my SQLBuffer methods abstract the pattern that you're open-coding (and, therefore, it's harder to screw up).