Entering information about a business and finding matches

Question

I'm writing a Java Servlet application where the users must enter information about a business. I'd like to prevent as many duplicate entries in the MySQL database as possible and I'd appreciate people's comments on the logic of the process.

When the user fills out the form and submits it, the business name and address are sent to a function which splits the address string, builds a query and queries the database for like-entries. If there's no result or more than one result, the user's input gets cleaned up and stored. If there's one result, the user's data is replaced by the existing business information.

private static List<Business> getBusinesses(String name, String address) throws ClassNotFoundException, SQLException {
    List<Business> businessList = new ArrayList<>();

    Connection conn = getConnection();

    //split the address for the where clause in the query
    String[] add = address.split(" ");

    //get rid of punctuation, h/t http://stackoverflow.com/users/3053630/jeff-ward
    name.replaceAll("[^a-zA-Z0-9\\s]", "").replaceAll("\\s+", " ");

    //build query
    String query = "select id from business where ";
    for (String add1 : add) {
        query += "streetAddress like '%" + add1 + "%' and ";
    }

    //find at least a partial match to the business name
    if(name.length()>5){
        query += "businessName like '%" + name.substring(0, 4) + "%';";
    }else{
        query += "businessName like '%" + name + "%';";
    }

    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery(query);

    while(rs.next()){
        //build list of businesses
        businessList.add(getBusiness(rs.getInt("id")));
    }

    rs.close();
    s.close();
    conn.close();

    return businessList;
}

I do understand that I may get some incorrect answers (for example, "Dominos Pizza" at 55 Marbach Road will match with "Dominos Pizza" at 2557 Marbach Road), but I plan on doing further evaluation/matching before finalizing.

Answer 1

From a security perspective, you are vulnerable to malicious SQL injection, it only takes 1 malicious user..

Consider using prepared statements to plug this.

Furthermore, this hit my eye:

//find at least a partial match to the business name
if(name.length()>5){
    query += "businessName like '%" + name.substring(0, 4) + "%';";
}else{
    query += "businessName like '%" + name + "%';";
}

Since calling substring is a cheap operation, and it will return the value of name if the length is less than 5, I would go for simply

//find at least a partial match (first five characters) to the business name
query += "businessName like '%" + name.substring(0, 4) + "%';";

Also, 4 is a magical constant here, you could have named that constant and defined it.

//find at least a partial match to the business name
query += "businessName like '%" + name.substring(0, PARTIAL_MATCH_LENGTH) + "%';";

Also, in my mind, Java is for industrial strength with fullout names, so I would name some variables differently:

• add -> addressParts
• add1 -> addressPart

Finally, should getBusinesses know how to clean up name? Are you doing this in every single querying method that receives name ? There should be a class or method that knows how to clean name which should then be used by getBusinesses.