How to prevent duplicate Usernames in php/mysql

Question

(This is my first try with php)

I have a very basic register page. http://graves-incorporated.com/test_sites/member_test/register/register.php

* I just remembered PHP code doesn't show up in the source so here it is:

enter code here
<?php
include('connection.php');

if(isset($_POST['form'])){
    if(empty($_POST['username']) || empty($_POST['password']) || empty($_POST['conf_pass']) || empty($_POST['email'])){
        echo '<b>Please fill out all fields.</b>';

    }elseif($_POST['password'] != $_POST['conf_pass']){
        echo '<b>Your Passwords do not match.</b>';
    }else{
        $url = 'http://graves-incorporated.com/test_sites/plantation_park_2012/';
        echo '<META HTTP-EQUIV=Refresh CONTENT="2; URL='.$url.'">';
        echo '<b>Congrats, You are now Registered.</b>';
        mysql_query("INSERT INTO users VALUES(NULL, '$_POST[username]', '$_POST[password]', '$_POST[email]')");     
    }
}
?>

I want to make sure I don't get duplicate users and/or email addresses in the database. I set up a Unique Key in MySQL for Username and Email, which prevents it, but the user on the actual form doesn't know that, it still tells them "Congrats, you are signed up" or whatever it says... haha

So what can I add to the php code (and where in the code) that would prevent this?

Thanks for helping this major noob, Dan Graves

You need to check if `insert` query failed or not. And if it does - see the message using `mysql_error()` — zerkms, Mar 07 '12 at 09:54
could you tell me exactly how to enter that? php is literally a foreign language to me. I am learning it as fast as I can but still am not fully grasping it haha — Dan Graves, Mar 07 '12 at 09:56
oh really? How do I prevent that? I don't need high level security for this site — Dan Graves, Mar 07 '12 at 10:01
This is very basic security - google and you'll find tonnes of tutorials. — James, Mar 07 '12 at 10:07
This SQL Injection attack stuff is way over my head. I am baby stepping with php, I don't think I have the know how to implement defense against these attacks — Dan Graves, Mar 07 '12 at 10:37
http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php Here's a decent tutorial, it's not hard to defend against. — James, Mar 07 '12 at 11:21
I don't know where I am suppose to paste the " mysql_real_escape_string() " into my php code — Dan Graves, Mar 08 '12 at 04:23

mgraph · Accepted Answer · 2012-03-07T10:10:01.297

<?php
include('connection.php');

if(isset($_POST['form'])){
    if(empty($_POST['username']) || empty($_POST['password']) || empty($_POST['conf_pass']) || empty($_POST['email'])){
        echo '<b>Please fill out all fields.</b>';

    }elseif($_POST['password'] != $_POST['conf_pass']){
        echo '<b>Your Passwords do not match.</b>';
    }else{

        $dup = mysql_query("SELECT username FROM users WHERE username='".$_POST['username']."'");
        if(mysql_num_rows($dup) >0){
            echo '<b>username Already Used.</b>';
        }
        else{
            $url = 'http://graves-incorporated.com/test_sites/plantation_park_2012/';
            echo '<META HTTP-EQUIV=Refresh CONTENT="2; URL='.$url.'">';

            $sql = mysql_query("INSERT INTO users VALUES(NULL, '$_POST[username]', '$_POST[password]', '$_POST[email]')");     
            if($sql){
                 echo '<b>Congrats, You are now Registered.</b>';
            }
            else{
                echo '<b>Error Registeration.</b>';
            }
        }
    }
}
?>

Your code works! One tiny problem though, the way I have it set up (probably not the best way) it redirects to another website after completion. Only thing is if you enter a duplicate it still forwards. if that is something that is not possible to do I can just disable the auto forward — Dan Graves, Mar 07 '12 at 10:08

score 1 · Answer 2 · answered Mar 07 '12 at 09:55

1

Which DB driver are you using? Are you looking for DB errors? Some of them just happily sail past any DB errors and you have to call a function specifically to check for DB errors. your DB should throw an error on duplicate data, and you can pick that error up and alert the user.

answered Mar 07 '12 at 09:55

James

3,265
4
22
28

I am using MySql 5. Is that what you mean? How would I set that function – Dan Graves Mar 07 '12 at 10:00
1

No, the driver is what connects to the DB. mysql, mysqli or PDO are all PHP drivers for MySQL. I see from your code which driver you are using. Look up mysql_error() on php.net and use that to check for an error. – James Mar 07 '12 at 10:02

jdalangin · Answer 3 · 2012-03-07T10:40:45.007

1

From a user interface stand point, I think it would be great if they have the ability to check via ajax request if the username exists in the db or not, sort of like an availability checker.

It would also save you cpu time if you isolate the process of checking username availability rather than processing everything just to find out that the username is not available.

To do so via jquery, i would recommend:

$('#checkAvailabilityButton').click(function() {
    var usernameVal = $('#usernameField').val(); // assuming this is a input text field
    $.post('checkusername.php', {username=usernameVal}, function(data) {
        alert('data');
    });
});

And on your php end run a query on your database that would look like:

"SELECT Username FROM users WHERE Username = 'POSTVALUE'"

if (mysql_num_rows > 0) {
    echo "Username is taken"
}

Also be very very careful not to allow unsanitized post variables into your database to prevent SQL injections.

Lastly, try to use a better PHP database extensioin like MySQLi, most STMT requests automatically sanitize variables via mysqli_prepare.

Good luck!

edited Mar 07 '12 at 10:40

answered Mar 07 '12 at 10:21

jdalangin

197
5

I am not sure what "unsanitized post variables" are but I am trying to read up on these Injection Attacks but its not really making any sense to me, haha I am the definition of a php/MySql noob – Dan Graves Mar 07 '12 at 10:55
Let's take your insert example. For example, SELECT username FROM users WHERE username='".$_POST['username'] but post will be x'; DROP TABLE 'username --- BAM, the whole table gets wiped out. – jdalangin Mar 07 '12 at 11:04
oh man, yeah I better look into fixing that some how. This register page is only going to emailed to a select number of people at a condo complex, there will be no link to it anywhere. Do you think I still need to worry about it? – Dan Graves Mar 08 '12 at 01:37

score 0 · Answer 4 · answered Mar 07 '12 at 09:57

You can use ajax query before submit to check/prevent is already exists Also in back-end after you do INSERT INTO... you can do SELECT FOUND_ROWS() to check if it's actually equals 1, it means one row inserted. Then redirect user to error page with information what is not correct. For better user experience, I recommend first option, with JQuery it will not take a lot of time to implement.

How to prevent duplicate Usernames in php/mysql

4 Answers4

Linked