NamedParameterJdbcTemplate substituing values with single quotes

Question

    @Autowired
     NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
       public List<Contact> findByPhoneWithNamedParameters(String phone) {
            MapSqlParameterSource namedParameters = new 
            MapSqlParameterSource().addValue("phone", phone);
            String sqlQ = getSqlQuery(namedParameters);
            namedParameters.addValue("anotherCondition", sqlQ);
            String sql = "select * from Contact c where c.phone=:phone 
 :anotherCondition";
            return namedParameterJdbcTemplate.query(sql, namedParameters, new ContactRowMapper());
        }
    
        private String getSqlQuery(MapSqlParameterSource namedParameters) {
            return " and c.name='Saman'";
        }

The above one is generating the query like below:

select * from Contact c where c.phone='09137390432' ' and c.name=''Saman'''

getSqlQuery() return value is embeddigng within single quotes, with that query is not working as expected.

I tried to concatenate the value directyle instead of namedParams; But in my case, I have to avoid the SQL Injection.

How to resolve this?

Answer 1

You should only use named parameters with the actual values you want to pass to the query, not with whole SQL sub-parts. Otherwise it will escape also the valid SQL quotes.

So you should change your code to something like this:

private String getAdditionalSqlConditions(MapSqlParameterSource namedParameters) {
   namedParameters.add("name", "Saman");
   return "c.name = :name";
}

 public List<Contact> findByPhoneWithNamedParameters(String phone) {
    MapSqlParameterSource namedParameters = new MapSqlParameterSource().addValue("phone", phone);
    String conditions = getAdditionalSqlConditions(namedParameters);
    String sql = "select * from Contact c where c.phone=:phone and " + conditions);

    return namedParameterJdbcTemplate.query(sql, namedParameters, new ContactRowMapper());
}