MDL-68183 auth: Fix the performance of signup_validate_data search query
authorDavid Mudrák <david@moodle.com>
Tue, 17 Mar 2020 12:04:07 +0000 (13:04 +0100)
committerDavid Mudrák <david@moodle.com>
Tue, 17 Mar 2020 13:52:32 +0000 (14:52 +0100)
commit3621b497d2ec024c61e5634de6a175c03e85c22c
treed365ac4800bea8da66e5d65e1d78d4171cfa1944
parent77bc884473367655a6fc3e8543aebeed37a7f23d
MDL-68183 auth: Fix the performance of signup_validate_data search query

When searching for other users with the same email address, we perform
the case-insensitive and accent-sensitive search. That may be expensive
as some DBs such as MySQL cannot use the index in that case. Instead,
sequential scan of all the user records is performed and the comparison
uses the LOWER function to filter the matching records. This leads to
significant performance heavy queries which in turn represent a surface
for DoS attacks.

For that reason, we first perform accent-insensitive search for
potential candidates in a subselect, which can use the index. Only then
we perform the additional accent-sensitive search on this limited set or
records.
lib/authlib.php
lib/tests/authlib_test.php