CODEWITHSUNDEEP
hibernatespring-bootjpa
John Kulova
John Kulova

Reputation: 487

Space is not allowed after parameter prefix ':' JPA

This is my query:

EntityManager em = null;
        EntityTransaction et = null;
        try {
            em = entityManagerFactory.createEntityManager();
            et = em.getTransaction();
            et.begin();
            String q = "UPDATE naeb_application_processes SET process_info="+processinfo+", status=1 WHERE application_id="+naebappid+" AND process_id=44";
            System.out.println(q);
            Query query = em.createNativeQuery(q);
            query.executeUpdate();
            et.commit();
        } catch (Exception e) {
            if(et != null) {
                et.rollback();
            }
            // TODO: handle exception
            e.printStackTrace();
            resp = "FAILED";
        }
        finally {
            em.close();
            resp = "OK";
        }

I get the error:Space is not allowed after parameter prefix ':' I have tried escaping with \:= but it didnt work

Upvotes: 1

Views: 2958

Answers (1)

coladict
coladict

Reputation: 5095

The problem is you're not using Prepared Statements, and it also makes you vulnerable to SQL injection.

    EntityManager em = entityManagerFactory.createEntityManager();
    EntityTransaction et = null;
    try {
        et = em.getTransaction();
        et.begin();
        String q = "UPDATE naeb_application_processes SET process_info=:pinfo, status=1 WHERE application_id=:appid AND process_id = :pid";
        System.out.println(q);
        Query query = em.createNativeQuery(q);
        query.setParameter("pinfo", processinfo);
        query.setParameter("appid", naebappid);
        query.setParameter("pid", 44); //or 44L depending on your database and layout
        query.executeUpdate();
        et.commit();
        resp = "OK";
    } catch (Exception e) {
        if(et != null) {
            et.rollback();
        }
        // TODO: handle exception
        e.printStackTrace();
        resp = "FAILED";
    }
    finally {
        em.close();
    }

Every parameter that comes from outside has to be added as a name in the query, beginning with : and should be simple as shown above. Then you pass those parameters onto the query using query.setParameter. Always follow this practice to keep your data secure.

Another thing you should do is make sure you create only one EntityManager for each HTTP request, not for each query, and always close it in a try-finally statement like this one.

Upvotes: 2

Related Questions