SQL error when processing messages for Direct addresses that include apostrophes #260
Description
The method org.nhindirect.config.store.dao.impl.AddressDaoImpl#listAddresses(java.util.List, org.nhindirect.config.store.EntityStatus) will create an a malformed SQL statement if one of the Direct addresses provided in the list includes an apostrophe.
Here's the existing method
/*
* (non-Javadoc)
*
* @see org.nhindirect.config.store.dao.AddressDao#listAddresses(java.util.List, org.nhindirect.config.store.EntityStatus)
*/
@SuppressWarnings("unchecked")
@Transactional(readOnly = true)
public List<Address> listAddresses(List<String> names, EntityStatus status) {
if (log.isDebugEnabled())
log.debug("Enter");
List<Address> result = null;
Query select = null;
if (names != null) {
StringBuffer nameList = new StringBuffer("(");
for (String aName : names) {
if (nameList.length() > 1) {
nameList.append(", ");
}
nameList.append("'").append(aName.toUpperCase(Locale.getDefault())).append("'");
}
nameList.append(")");
String query = "SELECT a from Address a WHERE UPPER(a.emailAddress) IN " + nameList.toString();
if (status != null) {
select = entityManager.createQuery(query + " AND a.status = ?1");
select.setParameter(1, status);
} else {
select = entityManager.createQuery(query);
}
} else {
if (status != null) {
select = entityManager.createQuery("SELECT a from Address a WHERE a.status = ?1");
select.setParameter(1, status);
} else {
select = entityManager.createQuery("SELECT a from Address a");
}
}
@SuppressWarnings("rawtypes")
List rs = select.getResultList();
if ((rs.size() != 0) && (rs.get(0) instanceof Address)) {
result = (List<Address>) rs;
} else {
result = new ArrayList<Address>();
}
if (log.isDebugEnabled())
log.debug("Exit");
return result;
}
Here's a possible replacement method
/*
* (non-Javadoc)
*
* @see org.nhindirect.config.store.dao.AddressDao#listAddresses(java.util.List, org.nhindirect.config.store.EntityStatus)
*/
@SuppressWarnings("unchecked")
@Transactional(readOnly = true)
public List<Address> listAddresses(List<String> names, EntityStatus status) {
if (log.isDebugEnabled())
log.debug("Enter");
List<Address> result = null;
Query select = null;
if (names != null) {
String query = "SELECT a from Address a WHERE a.emailAddress IN (?1)";
if (status != null) {
select = entityManager.createQuery(query + " AND a.status = ?2");
select.setParameter(2, status);
} else {
select = entityManager.createQuery(query);
}
select.setParameter(1, names);
} else {
if (status != null) {
select = entityManager.createQuery("SELECT a from Address a WHERE a.status = ?1");
select.setParameter(1, status);
} else {
select = entityManager.createQuery("SELECT a from Address a");
}
}
@SuppressWarnings("rawtypes")
List rs = select.getResultList();
if ((rs.size() != 0) && (rs.get(0) instanceof Address)) {
result = (List<Address>) rs;
} else {
result = new ArrayList<Address>();
}
if (log.isDebugEnabled())
log.debug("Exit");
return result;
}
The IN clause (in MySQL at least) appears to be case insensitive so there may be no need to worry about converting everything to the same case, of course this may not be the case with MSSQL, Oracle, PostgreSQL etc. So iterating through the names list and and ensuring a common case is an option... for example
/*
* (non-Javadoc)
*
* @see org.nhindirect.config.store.dao.AddressDao#listAddresses(java.util.List, org.nhindirect.config.store.EntityStatus)
*/
@SuppressWarnings("unchecked")
@Transactional(readOnly = true)
public List<Address> listAddresses(List<String> names, EntityStatus status) {
if (log.isDebugEnabled())
log.debug("Enter");
List<Address> result = null;
Query select = null;
if (names != null) {
List<String> upperNames = new ArrayList(names.size());
for (String aName : names) {
upperNames.add(aName.toUpperCase(Locale.getDefault()));
}
String query = "SELECT a from Address a WHERE UPPER(a.emailAddress) IN (?1)";
if (status != null) {
select = entityManager.createQuery(query + " AND a.status = ?2");
select.setParameter(2, status);
} else {
select = entityManager.createQuery(query);
}
select.setParameter(1, upperNames);
} else {
if (status != null) {
select = entityManager.createQuery("SELECT a from Address a WHERE a.status = ?1");
select.setParameter(1, status);
} else {
select = entityManager.createQuery("SELECT a from Address a");
}
}
@SuppressWarnings("rawtypes")
List rs = select.getResultList();
if ((rs.size() != 0) && (rs.get(0) instanceof Address)) {
result = (List<Address>) rs;
} else {
result = new ArrayList<Address>();
}
if (log.isDebugEnabled())
log.debug("Exit");
return result;
}