Resetting the root password/privileges for MySQL/PHPMyAdmin (XAMPP 1.7.3, MySQL 5.1.41)

We use XAMPP for our local development (on Windows), and I have a habit of messing around with the database and doing bad things by mistake, because it’s only for local development, so it doesn’t matter that much. It’s still annoying when I lock myself out of all my databases though!

While setting up a wordpress site locally for testing, I managed to remove the global privileges of the root user in PHPMyAdmin, thinking it was the user for the wordpress database. I did the usual thing of searching for solutions online, and after trying a few different things involving the command line and MySQL Workbench, I came across this Apache Friends forum thread: http://www.apachefriends.org/f/viewtopic.php?f=16&t=46832. The instructions boil down to the following:

  • Open mysql/resetroot.bat in a text editor
  • Add “–datadir=c:/xampp/mysql/data” into the “mysql\bin\mysqld.exe…” line (line 18), after “–no-defaults”. Change the datadir parameter to the path to your data directory – the one shown is the default.
  • Run resetroot.bat – this should reset the MySQL root password and privileges. You can then login to MySQL/PHPMyAdmin using the username root and no password. For security, you should set a password for the MySQL admin user in the XAMPP Security panel: http://localhost/security/

Importing Multiple Users in Rogo

Rogo enables the import of multiple users from a CSV file.

The are a number of required fields, although the order of the fields is flexible. The import does not worry about the case of the field headings, and can accept alternatives for each, as shown below:

  • Student ID: student_id, id
  • Forename(s): first names, forenames
  • Surname: surname, family name
  • Title: title
  • Course Code: course code, course
  • Year of Study: year of study, year of course (1, 2, 3 etc, not actual year, which is held in session)
  • Email Address: email, local email

It is also possible to specify a username for each student. If no username is given, the system will take everything before the @ in the email as the student’s username. A Module and Session (e.g. 2011/12) can also be specified – both are needed for this to be added. Module needs to be the module ID, e.g. OrgBod.

The Student ID does not refer to the student’s username or their database ID (an incremental digit), but a student ID held in a separate table in the database, which does not have to be unique.

Importing a user with the same username as a user that is already in the database will result in that user being overwritten.

There is no way of specifying a password for a user. All passwords are generated randomly and then hashed. This means there is also no way of finding out a user’s password. The only way to define a password for a user is to click “Reset” from the User File, which sends the user an email that they can then click on to set their password. This makes sense from a security point of view, but it is important for us to be able to see user’s passwords (something that is possible in Perception), so that we can log them in if they have forgotten their password, and can test multiple users with defined passwords.

In order to enable defining of passwords in user import, the following was added to import_users.inc:

Line 117:

    //JHM 2012-04-13: Enable passwords to be imported by csv
if (isset($header['password']) && $fields[$header['password']] != "") {	//Check that we have a password header, and the password is not blank for this user
    $password = $fields[$header['password']];
} else {
    $password = PasswordUtils::gen_password();	//No password was provided, so generate a random one
}

Line 137, comment out:

//$password = PasswordUtils::gen_password();	//JHM 2012-04-13: Password now obtained from csv or generated above (line 118)

From looking in the database, this was changing the value of the password for a user, but still did not allow the user to login using the given password. It emerged that this was because the passwords were being doubly encrypted, first in import_users.inc and then in UserUtils::createUser (userutils.class.php line 50). Therefore, I changed lines 137-139 of import_users.inc to:

//$password = PasswordUtils::gen_password();	//JHM 2012-04-13: Password now obtained from csv or generated above (line 118)
//$encpw_password = PasswordUtils::encpw($username, $password);	//JHM 2012-04-13: Password encrypted in createUser below
$encpw_password = $password;	//JHM 2012-04-13: Password encrypted in createUser below, so use plain password

This then worked fine for new user imports, but not for importing users over the top of old users. This required changes to lines 153 onwards in import_users.inc:

$encpw_password = PasswordUtils::encpw($username, $password);	//JHM 2012-04-13: Encrypt password for updating

//JHM 2012-04-13: Added password - 'ssssss' refers to var types, so add extra s to get it to work
$result = $mysqli->prepare("UPDATE users SET yearofstudy=?, title=?, first_names=?, surname=?, grade=?, password=? WHERE username=?");
$result->bind_param('sssssss', $year, $title, $forname, $surname, $course, $encpw_password, $username);

This now seems to work. This issue has been submitted to the Trac system – ticket #701, https://suivarro.nottingham.ac.uk/trac/rogo/ticket/701

We have also made a request for the import to flag up potential problems with header names, and/or enabling users to tell Rogo which headers it should expect, so it can report back if it failed to find any of these. Trac ticket #700 – https://suivarro.nottingham.ac.uk/trac/rogo/ticket/700