php - Generate unique key and save in the database not working

Solution:

You are not callinggenerateKey function. Call this function and store return value to$randStr before inserting in database.

Note: please see SQL injection prevention for same.

$randStr = generateKey($conn);

// Your insert statement
Copy code

Solution:

While the accepted answer is correct as far as it goes, there is much here that can be improved. Reading all the existing keys and checking them one by one is intensive, and will get slower as the number of keys increases. It also opens a window for a race condition, where two requests are competing fro resources.

Applying a unique index to thekeystring column allows us to perform an atomic 'test and set' with MySQL checking the key and inserting it in one operation.

We can also switch to prepared queries to eliminate the risk of SQL injection.

So, apply a UNIQUE index to the keystring column. This modification to the table only needs to be done once.

ALTER TABLE `users` ADD UNIQUE INDEX `keystring_UNIQUE` (`keystring` ASC) VISIBLE;
Copy code

Then the new code looks like this:

function generateKey() {
    $keyLength = 8;
    $str = "0123456789abcdefghijklmnopqrstuvwxyz";
    $randStr = substr(str_shuffle($str), 0, $keyLength);
    return $randStr;
}

$recipient = $_POST['emailFor'];
$sender = $_POST['senderEmail'];
$message = $_POST['message'];

// This will be required somewhere
$conn = mysqli_connect("xxxx", "xxxx", "xxxx", "xxxx");

// Prepare the query
$stmt = mysqli_prepare($conn, "INSERT INTO users (keystring, email) VALUES(?, ?);");

// If the key generated already exists the loop will try again.
// Limit the collisions before we give up
$collisionLimit = 10;

do {
    $randstr = generateKey();
    mysqli_stmt_bind_param($stmt, 'ss', $randstr, $sender);
    $result = mysqli_stmt_execute($stmt);


    // check the result. If it's TRUE all's well and we continue. 
    // If we have an error throw an Exc eption if it's not a duplicate key error

    if ($result === false) {
        if (mysqli_stmt_errno($stmt) != 1062) {
            throw new Exception(mysqli_stmt_error($stmt));
        }

        // If we have a collision, check the limit and throw an Exception if necessary
        if (--$collisionLimit) {
            throw new Exception("Unable to insert key - too many collisions");
        }
    }

} while ($result===false);
Copy code