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?

second_name is not null returns everyone with a last name. second_name is null returns where everyone has no last name. Also is the last name actually null or is it an empty string like ''? — Daniel Gale
– Daniel Gale, Commented Mar 19, 2018 at 14:59
Have you tried to execute the statement on the DB directly? If so, did you get the expected output there? If not, we don't have to look at the code but on the statement and the data in your DB. — Chris311
– Chris311, Commented Mar 19, 2018 at 15:02

Mark Rotteveel · Accepted Answer · 2018-03-19 15:07:50Z

8

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).

edited Mar 19, 2018 at 15:07

answered Mar 19, 2018 at 15:00

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Over a year ago

I figure what's going to be the next question :D

Mark Rotteveel Over a year ago

@JaimeDrq I hope my edit addresses that, and if not, we'll see.

Collectives™ on Stack Overflow

SQL in java return wrong data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related