Today, a colleague found a bug in MySQL regarding fuzzy lookups, which is quite embarrassing to say the least. When I saw the problem, I didn’t even know what I had written wrong.

The problem was that I meant to find rows in the database starting with abc_, but I ended up with a statement that looked like the following

1

SELECT * FROM users_tab WHERE name LIKE 'abc_%';

Can you find the desired results this way? Find out by looking at.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> SELECT * FROM users_tab WHERE name LIKE 'abc_%';
+----+----------+
| id | name     |
+----+----------+
|  1 | abc_1    |
|  2 | abc_2    |
|  3 | abcdef_1 |
|  4 | abcdef_2 |
+----+----------+
4 rows in set (0.00 sec)

The problem is that I only know that % is a SQL wildcard, but in fact _ is also a standard SQL wildcard. % matches zero or more arbitrary characters, _ matches a single arbitrary character. See: Official MySQL documentation.

This makes the result given in the above statement clear.

So what should be done to avoid the special meaning of this particular character? The escaped character of course (the default is \). Use \_ to match _ itself, and \% to match % itself.

So, the part after my LIKE should be written: abc\_%. Is that correct?

1
2
3
4
5
6
7
8

mysql> SELECT * FROM users_tab WHERE name LIKE 'abc\_%';
+----+-------+
| id | name  |
+----+-------+
|  1 | abc_1 |
|  2 | abc_2 |
+----+-------+
2 rows in set (0.00 sec)

This error may seem very simple, but in practice there are definitely a few people who make this mistake. For example, I tried entering a % character into a lookup page on another project, and it gave me all the results.

I guess the backend is constructing the SQL statement with the parameters bound after the LIKE part.

1

arg := fmt.Sprintf("%%%s%%", input)
  • When a is entered, %a% is obtained.
  • When you type %, you get %%%.

So, all the results are matched. However, the correct expectation would be: %\%%. This means that you are looking for a result that contains the character % itself.

The error in the above is that it does not handle the special characters in input: %, _, and the escape character \.

Since \ itself is an escape character for SQL statements, if you are splicing SQL by hand (which is highly discouraged), it should be written as

1

SELECT * FROM users_tab WHERE name LIKE '\\\\';

Whereas, if it is parameter bound, it should be written as

1

SELECT * FROM users_tab WHERE name LIKE ?;

where ? is bound with the argument \\, similar to \_ and \%.

For this case of parameter binding, I wrote a golang program to help with the conversion.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

package main

import (
	"fmt"
	"strings"
)

var escapeReplacer = strings.NewReplacer(`%`, `\%`, `_`, `\_`, `\`, `\\`)

func main() {
	fmt.Println(escapeReplacer.Replace(`abc_`))
	fmt.Println(escapeReplacer.Replace(`%`))
	fmt.Println(escapeReplacer.Replace(`\`))
	fmt.Println(escapeReplacer.Replace(`_`))
}

Results of the run.

1
2
3
4
5

$ go run main.go
abc\_
\%
\\
\_

After such a substitution operation, the result can be safely used as a parameter to the LIKE statement.

The use of other escape characters is also possible and is not discussed here.