Affects Version/s: 3.7.6, 3.8.2, 3.9
This has been raised by Eduard Cercós on our https://moodle.org site. We are experiencing several slow SQL queries triggered e.g. on the /login/forgot_password.php, rsslib.php or the signup page. They significantly increase the DB load (queries take up to 30 secs to finish on our prod site) and may represent a weak point and a surface for potential DoS attacks.
I was able to trace it down and confirm it. There is a query in core_login_process_password_reset() where we search for the user record matching the provided email. That search is intended to be case insensitive yet accent sensitive. As such it leads to SQL comparison
Likely because the LOWER function is involved, the DB engine does not make use of the index for the email column and instead, it performs a sequential scan:
As you can see, in our case, that means that more than 1 million records have to be checked and the LOWER comparison performed for each of them.
This can improve significantly if the search is both case insensitive and accent insensitive. In that case, the MySQL uses the comparison
that can make use of the index over the email column:
This pattern repeats in all other reported places and happens everywhere where we search for users by email.
My proposal is to change these functions so that
- they perform the accent insensitive search first to get a short list of candidate users
- and only then perform another accent sensitive search just among them using the additional id IN (...) condition.
This is also a good opportunity to rewrite the existing LIKE statements into proper comparison statements via the sql_equal() method (had not existed yet when some of these queries were implemented).