2017/9/1 The article was revised after being pointed out by @Kilisame. Thank you for pointing out.
--Example of escape processing --Java sample code
Please note that the symbols that must be escaped vary depending on the DB used. This time is a sample when using MariaDB.
Suppose you have a "Product List" table like this.
Product ID | Product name |
---|---|
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 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
Product ID | Product name |
---|---|
2 | Orange 100% |
4 | Apple 100% |
[3] Partial match with "_ low fat"
select product ID,Product name from product list where product name like'%\_low fat%';
result
Product ID | Product name |
---|---|
5 | milk_low fat |
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
Product ID | Product name |
---|---|
2 | Orange 100% |
4 | Apple 100% |
6 | Juice for 100 oranges |
7 | Juice for 100 apples |
Therefore, if you want to implement search processing in your application, you must escape processing for the search target word.
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();
}