How to prevent duplicate username and email data?

Question

I have been making a login/register system and I am drawing close to finishing my register portion of the code. The only problem I am running into is how to make it so that users cannot register with duplicated usernames and e-mail. I want it to work so that my database won't accept the information, and it will tell the user about the error.

<!-- daftar -->
    <section id="daftar" style="background: #333;">
        <div class="container">
            <div class="row text-center">
                <div class="col-lg-10 col-lg-offset-1">
                    <h2 style="font-family:Font Juice; font-size:50px; color:#ccc; padding: 20px;">Daftar Baru</h2>
                    <hr class="small">
                    <div class="row">
                            <div class="col-lg-8 col-lg-offset-2" >
                                <form name="contactForm" method="post" id="contactForm" action="<?php $_SERVER['PHP_SELF'];?>">
                                <div class="row control-group">
                                    <div class="form-group col-xs-12 floating-label-form-group controls">
                                        <input type="text" class="form-control" placeholder="Nama Anda" name="t1" id="t1" required data-validation-required-message="Input nama anda.">
                                        <p class="help-block text-danger"></p>
                                    </div>
                                </div>
                                <div class="row control-group">
                                    <div class="form-group col-xs-12 floating-label-form-group controls">
                                        <input type="email" class="form-control" placeholder="Email" name="t2" id="t2" required data-validation-required-message="Input alamat email.">
                                        <p class="help-block text-danger"></p>
                                    </div>
                                </div>
                                <div class="row control-group">
                                    <div class="form-group col-xs-12 floating-label-form-group controls">
                                        <input type="text" class="form-control" placeholder="Username" name="t3" id="t3" required data-validation-required-message="Input username untuk login">
                                        <p class="help-block text-danger"></p>
                                    </div>
                                </div>
                                <div class="row control-group">
                                    <div class="form-group col-xs-12 floating-label-form-group controls">
                                        <input type="password" class="form-control" placeholder="Password" name="t4" id="t4" required data-validation-required-message="Input password untuk login">
                                        <p class="help-block text-danger"></p>
                                    </div>
                                </div>
                                
                                <div class="row control-group">
                                    <div class="form-group col-xs-12 floating-label-form-group controls">
                                        <input type="password" class="form-control" placeholder="Konfirmasi Password" name="t5" id="t5" required data-validation-required-message="Konfirmasi Password">
                                        <p class="help-block text-danger"></p>
                                    </div>
                                </div>

                                <script type="text/javascript">
                                 window.onload = function () {
                                  document.getElementById("t4").onchange = validatePassword;
                                  document.getElementById("t5").onchange = validatePassword;
                                 }
                                 function validatePassword(){
                                  var t4=document.getElementById("t4").value;
                                  var t5=document.getElementById("t5").value;
                                  if(t4!=t5)
                                   document.getElementById("t5").setCustomValidity("Passwords tidak Sama, Coba Lagi");
                                  else
                                  document.getElementById("t5").setCustomValidity('');
                                 }
                                </script>
                                
                                <div class="row">
                                    <div class="form-group col-xs-12 floating-label-form-group controls">
                                        <label class="checkbox-inline">
                                        <h5 style="color:#fff;  font-family: BebasNeueRegular; font-size: 24px">Dengan menekan tombol " Daftar "<br> Saya Setuju dengan <a href="#">Kebijakan dan Ketentuan</a> yang berlaku.</h5>
                                        </label>
                                </div>
                                
                                <div class="row">
                                    <div class="form-group col-xs-12">
                                        <button type="submit" class="btn btn-primary btn-lg" style="font-family:'BebasNeueRegular'; font-size:20px;  text-shadow: 0 2px 2px rgba(0, 0, 0, 0.3);" name="btnSimpan" id="btnSimpan"> <span class="glyphicon glyphicon-list"></span> &nbsp;&nbsp;Daftar&nbsp;&nbsp;</button>
                                        <br><br><br>
                                    </div>
                                </div>
                                <div id="success"></div>
                            </form>
                            
                            <?php
                                if(isset($_POST['btnSimpan'])){
                                    $q = $op->save("tb_user","nama_user,email_user,username,password","'".$_POST['t1']."','".$_POST['t2']."','".$_POST['t3']."','".md5($_POST['t4'])."'");
                                    if($q){
                                        echo "<div class='alert alert-success'>
                                            <a href='#' class='close' data-dismiss='alert' aria-label='close'>&times;</a>
                                            <strong>Success!</strong> Data berhasil disimpan. Silahkan klik tombol Mulai Bermain.</div>";
                                    }else{
                                        echo "<div class='alert alert-danger' >
                                            <a href='#' class='close' data-dismiss='alert' aria-label='close'>&times;</a>
                                            <strong>Error !</strong> Data gagal disimpan</div>"; 
                                    }
                                }
                            ?>
                        </div>
                    </div>

You can make the username and/or email columns unique in the table you are saving the data. — Qirel, Sep 05 '16 at 18:19
Your code is vulnerable to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). You should bind variables to a prepared statement, which is possible in both [PDO](https://secure.php.net/manual/en/pdostatement.bindparam.php) and [`mysqli`](https://secure.php.net/manual/en/mysqli-stmt.bind-param.php). — ChrisGPT was on strike, Sep 05 '16 at 18:21
See: [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/q/60174/660921). — Martin Tournoij, Sep 05 '16 at 18:33
Please dont __roll your own__ password hashing. PHP provides [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) please use them. And here are some [good ideas about passwords](https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet) If you are using a PHP version prior to 5.5 [there is a compatibility pack available here](https://github.com/ircmaxell/password_compat) — RiggsFolly, Sep 05 '16 at 18:39

score 1 · Accepted Answer · answered Sep 05 '16 at 18:21

1

Create a UNIQUE CONSTRAINT on email_user and username column in your tb_user table; which will throw error if you try to insert duplicates.

ALTER TABLE tb_user ADD CONSTRAINT constr1 UNIQUE (email_user);

answered Sep 05 '16 at 18:21

Rahul

76,197
13
71
125

2

Might be nice to show how you would process the error – RiggsFolly Sep 05 '16 at 18:37

score -1 · Answer 2 · answered Sep 05 '16 at 18:21

-1

You can make a SELECT query right before $q = $op->save() with the name and password combination in the post.

If it returns any results, return a message and prevent saving.

answered Sep 05 '16 at 18:21

iliaz

395
2
10

1

This doesn't solve the problem. It makes it less likely, but it introduces a [race condition](https://en.wikipedia.org/wiki/Race_condition). If two users simultaneously try to register the same data both might succeed. The correct answer is to use a database constraint like Qirel and Rahul have both suggested. – ChrisGPT was on strike Sep 05 '16 at 18:23
Why _wouldn't_ you want to "cover 100%"? The proper solution is just as easy as yours, but it actually _works_. – ChrisGPT was on strike Sep 05 '16 at 18:42
At this example, having two users entering the exact same username and password at the exact split second, though possible, maybe on a theoretical level, irl is highly improbable. That's what I am trying to say, and of course the other solution is more safe. As you can also see mine and Rahul answers were racing (pun intended) to be the first answers :-) – iliaz Sep 05 '16 at 19:11
This kind of laziness can easily lead to serious security issues. I'm glad you don't work for me... – ChrisGPT was on strike Sep 05 '16 at 19:50
If you read what I read and not be lazy, you would see that we agree. I am also glad on this case. – iliaz Sep 05 '16 at 19:52

How to prevent duplicate username and email data?

2 Answers2