CODEWITHSUNDEEP
phpmysql
Grufas
Grufas

Reputation: 1450

PHP check database table, if value exists, run loop to generate unique value

I have function which checks if value exists, in this case it is API key. What I am trying to achieve is, before creating new api key for each account registration, I want to loop my function to generate new key if existing already in database. Key is simple string generated using:

$apiKey = bin2hex(random_bytes(16));

My function:

function apiCheckKey($apiKey) {
    global $conn;
    $sql = "SELECT * FROM `api` WHERE `key` = '".$apiKey."'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result)) {
        return true;
    } else {
        return false;
    }
}

My check:

if(!apiCheckKey($apiKey)) {
     // loop
}

How can I run loop efficiently to generate new key and eliminate duplicates? Please keep in mind, database will contain 100 000+ records...

Upvotes: 1

Views: 277

Answers (1)

tadman
tadman

Reputation: 211700

There's a few things to keep in mind when doing this:

  1. Ensure you have a UNIQUE constraint on this column so it's impossible to add duplicate values. You can't rely on a SELECT COUNT(*) FROM x WHERE key=? test before inserting as that's vulnerable to race conditions.
  2. Generate an API key that's sufficiently random that collisions are unlikely. A >=20 character random string using all letters, both upper and lower case, plus numbers will have 704,423,425,546,998,022,968,330,264,616,370,176 possible forms so a collision is astronomically unlikely. If you have shorter keys collisions become a lot more probable due to effects like the pigeonhole principle and the birthday paradox.
  3. In the unlikely event a collision does occur, make your code generate a new key and retry the insert. A UNIQUE constraint violation is a very specific MySQL error code you can handle. Check your error value if/when the INSERT fails and dispatch accordingly.

Test your code by generating a few million keys to be sure it's operating properly.

Upvotes: 1

Related Questions