Can a sql query with both user input and parameter be prone to sql injection?

Question

I've stumbled over some code in an application where (certain) users are allowed to input where conditions. In addition to this there is a part of the where condition that is set as a parameterized query.

E.g.:

select foo from bar where <user generated> and foo = ? 

(Besides this the user generated portion is run through a filter of sorts before the query is constructed that only allows certain input)

The application uses JDBC against Mysql (Connector/J). This driver do, as far as I can understand, the preprocessing of prepared statements on the client side. If sql injection is possible, would it be possible to "escape" out of the parameterized where condition? Perhaps with subqueries?

Answer 1

Yes. Pretty much any sql query that concatenates raw or imperfectly protected user input can be vulnerable to injection.

There are some restrictions. For example, The user input has to be long enough to be a viable attack (single character injection may not be enough, given the specific use of the soecific statement in question). It has to be usable data for the attack (input validation may restrict the input to a know white list of safe values, such as {1, 2, 3}). It cannot be encoded in another, benign form (like basese64 encoding the user data).

The rule of thumb you should use is "if it's concatenated, it's a vulnerability unless proven otherwise." It's generally cheaper and faster and easier to fix than to prove it's not a vulnerability, so it should just be fixed.