2

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:

Dave Rager
  • 123
  • 6
  • Either coding style prevents SQL injection just fine. As you mention, like injection could potentially cause a DoS, but it's probably not a big concern in practice. – paj28 Aug 23 '17 at 16:07
  • What happens if the user name has a '%' character in it? What if someone registers the name '%admin'? – Macil Aug 23 '17 at 22:40

1 Answers1

1

I haven't found much discussion other than making sure the bind variable is properly escaped

Given the syntax you have presented here, it appears that ":userName" is a bind variable hence you should not be trying to escape it. If this is the case, then client binding will handle that. Nothing unusual here.

I believe in this case the execution plan is created before the bind variable is applied

The execution plan will be created for both the queries you have shown us at the same point. That will be before the bind variable is applied for statements prepared serverside and after for statements prepared clientside - different DBMS/different bindings work differently.

Other than making sure the variable is properly escaped

If you have established that this is a bind variable, stop trying to escape it.

symcbean
  • 18,418
  • 40
  • 74
  • Thanks for your response. By escape I mean the wildcard characters within the variable. Depending on the query they could be used to get more information than was intended. For instance a "starts with" query can be changed to a contains by putting a '%' at the beginning. I was more concerned whether or not it was possible to change the actual execution plan but from what I've been reading it doesn't look like it. – Dave Rager Aug 24 '17 at 12:25
  • The wildcard characters are not delimiters, hence parameter binding should not transform them (unless you're trying to apply them to a non-character data type) – symcbean Aug 24 '17 at 13:54