I was doing a code review and I came across a query that looked something like this:
String sql = "SELECT * FROM users WHERE username LIKE '%' || :userName || '%'";
Seeing the string concatenation made me stop and think about this for a bit. At first glance this seems a little risky though I haven't found much discussion other than making sure the bind variable is properly escaped.
The comments on this question at stackoverflow talk about being able to get more information than intended or cause a DoS if it's not escaped but not necessarily doing any other damage.
In the case above, when do the wildcards and bind variable get concatenated and evaluated? Since the wildcards are part of the string concatenation would the bind variable need to be evaluated before or after the execution plan is created?
Alternatively, the wildcards are added as part of the bind variable:
String sql = "SELECT * FROM users WHERE username LIKE :userName"; params.addValue("userName", "%" + userName + "%");
I believe in this case the execution plan is created before the bind variable is applied leaving the variable not able to change what is executed (though it should still be escaped). However, the first example above doesn't seem quite so clear. Other than making sure the variable is properly escaped is there anything else to be concerned about?
Other links I looked at this morning: