General developer forum

Security Overview Report issue

 
Picture of Sudhanshu Gupta
Security Overview Report issue
 

Hi There,

Getting an issue while getting the security overview report(Site Administrator > Reports > Security Overview) in our Moodle script taking a lot of time to execute which ultimately lead to the gateway timeout for the script,  after some debugging from our end find this query (    $sqlfrom = "FROM (SELECT rcx.*

                       FROM {role_capabilities} rcx

                       JOIN {capabilities} cap ON (cap.name = rcx.capability AND ".$DB->sql_bitand('cap.riskbitmask', RISK_XSS)." <> 0)

                       WHERE rcx.permission = :capallow) rc,

                     {context} c,

                     {context} sc,

                     {role_assignments} ra,

                     {user} u

               WHERE c.id = rc.contextid

                     AND (sc.path = c.path OR sc.path LIKE ".$DB->sql_concat('c.path', "'/%'")." OR c.path LIKE ".$DB->sql_concat('sc.path', "'/%'").")

                     AND u.id = ra.userid AND u.deleted = 0

                     AND ra.contextid = sc.id AND ra.roleid = rc.roleid";)

define in function report_security_check_riskxss() in report/security/locallib.php taking lot of time(approx 14 mins) to run as we have quite handy data.


And after running it from CLI got:
+----------------------+
| COUNT(DISTINCT u.id) |
+----------------------+
|                 1092 |
+----------------------+
1 row in set (13 min 9.94 sec)

Does anybody know how can we reduce this time or any alternative solution?

 
Average of ratings: -
Picture of Darko Miletić
Re: Security Overview Report issue
Core developersParticularly helpful Moodlers

What database are you using?

In any case SQL LIKE and OR does not work well especially with MySQL.

In case you do use MySQL and it happens to be 5.6.4 or more recent you can add fulltext indexes and rework queries to use that. See this document

https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

and this one

https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html#function_match

 
Average of ratings: -