phpmyadmin Question posted in
The official documentation can be found here.

Generating a Random Alphanumeric String When Registering User Account – Phpmyadmin

I have a requirement to generate a unique alphanumeric string for each user when they register on our WordPress site.

I’ve added the following code into my child theme’s functions.php file, and it works fine – it’s storing the 10 character string as “token_id” in “usermeta” table.

function generate_token_id($length = 10) {
    $characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $result = '';
    for ($i = 0; $i < $length; ++$i) {
        $result .= $characters[mt_rand(0, strlen($characters) - 1)];
        }
    return $result;
}

add_action('user_register', 'save_token_id', 10, 2);

function save_token_id($user_id) {
    $meta_key = "token_id";
    $key = generate_token_id();
    update_user_meta($user_id, $meta_key, $key);
}

The problem I have, is the second function does not check whether the generated string is already present in the database, and could potentially cause a duplication.

I’ve updated the function to include a do / while loop below to validate, however this is really stretching my knowledge, and just need some experienced eyes to tell me if I have the do / while / sql select routine correct.

function generate_token_id($length = 10) {
    $characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $result = '';
    for ($i = 0; $i < $length; ++$i) {
        $result .= $characters[mt_rand(0, strlen($characters) - 1)];
        }
    return $result;
}

add_action('user_register', 'save_token_id', 10, 2);

function save_token_id($user_id) {
    $meta_key = "token_id";
    do {
        $key = generate_token_id();
        $keycheck = $wpdb->get_results("select count(*) from `usermeta` where `meta_value` like '"$key"'");
    } while ($keycheck > 0);
    update_user_meta($user_id, $meta_key, $key);
}

UPDATE: So I’ve declared “global $wpdb;” inside the function, and I’ve changed the SQL syntax, so it now looks like this:

function save_token_id($user_id) {
    global $wpdb;
    $meta_key = "token_id";
    do {
        $key = generate_token_id();
        $keycheck = $wpdb->get_results("select count(*) from $wpdb->usermeta where meta_value = " . $key);
    } while ($keycheck > 0);
    update_user_meta($user_id, $meta_key, $key);
}

However the PHP DEBUG log is full of SQL errors.

WordPress database error: [Unknown column '74TTW1PIPP' in 'where clause']
select count(*) from wp_usermeta where meta_value = 74TTW1PIPP

WordPress database error: [Unknown column 'CST10WY8EQ' in 'where clause']
select count(*) from wp_usermeta where meta_value = CST10WY8EQ

WordPress database error: [Unknown column 'M3GSGAHD5J' in 'where clause']
select count(*) from wp_usermeta where meta_value = M3GSGAHD5J

I’ve validated the SQL query in phpMyAdmin, however I can’t get it correct in the PHP function. meta_value column is clearly declared, why is it using the $key variable as the column?

Tags:

2

Answers


  1. 0

    Instead of $wpdb->get_results use of $wpdb->get_var()

     $keycheck = $wpdb->get_var("select count(*) from `usermeta` where `meta_value` like '"$key"'");
}
    Login or Signup to reply.
  2. 0

    I don’t think so you need to extra check for the existing generated duplicate key in the database if you use any function that can generate cryptographically secure hashed string. For example, you may consider following function by passing your $user_id to the function to get a unique hashed key.

    <?php
/**
 * Generate cryptographically secure hashed string
 *
 * @param int|string $user_id
 * @param int $length
 * @param string $hash Any cryptographically secure hash
 * For example: 'gost-crypto', 'whirlpool', 'ripemd128' and for more
 * checkout https://www.php.net/manual/en/function.hash-algos.php
 *
 * @param int $iterations The number of internal iterations to perform for the derivation
 * @return string
 */
function generate_unique_id($user_id, $length = 20, $hash = 'ripemd128', $iterations = 10000) {
    // Generate a random IV
    if (version_compare(PHP_VERSION, '7.0.0', '<')) {
        $salt = openssl_random_pseudo_bytes(16);
    } else {
        $salt = random_bytes(16);
    }

    return hash_pbkdf2($hash, $user_id, $salt, $iterations, $length);
}

// Assuming 1 is the user ID
echo generate_unique_id(1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search