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
Can you find the desired results this way? Find out by looking at.
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
\_ to match
_ itself, and
\% to match
So, the part after my
LIKE should be written:
abc\_%. Is that correct?
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
- 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
The error in the above is that it does not handle the special characters in
_, and the escape character
\ itself is an escape character for SQL statements, if you are splicing SQL by hand (which is highly discouraged), it should be written as
Whereas, if it is parameter bound, it should be written as
? is bound with the argument
\\, similar to
For this case of parameter binding, I wrote a golang program to help with the conversion.
Results of the run.
After such a substitution operation, the result can be safely used as a parameter to the
The use of other escape characters is also possible and is not discussed here.