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'm not missing it. I just don't feel like sharing my login info for MySQL with you. As for SQLi I'll tackle that later :D — Zvonimir Rudinski, Jul 07 '16 at 11:28
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, Jul 07 '16 at 12:18

score 3 · Accepted Answer · answered Jul 07 '16 at 11:27

3

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 07 '16 at 11:27

Praveen Kumar Purushothaman

164,888
24
203
252

1

Thanks dude! That was it :D – Zvonimir Rudinski Jul 07 '16 at 11:30
@ZvonimirRudinski Thanks buddy! `:)` – Praveen Kumar Purushothaman Jul 07 '16 at 11:32
1

Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Jul 07 '16 at 12:19
@JayBlanchard Having a server that has an open 80 port is also not safe! `:P` – Praveen Kumar Purushothaman Jul 07 '16 at 12:21
1

Don't be hyperbolic @PraveenKumar ¯\\_(ツ)_/¯ You know what I mean! – Jay Blanchard Jul 07 '16 at 12:22
1

@JayBlanchard Seriously dude, after seeing that answer, I became hopeless! `:P` – Praveen Kumar Purushothaman Jul 07 '16 at 12:23
1

It's pretty scary! :P – Jay Blanchard Jul 07 '16 at 12:24

Phiter · Answer 2 · 2016-07-07T11:29:47.390

1

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 07 '16 at 11:29

answered Jul 07 '16 at 11:26

Phiter

14,570
14
50
84

Yeah I'm not used to adding `mysqli_query` as parameter instead of variable. – Phiter Jul 07 '16 at 11:29
I'm so used to OOP MySqli that I forget that some of those needs parameters. – Phiter Jul 07 '16 at 11:30
Ah... Gotcha, makes sense... `:D` – Praveen Kumar Purushothaman Jul 07 '16 at 11:30
Well I answered first, but you fixed his problem faster XD – Phiter Jul 07 '16 at 11:31
Yes, but the downvote can be removed on this answer. – Praveen Kumar Purushothaman Jul 07 '16 at 11:32
`mysqli_real_escape_string` is not totally safe. – apokryfos Jul 07 '16 at 11:45

GROVER. · Answer 3 · 2016-07-07T11:33:57.617

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...");

score 1 · Answer 4 · answered Jul 07 '16 at 11:36

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 ...

Yeah, I'll consider that :) – Zvonimir Rudinski Jul 07 '16 at 11:42 — Zvonimir Rudinski, Jul 07 '16 at 11:42

score 0 · Answer 5 · answered Jul 07 '16 at 12:14

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

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

5 Answers5