Wednesday, November 12, 2014

Enabling Moodle course and activity completions en masse for Moodle 2.7

This week I am going to talk about a method to enable course completions en-masse across a library of existing Moodle courses.

Stupid Moodle Tricks!

This is my pet name for these little tips, #stupidmoodletrick anyone? Think Letterman's stupid human tricks (video below).  These are generally one-off tips, tricks, hacks to manipulate Moodle, generally as a time saver or to accomplish something Moodle isn't generally known to do.

So last week, a client asked me if I could help save them some time in converting some old Moodle 1.9 courses. Actually the more honest version is my lovely and dear wife, Michelle, volunteered that I could totally save the client hours of work by "changing a few database settings." And it turned out she was right!

The following assumes you a have already enabled course completion tracking in your site administration settings. Be aware all of these changes are accomplished by running the below queries directly on your Moodle database. As always when making these sort of changes, be sure you have a good backup and that you know how to recover it to make a working site. Things can and do go wrong and in unexpected ways. I also recommend you use a test site first to become familiar with the technique.

This method was tested on Moodle version 2.7.1+, and 2.7.2+. As with any method that directly interacts with the Moodle database, things almost certainly will change with new versions. It probably will also work with older Moodle 2 versions, but again test first.

Enable course completions on all courses

First we need to enable completion tracking for all courses. 

update mdl_course set enablecompletion = 1 where 1;

Turn on activity completion for all course modules (ie activities and resources)

This query enables the setting for all course modules across the site. It sets 'Completion Tracking' to 'Show activity as completed when conditions are met.' It also sets 'Require view' as the criteria, as illustrated in the screenshot below.

update mdl_course_modules set completion = 2, completionview = 1 where 1;

In contrast, if you wanted to set the activity completion to be marked manually by the student, you would run the following query instead.

update mdl_course_modules set completion = 1 where 1;

Which results in the following settings

Limiting which type of modules are set

Let's say we only want to enable completion tracking for all quiz modules. We would first need to have a look at the mdl_modules table and find the id assigned to the quiz module. We can use the following query.

SELECT * from mdl_modules;

Results from my test site are shown below.

We can see that the quiz module id is set to 25. Be aware this will be different for different sites. For example, I tried this on another test site and the quiz id was set to 16.

So now we can create a query to only modify quiz modules.

update mdl_course_modules set completion = 2, completionview = 1 where module=25;

Turn on grade required completion element for all quiz modules

The completion grade requirement is stored by setting a value of 0 in the completiongradeitemnumber field. Knowing this we can set the value and combined with the where clause above to limit to only quizzes. This can be a very useful technique since many compliance courses will use a quiz grade as their completion criteria.

update mdl_course_modules set completiongradeitemnumber=0 where module=25;

Note that this method only changes the setting to require a grade. It doesn't require a specific grade which need additional manual setup in the Moodle grade book. I think setting a specific grade is considerably less trivial. Let me know in the comments if you have a query / method to do this.

The overall result of applying this technique for the requesting client was to save 800 course setting edits, and about 2,500 activity setting edits which probably would have taken several days to a week to do manually.

A note about method

When developing a #stupidmoodletrick, I use a throw away test site. I usually just use the Moodle all-in-one installer to create the site. I then setup the test site with whatever test users / courses, and settings which are needed for the scenario. I then have a look at the database. The all-in-one installer includes phpMyAdmin which is an easy way to have a look at the database structure in a graphical environment. Moodle is good about using descriptive names for its tables and fields. I try to identify which table I think will be used to save the information and which are likely fields. Then, I go into Moodle and manually change the setting manually. After this, I go back to the database and look at the changes made and attempt to determine what is getting set.

Next, I attempt to manually edit the database using the information I have learned. I make the edit and then I purge all caches in Moodle. Finally, I access Moodle again to confirm that I have achieved the desired result. I also look to see if anything appears to be broken. I repeat the process until I have a working method that doesn't break anything.

Note a more thorough method is to trace the actual Moodle queries in the source code, but I find I normally don't have to do this. In particular think of settings that may impact more than one table (related tables and fields), we want to avoid introducing later hard to find problems.

Wednesday, October 29, 2014

Multi-language certificate tips for Moodle

I recently completed a project to provide a customized certificate including course completion elements for a course to be delivered in 14 languages. It was a great learning experience. This post shares a few tips and tricks learned along the way.

Some Context

