java resultset.getstring("col_name") query

Anubhab picture Anubhab · Apr 5, 2013 · Viewed 29.9k times · Source

I have a simple query regarding ResultSet.getString() method in java for JDBC.
Suppose the value in the Database column is having a \ which is javas escape character e.g. \n or \t etc.
When i retrieve the value as getString() i see one more escape character is getting added and the actual meaning of this \n is now a string literal only.
So i had to unescape java and then use it properly.

String s= rs.getString("col_name");

When s contains `\n':

System.out.println(s)

output:

\n

After unescaping java using apache common StringEscapeUtils
output:

System.out.println("hi"+s+"hello");
hi
hello

My question is who is adding this extra \ before unescaping?? Sorry if it is a dumb question.

Answer

A.H. picture A.H. · Apr 5, 2013

The JDBC driver does no escaping in the ResultSet. It would be very bad if it does. Look at this simple example:

The database table x does contain one column value. There are two rows: One with a two character string ('\' and 'n') and one witch a one character string (the newline character). I've added a string-length to the output for clarification.

select *, length(value) from x;
 value | length 
-------+--------
 \n    |      2
      +|      1
       | 

This table is read with JDBC:

    Connection db = DriverManager.getConnection( /* these parameters are up to you */ );

    Statement st = db.createStatement();
    ResultSet rs = st.executeQuery("select value, length(value) from x");
    while(rs.next()){
        String value = rs.getString(1);
        int length = rs.getInt(2);

        System.out.println("length="+length+", value='"+value+"'");
    }

You see: no explicit escaping anywhere in the code so far. And the output is:

length=2, value='\n'
length=1, value='
'

You see: There is still nothing escaped - neither by the code nor by JDBC.

BUT

Things get a bit murky if you mix in Java literals: If you do something like this:

st.execute("insert into x values ('\n')");

then guess what happened? You have another row with one character!

length=2, value='\n'
length=1, value='
'
length=1, value='
'

This is because the Java compiler translated the two characters '\n' into one newline character. Hence the JDBC driver and the database only see one character.

But if you would have read some userinput and the user would have typed \ and n then nobody would de-escape this and the row would have contained two characters.

Next step

You say, you do explicit de-escaping using StringEscapeUtils. Then this would happen:

  • The row with one character will pass unaltered from the DB up to the screen.
  • The row with two characters will be changed by StringEscapeUtils into a string containing one characters. After that both row look like the same to you.

Summary

Do not confuse String-Literal escaping by the compiler with escaping by JDBC (which won't happen).

Do not add unnecessary escaping layers.