Escaping single quotes in table SQL queries

Question

Using java.sql.Preparedstatement implies the escaping of characters will be done while parsing the queries, this does happen also when I have single quotes in my data but when I have single quotes in my table name itself the query does not work (I am using Oracle 11g).

Here's my code:

Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection(
    "jdbc:oracle:thin:client/adept@ind-db-02:1521:ind02");

PreparedStatement preparedStatement = con.prepareStatement(
    "SELECT * FROM (?) where rownum=1");

preparedStatement.setString(1,"CLIENT.\"SR'tab\"");
ResultSet rs3=preparedStatement.executeQuery();

Is there any way of escaping single quotes from the table name using a prepared statement?

Answer 1

PreparedStatement placeholders are not intended for table names nor column names, they are only intended for actual column values. In other words, you are actually misusing PreparedStatement.

See also

• Using Prepared Statements

Answer 2

hi!

Although it is very correct to use bind parameters to pass data to the database, you can not use bind parameters for table names.

In that case, it should be enought to properly quote it.

e.g. (untested):

PreparedStatement preparedStatement=
  con.prepareStatement("SELECT * FROM \"CLIENT.SR'tab\" where rownum=1"); 

Answer 3

I recommend you change those table names now, to prevent agony in the future. The high-powered tools we all use are great but when one colors too far outside the lines, the pain is unending.