setParameter For Two Or More Arguments Not Working In Jpa (Mysql Injection)

Question

Hello guys...i was looking a way to avoid mysql injection in jpa..and got a link http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-persistence-api-jpa which is cool...and works fine for single argument here is the code...

            StringBuilder getCity = new StringBuilder();
            getCity.append("SELECT ");
            getCity.append(" CITY.* ");
            getCity.append("FROM  ");
            getCity.append(" CITY  ");
            getCity.append("WHERE ");
            getCity.append("CITY.NAME LIKE ?1");
            System.out.println(getCity.toString());
            getSearchQuery=entityManager.createNativeQuery(getCity.toString(),CityModel.class).setMaxResults(1);
            getSearchQuery.setParameter(1,QueryToSearch);
            City=(CityModel)getSearchQuery.getSingleResult();

Here is the other code where two argument are there..

getCity.append("SELECT ");
            getCity.append(" CONCAT_WS('<br />',CITY.NAME,CITY.ADDRESS) ");
            getCity.append("FROM  ");
            getCity.append("CITY  ");
            getCity.append("WHERE ");
            getCity.append(" (CITY.NAME LIKE ");
            getCity.append(" ?1 OR CITY.ADDRESS LIKE ");
            getCity.append(" ?2)");
            getCity.append(" AND ");
            getCity.append(" CITY.STATUS=");
            getCity.append("'"+"ACTIVE"+"'");
            getCity.append(" AND CITY.TYPE= ?3");
            System.out.println(getCity.toString());
            getSearchQuery=entityManager.createNativeQuery(getCity.toString());
            getSearchQuery.setParameter(1,QueryToSearch);
            getSearchQuery.setParameter(2,QueryToSearch);
            getSearchQuery.setParameter(3,CityType);

NOTE:Works fine even in second query if i put only one LIKE...(Multiple like and argument not working) And Just curious how does LIKE ?1 in query works %Type% Or %Type Or Type :)

see http://stackoverflow.com/questions/327765/wildcards-in-java-preparedstatements for the answer. This question will be closed as a duplicate, so you may just want to delete it. — Guy Schalnat, Jul 14 '15 at 08:01
Like question for getting knowledge....but what you are pointing is Sql Statement...i am asking in jpa — Gopi Lal, Jul 14 '15 at 08:08
You are correct. Try http://stackoverflow.com/questions/1341104/parameter-in-like-clause-jpql, or, in official documentation, https://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvg — Guy Schalnat, Jul 14 '15 at 11:12
@GuySchalnat Man This helped me....You know the Mistake Missing % :"%"+QueryToSearch+"%" man getting crazy...But i can search without % Right in native query LIKE — Gopi Lal, Jul 16 '15 at 10:23

score 1 · Accepted Answer · answered Jul 16 '15 at 10:27

For Future Users Who Wants LIKE in jpa Native Query

Wrong One...

 getSearchQuery.setParameter(1,QueryToSearch);
 getSearchQuery.setParameter(2,QueryToSearch);
 getSearchQuery.setParameter(3,CityType);

Right One

 getSearchQuery.setParameter(1,"%"+QueryToSearch+"%");
 getSearchQuery.setParameter(2,"%"+QueryToSearch+"%");
 getSearchQuery.setParameter(3,CityType);

setParameter For Two Or More Arguments Not Working In Jpa (Mysql Injection)

1 Answers1