1

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...

Improve this question
3
  • 1
    WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. Commented Sep 7, 2018 at 19:22
  • 1
    Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. Commented Sep 7, 2018 at 19:22
  • thank your suggestion, I will for sure, but how to find solution in my existing case? Commented Sep 7, 2018 at 19:24

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for opinion. What do you think if I will generate string using bin2hex(random_bytes(16))? It returns 32 character string like 0C86B1508F236945534705C0661F59A9. Maybe it is not a bad idea for API key?
Anything that's "cryptographically secure" like random_bytes is should do, but keep in mind that there are only 16 hex values instead of 62 letters, so that's a significantly smaller key space and collisions are more likely. You can use base64_encode instead to generate 64-possible values, then substitute / and + with arbitrary letters to avoid having unusual junk in your key.
Thank you! You helped a lot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.