Create a new Moodle and Drupal user directly in mySQL

On the long road to achieve single-sign on among open-source applications on a small, school network, here is a tip. You may create a new local user in Moodle or Drupal with mySQL statements.

We use this to register parents on our intranet web site. I maintain a small Perl script that verifies new parent users against Education Edge by email address and then creates the new account in both Moodle and Drupal, the two content-management systems we are using right now.

Drupal 5.1

INSERT INTO `users` SET `name`='$name', `pass`='" . md5_hex($pass) . "', `mail`='$mail', `init`='$mail', `created`=UNIX_TIMESTAMP(), `data`='a:():{}', `status`='1'

Moodle 1.8

INSERT INTO `user` SET `auth`='manual', `confirmed`='1', `policyagreed`='1', `mnethostid`='1', `username`='$username', `password`='" . md5_hex($password) . "', `email`='$email', `idnumber`='$eeid', `firstname`='$firstname', `lastname`='$lastname', `description`='$description'

Of course, you have already escaped illegal characters from your scalars by now in order to avoid SQL malicious code injection! Also, these statements use the Perl MD5 library. PHP syntax will be slightly different.

8 comments

  1. Bill Fitzgerald says:

    Hello, Richard,

    RE: "Of course, you have already escaped illegal characters from your scalars by now in order to avoid SQL malicious code injection!" —

    This is precisely why it’s better to use the UI of the given apps, as the code of both these apps sidesteps precisely these security holes — additionally, for systems that don’t want to install mod_perl and the Perl MD5 library, this is an added layer of work, and an additional thing that can break/be difficult to maintain over time.

    It would be simpler to have Drupal and Moodle authenticate against your main record source via LDAP, or have Drupal authenticate via LDAP, and Moodle authenticate against Drupal’s user tables. These are behaviors native to both apps that can be configured largely through the UI in a relatively vanilla server environment. Additionally, this can be configured safely by an admin who might not know how to escape illegal characters.

    Additionally, in Drupal, what’s happening in the sessions table? In 4.7, creating users with direct sql inserts led to Great Pain unless you looked at the sessions table as well — It’s possible that this is happening automagically in 5, but it didn’t in 4.7 —

    Cheers,

    Bill

  2. Bill Fitzgerald says:

    In the above comment, substitute "sequences table" for "sessions table" — even writing about it is tricky 🙂

  3. rkassissieh says:

    Bill,

    Here are the requirements of this registration tool:

    – Parents have a single place to register an account on our intranet.
    – Registration must be verified against Education Edge, using parent email on record.

    As I see it, there is no way to use the built-in UI from Moodle or Drupal to do this. I would have to send parents to two different sites and would lose the parent authentication.

    LDAP works great for our network users — students, teachers, and staff. Parents don’t have network accounts, nor would we want to pay for and manage those.

    If you want to be careful, you should closely examine the Moodle and Drupal source code to determine how they create new user accounts and then copy that methodology into your account creation script. As it so happens, I modified the schema of the Drupal users table to auto increment the user id. It’s too bad that Drupal decided to use sequences instead of auto_increment, but my modification seems compatible with that approach. Sequences is still updating correctly. Moodle uses auto_increment, if I recall correctly.

    I haven’t tried to configure either Moodle or Drupal to authenticate against the user database of the other CMS. That might be another good approach, but then one of the two systems would have to support two external authentication sources. I think Moodle can do that. I’m not so sure about Drupal.

    Richard

  4. Wesley Hampton says:

    Richard, you do a fantastic job forging ahead with this SSO/Moodle stuff, and the key part is that you are documenting it, which helps others. Great work and thanks for taking time to post this to the world!

    Thanks, Wes

  5. rkassissieh says:

    Gosh, thanks Wes. What are you doing with Moodle?

    Richard

  6. Bill Fitzgerald says:

    Hello, Richard,

    You indeed have a unique use case, and a unique solution —

    Building out a block for Moodle or a module for Drupal to manage the registration would have left you more (and more flexible) options when it came time to upgrade — as always, solving a problem is a balance between available time and available resources —

    Good luck as you move forward.

    Bill

  7. rkassissieh says:

    Bill,

    You were right, as always. Why does it take me months to realize this? Your point (above) about the sequences table was spot on at the time, and I only fixed it tonight while troubleshooting a vexing problem with Drupal automatic creation of LDAP users. Naturally, I added the fix to my parent registration script, so I am still pressing forward with my foolhardy solution, but now it is more robust, thanks to your feedback!

    Richard

  8. Bill Fitzgerald says:

    Hello, Richard,

    I’m glad this worked out for you, and glad that this conversation helped with the bug-squashing.

    Cheers,

    Bill