This post is targeted toward any developers (or soon to be developers) that need to produce multi-language certificates of completion for Moodle. The Moodle certificate module delivers certificates by generating PDF files. I had assumed that PDFs benefited from the same gains that web applications have with the advent of unicode character sets. The reality is more nuanced when it comes to multi-language documents.

Moodle's PDF font support is broken, but soon to be fixed

During the Moodle 2 development process, Moodle HQ made a sensible decision to remove some of the less commonly used PDF fonts to reduce the size of the Moodle source code installation. This especially made sense given that there was little or no core functionality that used the TCPDF library that contained the fonts and the fonts used a lot of disk space. More recently the TCPDF library version was updated for the Moodle 2.6 release. Changes in the underlying library broke Moodle's support for installing additional fonts. See for details (fix slated for release around November 14th M2.6.6 and M2.7.3 releases). 

The idea is that you can install the full set of PDF fonts from the TCPDF project by
  1. Downloading fonts file from project
  2. Unzip and place the fonts folder into your Moodle dataroot folder
  3. Reference additional fonts by creating a custom certificate type. See for creating custom certificate type. Although Moodle 1.9 specific documentation, the Moodle 2.x version of the document doesn't have the details for making a custom type and the basic instructions are still the same.
This combination of changes makes it difficult to support some languages with certificates. Because of the bug, you will need to either replace the moodle/lib/tcpdf/fonts folder with the complete font folder download, or apply the patch / work around from the above Moodle tracker. This is especially important if you need to produce a certificate using any of the Asian languages such as Chinese, Japanese, or Korean (also referred to as CJK, or CID-0 fonts).

No universal free unicode font for all languages

For our project, we were hoping to make one custom certificate type that would work with any of the 14 language versions of the course. This would allow us to use Moodle's built-in language pack and editing capabilities to provide the correct unicode text for each language. What we found in practice is there was no single free font that had all 40k+ characters needed for universal coverage.

We also found that some fonts would have the characters for a language in one style but not another. For example we used the freeserif and freesans fonts (included in Moodle) for the prototype. We found that with a bold font style we couldn't output Hindi characters.

With some research, we did find a commercial font ($165 license fee) that has all the characters needed to make universal certificate types, but the client was not sure if the licensing terms made it legal to use in a web application such as this and opted for creating multiple certificate types.

Related links

Asian fonts are special

CID-0 fonts are "non-embeddable" fonts. The idea is that they are supposed to be provided by the PDF reader. They are a bit like core fonts in this regard, but unlike core fonts which are generally included with your PDF reader download, these fonts need to be downloaded separately. The TCPF configuration for each of these fonts seems to reference the same basic font with an additional code to specify the specific language and character set. In practice, this meant we had to generate 5 custom certificate types to get complete coverage for the 14 language course.
  • Latin, cyrillic, arabic cert type (fonts freesans, freeserif without use of bold style)
  • Traditional Chinese cert type (font cid0ct)
  • Simplified Chinese cert type (font cid0cs)
  • Korean cert type (font cid0kr)
  • Japanese cert type (font cid0jp)
If you are an Adobe Acrobat user, you will automatically be prompted to download the CID-0 fonts if you open a PDF that uses them. This brings us to our final tip.

Mac preview gotchas

My development workstation is a Mac and by default it uses the Preview application to open PDF files. We spent a lot of time trying to figure out why our CID-0 based certificates were blank. After trying the same PDF files on a Windows workstation and finding that they worked correctly, we finally determined that Preview will not prompt for the missing fonts. We also found that installing Adobe Acrobat, opening the files, and downloading the CID-0 fonts package did not fix the issue for Preview which apparently uses a different font path. I have yet to determine a procedure to fix the issue for Preview, but it's pretty easy to work around once the root cause is discovered (right click on PDF and choose open with Acrobat, or change the default PDF viewing application).

Friday, July 11, 2014

PHP 5.5 and OpCache for Moodle / Moodle 2.7 on RHEL / CentOS 5

The Problem

On a recent consulting engagement, I was tasked with helping a school eliminate a severe performance issue on its Moode site. I determined the primary solution was to add a PHP accelerator such as APC or OPCache.

Red Hat still supports RHEL 5 for security updates, but the default PHP installation is rapidly becoming unusable for current versions of Moodle. The server's administrators had manually upgraded from the PHP 5.1 version included by Red Hat to a third party PHP 5.3 build. While this allowed them to install a newer version of Moodle, it also ruined the sites performance. The third party build didn't include a PHP accelerator. Because the site was in active use there wasn't enough time to upgrade the entire operating system.

