How can I check for duplicate usernames using PHP and MySQL?

Question

I'm just learning PHP and I thought it would be a good idea to learn some MySQL too.So I started working on the code and for some strange reason I keep getting duplicate users which is really really bad.

<?php
$link = mysqli_connect(here i put the data);
if(!$link)
{
    echo "Error: " . mysqli_connect_errno() . PHP_EOL;
    exit;
}
else
{
    if(isset($_POST['user']))
    { echo "User set! "; } 
    else { echo "User not set!"; exit; }
    if(isset($_POST['pass']) && !empty($_POST['pass']))
    { echo "Password set! "; } 
    else { echo "Password not set!"; exit; } 
    $num = mysqli_num_rows(mysqli_query("SELECT * FROM `users` WHERE ( username = "."'".$_POST['user']."' )"));
    if($num > 0)
    { echo "Cannot add duplicate user!"; }
    mysqli_close($link);
}
?>

For some strange reason I don't get the output I should get.I've tried some solutions found here on StackOverflow but they didn't work.

I hate when people say "I'm not that far along..." or "This site will not be public..." or "It's only for school, so security doesn't matter...". If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, "I'll add security later..." or "Security isn't important now...". If you don't have time to do it right the first time, when will you find the time to add it later? — Jay Blanchard
– Jay Blanchard, Commented Jul 7, 2016 at 12:18
@JayBlanchard came back 8 years later to say that I agree with you now more than ever :) — Zvonimir Rudinski
– Zvonimir Rudinski, Commented Sep 11, 2024 at 22:16

Praveen Kumar Purushothaman · Accepted Answer · 2016-07-07 11:27:09Z

2

The first parameter of connectionObject is not given in mysqli_query:

$num = mysqli_num_rows(mysqli_query($link, "SELECT * FROM `users` WHERE ( `username` = '".$_POST['user']."' )"));
//----------------------------------^^^^^^^

Also, your code is vulnerable to SQL Injection. A simple fix would be:

$_POST['user'] = mysqli_real_escape_string($link, $_POST['user']);

answered Jul 7, 2016 at 11:27

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

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

7 Comments

Zvonimir Rudinski Over a year ago

Thanks dude! That was it :D

Jay Blanchard Over a year ago

Even escaping the string is not safe!

Jay Blanchard Over a year ago

Don't be hyperbolic @PraveenKumar ¯\_(ツ)_/¯ You know what I mean!

Praveen Kumar Purushothaman Over a year ago

@JayBlanchard Seriously dude, after seeing that answer, I became hopeless! :P

Jay Blanchard Over a year ago

It's pretty scary! :P

|

Petr · Accepted Answer · 2016-07-07 11:36:42Z

1

This could be a problem of race condition.

Imagine that two users wants to create the same username at the same time. Two processes will execute your script. So both scripts select from database and find out that there is not an user with required username. Then, both insert the username.

Best solution is to create unique index on username column in the database.

ALTER TABLE users ADD unique index username_uix (username);

Then try insert the user and if it fails, you know the username exists ...

answered Jul 7, 2016 at 11:36

Petr

1,19911 silver badges21 bronze badges

1 Comment

Zvonimir Rudinski Over a year ago

Yeah, I'll consider that :)

Phiter · Accepted Answer · 2016-07-07 11:29:47Z

0

mysqli_query must receive two parameters in order to work. In this case, your mysqli_connect.

$num = mysqli_num_rows(mysqli_query($link, "SELECT * FROM `users` WHERE ( username = "."'".$_POST['user']."' )"));

Also, you can be affected by SQL Injection, in this code.

Never add user input directly in your queries without filtering them.

Do that to make your query more readable and safe:

$u_name=mysqli_real_escape_string($link, $_POST['user']);
$num = mysqli_num_rows(mysqli_query($link, "SELECT * FROM `users` WHERE ( username = '$u_name' )"));

edited Jul 7, 2016 at 11:29

answered Jul 7, 2016 at 11:26

Phiter

15k14 gold badges52 silver badges87 bronze badges

6 Comments

Phiter Over a year ago

Yeah I'm not used to adding mysqli_query as parameter instead of variable.

Phiter Over a year ago

I'm so used to OOP MySqli that I forget that some of those needs parameters.

Praveen Kumar Purushothaman Over a year ago

Ah... Gotcha, makes sense... :D

Phiter Over a year ago

Well I answered first, but you fixed his problem faster XD

Praveen Kumar Purushothaman Over a year ago

Yes, but the downvote can be removed on this answer.

|

GROVER. · Accepted Answer · 2016-07-07 11:33:57Z

0

To use mysqli_* extension, you must include your connection inside of the parameters of all queries.

$query = mysqli_query($link, ...); // notice using the "link" variable before calling the query 
$num = mysqli_num_rows($query);

Alternatively, what you could do is create a query() function within your website, like so:

$link = mysqli_connect(...);

function query($sql){
    return mysqli_query($link, $sql);
}

and then call it like so:

query("SELECT * FROM...");

edited Jul 7, 2016 at 11:33

answered Jul 7, 2016 at 11:28

GROVER.

4,4082 gold badges31 silver badges78 bronze badges

Comments

user5051310 · Accepted Answer · 2016-07-07 12:14:42Z

Here's how to write your code using prepared statements and error checking.

Also uses a SELECT COUNT(*)... to find the number of users instead of relying on mysqli_num_rows. That'll return less data from the database and just seems cleaner imo.

<?php
$link = mysqli_connect(here i put the data);

if(!$link) {
  echo "Error: " . mysqli_connect_errno() . PHP_EOL;
  exit;
}
else if(!isset($_POST['user'])) {
  echo "User not set!"; exit;
} 

echo "User set! ";

if(!isset($_POST['pass']) || empty($_POST['pass'])) {
  echo "Password not set!"; exit;
}

echo "Password set! ";

$query = "SELECT COUNT(username)
  FROM users
  WHERE username = ?";

if (!($stmt = $mysqli->prepare($query))) {
  echo "Prepare failed: (" . mysqli_errno($link) . ") " . mysqli_error($link);
  mysqli_close($link);
  exit;
}

$user = $_POST ['user'];
$pass = $_POST ['pass'];

if(!mysqli_stmt_bind_param($stmt, 's',  $user)) {
  echo "Execute failed: (" . mysqli_stmt_errno($stmt) . ") " . mysqli_stmt_error($stmt);
  mysqli_stmt_close($stmt);
  mysqli_close($link);
  exit;
}

if (!mysqli_execute($stmt)) {
    echo "Execute failed: (" . mysqli_stmt_errno($stmt) . ") " . mysqli_stmt_error($stmt);
  mysqli_stmt_close($stmt);
  mysqli_close($link);
  exit;
}

$result = mysqli_stmt_get_result($stmt);
if ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
  $num = $row[0];
  if($num > 0) {
    echo "Cannot add duplicate user!";
  }
}

mysqli_stmt_close($stmt);
mysqli_close($link);

please do suggest fixes to syntax, this was typed from a phone

Collectives™ on Stack Overflow

How can I check for duplicate usernames using PHP and MySQL?

5 Answers 5

7 Comments

1 Comment

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

1 Comment

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related