Safe, performant generation of a unique Drupal username

There are examples out there for generating a unique Drupal username. The usual technique is to continue incrementing a numeric suffix until an unused name is found. There's also a project to automatically generate usernames for new users. All of this makes sense and works, but compared to the existing solutions, I wanted one that focussed on encapsulation and stability; by which I mean it should:

  1. provide simple string-to-string conversion: initial guess in; unique username out. That way it can be used in many different places, not reliant on specific workflows, or modules being in place.
  2. assume the initial guess was itself a valid username, because different sites have different rules or conventions about that; so don't process the username except as required below.
  3. ensure safety at the database level by respecting the limits on username length there.
  4. ensure safety at the code level by not trying for uniqueness indefinitely, and using exceptions to notify other code of unexpected circumstances.
  5. permit parametrization of both the delimiter between the suffix, and the maximum value of the suffix before throwing an exception.
  6. perform well at the SQL/PDO layer (so minimizing the number of distinct queries.)

Here's my version, then, heavily commented and indented, hopefully to satisfy coding standards:

 * Helper function: create a unique username from an initial guess.
function _mymodule_unique_username($initial, $delimiter = '.', $max_iter = 99) {
  // In database, is varchar(60) and we add at least
  // a delimiter and an N-digit number.
  $next_username = $username_root = trim(substr(
    60 - ceil(log10($max_iter)) - strlen($delimiter)
  // Get all usernames of the form :name or LIKE :name:delimiter%
  // in a single query to improve performance (especially if
  // something goes wrong and we get many duplicates over time.)
  $sql_result = db_query(
    "SELECT name FROM {users} WHERE name = :name OR name LIKE :likename",
    array(":name" => $username_root, ":likename" => "$username_root$delimiter%")
  $existing_names = $sql_result->fetchAllAssoc("name");
  // Loop to check uniqueness of trial username(s).
  $i = 0;
  do {
    $username = $next_username;
    $found = isset($existing_names[$username]);
    // Set up the next username in advance.
    $next_username = $username_root . $delimiter . ++$i;
    // Loop until either nothing found or we've maxed out our iterations.
  } while ($found && ($i < $max_iter));
  // If we've maxed out, throw an exception for rest of code to handle.
  if ($i == $max_iter) {
    throw new Exception("Unique username guesses exhausted for '$initial'!");
  // Otherwise, return the username.
  return $username;

You can call it as follows:

$new_username = _mymodule_unique_username("barry.perkins");

A few notes:

  • Each call to the function makes only one SQL query, because a repeated query in a PHP loop performs badly. Given the default maximum iterations is 99, if something goes wrong then that could be an issue.
  • I don't put any limit on the SQL query, because the sort order of varchar is not and cannot be made numeric. That means that if I limited it to a certain number of records, I wouldn't be guaranteed to get them back in the same order as my PHP iteration. The function might then return a name that wasn't actually unique.
  • It's not thread safe: it doesn't "reserve" the username at all in between the function call and when you create it.
  • I've also reformatted this in the browser since testing it, so you use it at your own risk!

Feedback much appreciated, though!


You have a typo: $next_usename should be $next_username

Good spot: thanks!

The function does not take case-insensitive collations into account.
E.g. if submitted name is "FOO", the query will (correctly) find user named "foo".
But when checked using isset(), which IS case sensitive, the "foo" user is not seen, and the function happily returns FOO as unique. This can cause nasty, fatal errors when Drupal goes to save the record.
To fix:
Change the select statement to: "SELECT LOWER(name) as name ..."
Change the found logic to: $found = isset($existing_names[strtolower($username)]);