After discusion, with the client, we decided the best option forward was to upgrade to PHP 5.5 and use OPCache for PHP acceleration. This had the additional benefit of making the server ready for Moodle 2.7 and beyond. Here is how we did it.

The solution

We determined we could get a high quality PHP 5.5 build from the Remi repository ( that would continue to receive security updates. OPCache is included with PHP 5.5 and beyond.

Setup Remi repository (from cli)

rpm -ivh epel-release-5-4.noarch.rpm
rpm -Uvh remi-release-5*.rpm


Remove old PHP (from cli)

yum remove php php-soap php-intl php-cli php-mbstring php-common php-pdo php-gd php-xml php-ldap php-mysql php-xmlrpc

Install new PHP (from cli)

yum --enablerepo=remi,remi-php55 install php httpd php-soap php-intl php-cli php-mbstring php-common php-pdo php-gd php-xml php-ldap php-mysql php-xmlrpc php-opcache


OpCache Configuration

See the following Moodle docs page for Moodle specific OPCache settings


Some risks to consider before upgrading

Upgrading PHP can break older PHP applications

Make sure that your version of Moodle will work with a new PHP including all third party plugins. Also be sure any other PHP web applications are your server support the new version.

The new install may fail

There is also a chance that if you do something wrong in the repository setup or have an unusual configuration that you won't be able to successfully install the new version after removing your old PHP, so plan accordingly!

Missing PHP libraries

Each PHP build is different, not every build includes files for every library. Make sure the library you need are in the new version.

Friday, April 11, 2014

How Heartbleed is Breaking Your Moodle Security and How to Fix It!

What is Heartbleed?

Discussion of the Heartbleed vulnerability has been burning up the Internet this past week, so I won’t go into great detail about its details. Good articles here and here.

The High Points of Heartbleed

  • A vulnerability in OpenSSL
  • Impacts Apache and Ngnx web servers
  • Present for over 2 years
  • Exploit leaves no trace in server logs
  • Allows theft of security certificates, user credentials, and other site data
  • Thought to impact between 15%-33% of web-servers worldwide

How to Know if You Are Impacted

Chromebleed Plugin for Chrome

Written by security researcher Jamie Hoyle, not only does this provide an easy way to check for Heartbleed it also provides you with protection against unknowingly logging into sites with the vulnerability.

Heartbleed Checking Website

If you are not a Chrome user, Filippo Valsorda has posted a Heartbleed checking website. Just click on the URL and enter the URL of your Moodle site to check if you have the vulnerability. “


What It Means for Moodle Admins

If you have the Heartbleed vulnerability what you can’t know is if its been exploited by an outside party. Here are the overall steps you need to take to deal with the situation:

  1. Patch your webserver to use the new OpenSSL 1.0.1g release or later
  2. Generate a new SSL key, install, and revoke old key via your Certificate Authority
  3. Confirm you are no longer vulnerable
  4. Reset your users passwords (only after 1-3)

Steps one through three are absolutely required to prevent future leakage of data from your site. This is especially important now that the vulnerability is widely known.

Step four is the subject of a lot of discussion because of the challenges of resetting so many user accounts and without knowing if user data has actually been compromised. The longer you wait to patch your site the more likely that bad actors are harvesting your site’s data. Ask yourself why did I install SSL in the first place? This will help answer how important it is to force a reset of passwords. Most major cloud providers are recommending to users that they reset their passwords.

Here are some reasons to consider in favor of resetting
  • You have proprietary or sensitive business information stored in your Moodle
  • Your courses involve discussions of private or controversial topics that users wouldn’t want known outside of their classmates
  • Your user profiles contain private information
  • Your Moodle site is tied to your enterprise authentication system (LDAP, MS Active Directory, etc)
  • You’re concerned about government(s) spying on your site, or the users of your site might be in danger if the local government becomes aware of their participation in your site
  • You want to help protect your users who probably are reusing the same passwords across multiple services

How To Force Your Moodle Users to Reset Passwords

Luckily Moodle provides a built-in function which will allow you to force all your users to reset their passwords. It’s called Bulk user actions and it allows an administrator to select a group of users and apply a change across all of them. Remember that you need to do steps 1-3 before resetting passwords will help you!

Login as an administrator to your Moodle site. Expand the Site Administration menu and select Users. Under Users you will find the Bulk User actions option. Users -> Bulk User actions. Click the “Select all users” button. From pull down labelled “With selected users…”   select the “Force password change” option and you're done! Users will be asked to reset their passwords on their next login.

bulk password reset.png