Prevent duplicate data PHP Mysql

Question

I have a Registration Form, but there are a few problems. This is my first registration form. Where the data is needed for data collection needs.

But when someone registers, a lot of duplicate data is in PHPMyAdmin. I've tried various ways, but you know, I'm just a student. I want to prevent duplicate email data.

<?php
$link = mysqli_connect("localhost", "xxxx", "", "db");

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Escape user inputs for security
$nama = mysqli_real_escape_string($link, $_REQUEST['nama']);
$nohp = mysqli_real_escape_string($link, $_REQUEST['nohp']);
$email = mysqli_real_escape_string($link, $_REQUEST['email']);
$instagram = mysqli_real_escape_string($link, $_REQUEST['instagram']);

$sql = "INSERT INTO event (nama, nohp, email, instagram) VALUES ('$nama', '$nohp', '$email', '$instagram')";
if(mysqli_query($link, $sql)){
    echo "<p class='text-center'>Pendaftaran Berhasil. Akan kembali ke halaman utama dalam 5 detik.<br/>
    Atau klik <a href='index.php'>HOME</a></p>";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// close connection
mysqli_close($link);
?>

What code do I need to add in it, so that someone who has registered their email will not be able to register again?

Sorry for my bad english

If you want to prevent duplicate email field , set it as primary key and catch exception when insert then if there is duplicate it will throw error and display user to choose unique email — hs-dev2 MR, Jul 26 '19 at 09:09
You should use prepared statements instead of escaping the input. — Dharman, Jul 26 '19 at 09:11
Possible duplicate of [MySQL - Make an existing Field Unique](https://stackoverflow.com/questions/5038040/mysql-make-an-existing-field-unique) — Dharman, Jul 26 '19 at 09:13
Watch out when `example@email.com', ''); DELETE FROM event; --` signs up! — Tim, Jul 26 '19 at 09:34
@Tim OP is escaping the input, so unless there's some obscure charset configuration, that value becomes `example@email.com\',\'\'); DELETE FROM event;--`. Better to use a prepared statement, yes! But escaping works in *nearly all* cases (but its not 100% foolproof, and it makes for messy code). — Qirel, Jul 26 '19 at 09:36
@Qirel *Nearly all* isn't good enough when you have lost your database. Parameterised queries (or prepared statements) always work. — Tim, Jul 26 '19 at 09:39
Prepared statement is always better, sure - but even if the very, extremely obscure case of escaping fails (this only can happen if there is some "bugs" with charsets, without going into too much detail about that), the `mysql_` and `mysqli_` APIs can't run more than one query at a time. The query of `INSERT INTO foo (..) VALUES(..); DROP TABLE foo;` is invalid, because you attempt to execute two queries. In the end, we agree: always use prepared statements. But I'm just saying that its not really possible with that kind of drop-table injection if you escape. ;-) — Qirel, Jul 26 '19 at 09:41

score 3 · Answer 1 · answered Jul 26 '19 at 09:13

Add a unique constraint on the column you wish to be unique, then insert the data without any regard for duplication - MySQL will return an error if the value was duplicate, so all you need to do is catch the error.

First, make the field on that table a unique value (you can not have duplicate values on the table when doing this - if you have duplicate values in the event table, you must fix that first).

ALTER TABLE event
ADD UNIQUE (email);

Then we simply try to insert the data (using a prepared statement instead of escaping), and catch the error - and check for the error-code which belongs to unique constraint errors.

// Enable MySQLi exceptions 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // Connect to the database
    $link = new mysqli("localhost", "xxxx", "", "db");

    // Attempt to insert the data 
    $sql = "INSERT INTO event (nama, nohp, email, instagram) VALUES (?, ?, ?, ?)");
    $stmt = $link->prepare($sql);
    $stmt->bind_param("ssss", $_REQUEST['nama'], $_REQUEST['nohp'], $_REQUEST['email'], $_REQUEST['instagram']);
    $stmt->execute();
    $stmt->close();
} catch (Exception $e) {
    if ($e->getCode() == 1062) { // 1062 = Unique constraint error code
        // Unique field error, handle it
        // If you only have unique on the email, example: 
        // echo 'The email already exists';
    } else {
        // Handle the error, something else went wrong
        error_log($e->getMessage());
        echo "An unexpected error occurred";
    }
}

DonCallisto · Answer 2 · 2019-07-26T09:28:31.563

1

Put a unique constraint on email field directly onto table column. This will save you from duplicates (either inserted by your software or in other ways).

After that, you can check explictly via PHP script if the email is already there (but this will cost you an extra query and so is a no-go; moreover as Qirel correctly pointed out, this will expose you to potential concurrency issues) or try to insert and check insertion result (correct and safer way).

edited Jul 26 '19 at 09:28

answered Jul 26 '19 at 09:09

DonCallisto

27,046
8
62
88

Why would you perform an extra query when you already know that you have a duplicate entry from that exception? – Kai Neuwerth Jul 26 '19 at 09:18
1

@KaiNeuwerth I mean, before inserting. I'm not encouraging this solution, just exposing an alternative (worst) solution. As he's a student I think the best is to explain all solution (even the bad ones), warn about them and propose an alternative solution. – DonCallisto Jul 26 '19 at 09:21
If you do a `SELECT` query first to determine if the value exist or not, you risk getting race-conditions. It's extremely unlikely to ever happen, particularly for smaller applications (so for 99% of all cases, if not more, it'll do just fine), but it *can* happen. Better to slap a unique constraint on it, insert it without checking first and catch the error if the constraint fails. – Qirel Jul 26 '19 at 09:26
2

Oh I know, I just wanted to explain a bit more *why* its a bad idea ;-) – Qirel Jul 26 '19 at 09:34
@Qirel Yes, I was lacking this info. Just edited my post ;) – DonCallisto Jul 26 '19 at 09:46

score 1 · Accepted Answer · answered Jul 26 '19 at 09:15

You can add a UNIQUE constraint to the email field. This will be done by running the following query:

ALTER TABLE `event` ADD UNIQUE (email);

After that, when you want to insert a new row with an email that already exists in your table, you'll get an error like this: #1062 - Duplicate entry ...

You can catch that exception and react on it in your PHP code like this:

<?php
// perform INSERT query...
if (mysqli_errno($link) == 1062) {
    print 'An error occured';
}

Thank a lot. This solves my problem. Thank you so much :) – Hafid Maulana Jul 26 '19 at 11:43 — Hafid Maulana, Jul 26 '19 at 11:43

score 1 · Answer 4 · answered Jul 26 '19 at 09:31

You can give emailID unique constraint, whenever the user submits form with a emailID that already exists in the table, MYSQL will throw an exception which you will need to catch and display the error message. Reference :https://www.w3schools.com/php/php_exception.asp

Prevent duplicate data PHP Mysql

4 Answers4