1

What is wrong with the following statement?

        String sql = "INSERT INTO tablename (colname) VALUES ($tag$$${a{e^i\\pi}a}$$$tag$);";
        Connection connection = DB.getConnection();
        Statement st = connection.createStatement();       
        st.executeQuery(sql);

After execution, there should be a new row int tablename, with $${a{e^i\pi}a}$$ in colname (type: text). But all I get is $${ae^i\pia}$$, that is the expected without the braces.

I tested it in Java 7.11, PostgreSQL 9.1 (x64) on a Windows 7 machine.

mikemike
  • 157
  • 6

2 Answers2

2

Just use prepared statements so you will never have errors with unescaped Strings because SQL does that for you automatically.

  st = conn.prepareStatement("INSERT INTO tablename (colname) VALUES (?)");
  st.setString(1, "$${a{e^i\\pi}a}$$");
  st.executeUpdate();

For additional information you can look at the answer to this question: Java - escape string to prevent SQL injection

Community
  • 1
  • 1
th3falc0n
  • 1,389
  • 1
  • 12
  • 33
1

This is caused by the driver's JDBC escaping e.g. to support the {call ...} or {fn ...} syntax.

According to this thread:

http://postgresql.1045698.n5.nabble.com/plpgsql-function-comment-with-single-quote-braces-td5155934.html

you need to call statement.setEscapeProcessing(false) to prevent the driver to parse JDBC escape syntax.

Note that this only happens when using dollar quoting. String literals inside "regular" single quotes are not subject to JDBC escapes (just as values for PreparedStatements)