EclipseLink-Bug? Simple native SQL query exception

Question

I can't execute the following query with EclipseLink:

String query = "insert into A_TEST (TEST_NAME) values ('Ain''t it cool? It''s cool, or?')";


    EntityManagerFactory emf = Persistence.createEntityManagerFactory("jdbc-unit");
    EntityManager em = null;

    try {
        em = emf.createEntityManager();
        em.getTransaction().begin();

        Query q = em.createNativeQuery(query);
        //q.setHint(QueryHints.BIND_PARAMETERS, HintValues.FALSE);
        q.executeUpdate();

        em.getTransaction().commit();

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (em != null) {
                if (em.getTransaction().isActive()) 
                    em.getTransaction().rollback();
                em.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

Stacktrace:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Ungültiger Spaltenindex
Error Code: 17003
Call: insert into A_TEST (TEST_NAME) values ('Ain''t it cool? It''s cool, or?)
bind => [1 parameter bound]
Query: DataModifyQuery(sql="insert into A_TEST (TEST_NAME) values ('Ain''t it cool? It''s cool, or?)")
at org.eclipse.persistence.internal.jpa.QueryImpl.executeUpdate(QueryImpl.java:308)
at persistence.test.ErrorTest.main(ErrorTest.java:43)

I've found out that the problem is about the question marks and quotes. If I remove it is works. Also the order of these characters seems to be important.

Does anybody know the issue?

PS: The line "q.setHint(QueryHints.BIND_PARAMETERS, HintValues.FALSE);" makes another exception.

I've solved it: q.setHint(QueryHints.BIND_PARAMETERS, HintValues.TRUE); works for me

Tomas Kraus Tomas Kraus · Accepted Answer · 2015-09-17T10:39:53

I would relay on query parameter to do proper escaping for me. Check EclipseLink help pages. Passing whole SQL as String may also cause security issues (SQL injections) if part of such a query is user input.

query = "insert into A_TEST (TEST_NAME) values (?)";
...
query.setParameter(1, "Ain't it cool? It's cool, or?");

EclipseLink-Bug? Simple native SQL query exception

1 Answers