APOSTROPHE issue with java and SQL

Question

I have code, where I have single quote or APOSTROPHE in my search

I have database which is having test table and in name column of value is "my'test"

When running

SELECT * from test WHERE name = 'my''test';

this works fine

If I use the same in a Java program I am not getting any error or any result

But If I give the name with only single quote then it works

SELECT * from test WHERE name = 'my'test';

Could you please help me out to understand.

Java code is

    Connection con = null;
    PreparedStatement prSt = null;
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.
            getConnection("jdbc:oracle:thin:@localhost:1521:orcl"
                ,"user","pwd");
        String query = "SELECT * from "
                + "WHERE name = ? ";
        prSt = con.prepareStatement(query);

        String value = "my'mobile";
        char content[] = new char[value.length()];
        value.getChars(0, value.length(), content, 0);
        StringBuffer result = new StringBuffer(content.length + 50);
        for (int i = 0; i < content.length; i++) {
            if (content[i] == '\'')
            {
                result.append("\'");
                result.append("\'");
            }
            else
            {
            result.append(content[i]);
            }
        }
        prSt.setObject(1, result.toString());
        int count = prSt.executeUpdate();
        System.out.println("===============>    "+count);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        try{
            if(prSt != null) prSt.close();
            if(con != null) con.close();
        } catch(Exception ex){}
    }

Show us your Java code. But it looks like you are not using a `PreparedStatement` otherwise you wouldn't have a problem — a_horse_with_no_name, Jan 03 '17 at 15:35
You are overcomplicating things. Just use `prSt.setString(1, "my'mobile")` — a_horse_with_no_name, Jan 03 '17 at 15:50
That is the beauty of `PreparedStatement`: You don't have to escape anything, the JDBC driver will do any necessary escaping for you. — Andreas, Jan 03 '17 at 15:57
@ andreas thanks for reply but it used to work with above code but suddenly i think after java 1.7 i think it is not working again — Sudhakar Kummarasetty, Jan 03 '17 at 16:00
any one can help why sql developer is taking '' and java only taking ' — Sudhakar Kummarasetty, Jan 03 '17 at 16:03
Because you are using the `PreparedStatement` the wrong way. You must not escape single quotes when using a `PreparedStatement`. That is only required when you use varchar _literals_ inside the SQL code, but not when the parameter sent "outside" of the statement. You would need to escape the single quote if you concatenate the value into the SQL string - but that is something you should never do. — a_horse_with_no_name, Jan 03 '17 at 17:42

score 1 · Accepted Answer · answered Jan 03 '17 at 17:46

You don't have to escape anything for the parameter of a PreparedStatement

Just use:

prSt = con.prepareStatement(query);
prSt.setString("my'mobile");

Additionally: if you are using a SELECT statement to retrieve data, you need to use executeQuery() not executeUpdate()

ResultSet rs = prst.executeQuery();
while (rs.next())
{
   // process the result here
}

You might want to go through the JDBC tutorial before you continue with your project: http://docs.oracle.com/javase/tutorial/jdbc/index.html

APOSTROPHE issue with java and SQL

1 Answers1