Moodle in English: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

[Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by Matthew Willis - Friday, October 18, 2019, 7:29 AM

Hi there!

I've built the below SQL report via my localhost Moodle installation's phpmyadmin (MariaDB 10.4.8), which runs perfectly. However, this query encounters the 'not allowed words' error when attempting to save it in the Configurable Reports plug-in as the SQL reserved word 'update' is used within the two concatenated strings. How can I run this query through the Configurable Reports plug-in? I tried escaping update using backticks (ie '.../php?`update`=') like I would if the reserved word was a column name, but this doesn't fix the issue.

Here's the query:

-- displays all references to lynda in the choice options table and hyperlinks them to their Moodle edit page.
select
    concat('<a target="_blank" href="%%WWWROOT%%/course/modedit.php?update=',cm.id,'">',cm.id,'</a>'),
    concat('<a target="_blank" href="%%WWWROOT%%/course/modedit.php?update=',cm.id,'">',trim(cho.text),'</a>'),
    'Choice Options'
    from mdl_choice_options cho
        join mdl_course_modules cm on cm.instance = cho.choiceid and cm.module = 5
        join mdl_choice ch on cho.choiceid = ch.id
    where cho.text like '%lynda%'

I've uploaded a screenshot of the report in the Configurable Reports plug-in and phpmyadmin. Any help is much appreciated!

Attachment config reports query not allowed words.png

Attachment config reports query phpmyadmin success.png

Re: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by David Saylor - Saturday, October 19, 2019, 2:32 AM

Unfortunately, the validation that is triggering is not smart enough to allow those words in any form.

The check is a simple regular expression match that checks for whole words that they've defined as unsafe.

If you're curious about how its defined in the code:

$regex = '/\b(ALTER|CREATE|DELETE|DROP|GRANT|INSERT|INTO|TRUNCATE|UPDATE|SET|VACUUM|REINDEX|DISCARD|LOCK)\b/i';
if (preg_match($regex, $sql)) {
    $errors['querysql'] = get_string('notallowedwords', 'block_configurable_reports');

Average of ratings:Useful (2)

Re: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by Matthew Willis - Monday, October 21, 2019, 6:45 AM

Ah, that does make sense, as the plug-in does prevent users from sending any SQL commands that can edit data in the database. Thanks for posting the code for it too! Randy's solution seems like a great workaround to this.

Average of ratings: -

Re: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by Randy Thornton - Saturday, October 19, 2019, 4:29 AM

As David said.

My usual workaround for Config Reports is to replace one of the characters of the reserved word by its CHAR equivalent. For instance, CONCAT('updat', CHAR(101)) outputs "update" and will pass the validator.

Randy

Average of ratings:Useful (2)

Re: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by Matthew Willis - Monday, October 21, 2019, 6:56 AM

Nice! Will definitely use this here. Thanks for sharing!

Putting my security hat on for a sec, I wonder if this method allow users to bypass the plug-in preventing updates to the database in this way? E.g.


concat(dro, char(112)) database moodle;

I'm no SQL extraordinaire, so I don't even know if this is valid SQL.

Average of ratings:Useful (1)

Re: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by Randy Thornton - Tuesday, October 22, 2019, 5:29 AM

Well, I don't think so, because it won't let you do anything that isn't in a SELECT statement from a specific table. If you put this there, it will just output the text with the substitutions. And it will toss an error with the semicolon too. Also, of course, since you are connecting to the db as the Moodle user, it probably has privilege to drop tables but not to drop the database (unless someone did something crazy like use mysql root).

But as with any code, someone might find a sneaky way around it.

Average of ratings:Useful (1)

Re: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by Séverin Terrier - Tuesday, October 22, 2019, 4:20 PM

Hi Randy,

Is it really necessary to use the CHAR equivalent?
Can't you just use CONCAT('up', 'date') ?

Séverin

Average of ratings:Useful (2)

Re: [Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin

by Randy Thornton - Wednesday, October 23, 2019, 4:32 AM

Séverin,

Yes, I am sure that would work. I guess I usually use CHAR simply because I sometimes need it in Ad-hoc for the various punctuation marks like : and ? I need to replace and it is a matter of habit.

Plus, of course, knowledge of ASCII codes is one of retro those skills I like to keep alive

Randy

Average of ratings:Useful (1)

Configurable reports block (plugin)

[Moodle 3.5] 'Not Allowed Words' error in SQL report, but not in phpmyadmin