SQL in java return wrong data

Question

I have a java method like this one below:

public String qE (String query, String selector) throws QSLException,    IOException{

//I get my sqlQuery from properties
String sqlQuery = properties.getPRoperty(query);
//sqlQuery = SELECT count(?) FROM employees WHERE ? is not null

PreparedStatement ps = conn.preparedStatement(sqlQuery);
ps.setFetchSize(100);
ps.setString(1,selector);
ps.setString(2,selector);

ResultSet rs = ps.executeQuery();

String rs = "";

while(rs.next()){
queryValue = rs.getString(1);
}

return queryValue;
}

When I run it with parameters qe(employees, second_name) then this query should be executed:

SELECT count(second_name)
FROM employees
WHERE second_name is not null

The problem is that non of employees has second name and I should get 0 and the whole method should return 0 but I always get diffrent number greater than zero.

Can anyone tell me why this doesn't return 0 but always diffrent number like i.e. 2399?

Answer 1

A ? represents a value not an object name, so it is equivalent to using

SELECT count('second_name')
FROM employees
WHERE 'second_name' is not null

Which is always true and is always counted. In other words, your query counts all rows in table employees.

You cannot use parameters to parameterize object names. If you really need to do this dynamically, you will need to construct the query dynamically (by concatenating the name in the query string). Just be sure to guard yourself against SQL injection if you do that (eg by checking the name against a white list or comparing explicitly to the database metadata).