Escape processing when searching for LIKE

2017/9/1 The article was revised after being pointed out by @Kilisame. Thank you for pointing out.

What is known in this article

--Example of escape processing --Java sample code

Caution

Please note that the symbols that must be escaped vary depending on the DB used. This time is a sample when using MariaDB.

Commentary

Suppose you have a "Product List" table like this.

Here is the SQL statement to insert into the DB.

insert into product list(Product ID,Product name) values ('1', 'Cola'),
('2', 'Orange 100%'),
('3', 'milk'),
('4', 'Apple 100%'),
('5', 'milk_low fat'),
('6', 'Juice for 100 oranges'),
('7','Juice for 100 apples');

Here is the SQL statement that performs a partial match search for this "product list" table. [1] Partial match with "milk"

select product ID,Product name from product list where product name like'%milk%';

[2] Partial match with "100%"

select product ID,Product name from product list where product name like'%100\%%';

result

[3] Partial match with "_ low fat"

select product ID,Product name from product list where product name like'%\_low fat%';

result

The "%" and "_" symbols have a special meaning for the DB, so the intended partial match search cannot be performed unless the escape symbol "" is added in front of it, as in [2] and [3]. As a test, I tried a LIKE search without the escape symbol,

【4】

select product ID,Product name from product list where product name like'%100%%';

result

Therefore, if you want to implement search processing in your application, you must escape processing for the search target word.

Sample code

public void getName(String word){
    Connection con = null;
    String sql = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try{
        con = this.getConnection();
        sql = "select product name from product list where product name like?";
        ps = con.prepareStatement(sql);
        ps.setString(1, "%" +this.escape(word) + "%");
        rs = ps.executeQuery();
        
        rs.close();
        ps.close();
    }finally{
        if(con != null){
            con.close();
        }
    }
}

public String escape(String before){
    StringBuilder after = new StringBuilder();
		String esSymbol = "\";
		char es1 = '_';
		char es2 = '%';

		for (int i = 0; i < before.length(); i++) {
			if (before.charAt(i) == es1 || before.charAt(i) == es2) {
				after.append(esSymbol);
				after.append(String.valueOf(before.charAt(i)));
				continue;
			}
			after.append(String.valueOf(before.charAt(i)));
		}
		return after.toString();
}