Search code examples
javasql-serverjdbcjtds

resultSet obtained if parameter containing whitespace concatenated, but not using setString

I have this piece of code, with a prepared statement. I know the query is redundant. the parameter id is a string <space>413530 (" 413530"). Please note the preceding whitespace character.

String query = "SELECT RSCode as id FROM Customer WHERE RSCode=?";

PreparedStatement newPrepStatement = connection
        .prepareStatement(query);
newPrepStatement.setString(1, id);
resultSet1 = newPrepStatement.executeQuery();

while (resultSet1.next()) {
    System.out.println("Got a result set.");
    logindata.add(resultSet1.getString("id"));
}

I do not get any results after executing this query.

Now, if I use the same statements and append the parameter as part of the string as follows:

String query = "SELECT RSCode as id FROM Customer WHERE RSCode=" + id;

PreparedStatement newPrepStatement = connection
        .prepareStatement(query);
resultSet1 = newPrepStatement.executeQuery();

while (resultSet1.next()) {
    System.out.println("Got a result set.");
    logindata.add(resultSet1.getString("id"));
}

I get a result as after executing this prepared statement. Same also works with a java.sql.statement

I wish to know why the driver ignores the whitespace in the second piece of code, but has a problem in the first part.

Solution

  • If you use setString the parameter will be bound as a string resulting in this SQL (considering the bound parameter an SQL string):

    SELECT RSCode as id FROM Customer WHERE RSCode=' 0123';

    If you use concatenation the SQL used will be (considering the concatenated value as an integer, since space will be ignored as part of the SQL syntax):

    SELECT RSCode as id FROM Customer WHERE RSCode=<space>0123;

    In this case I would advise to convert it to int or long or whatever it is and bind it with the right type. With setInt() or setLong().

    And if you field is a string you could normalize it first using for example:

    String normalizedValue = String.trim(value);
newPrepStatement.setString(1, normalizedValue);

    or even direct in SQL like:

    SELECT RSCode as id FROM Customer WHERE RSCode=TRIM(?);