mysqli check if 2 value exist

[aHMAD_SQaLli]

Hello

I'm trying to check if 2 values exist in the database using php, Google didn't help...

 

I need something like this :

if($stmt = mysqli_prepare($db_connect,'QUERY TO CHECK IF USERNAME AND EMAIL EXIST')){
mysqli_stmt_bind_param($stmt, "ss", $user,$email);
mysqli_stmt_execute($stmt);
/*
    if username exist
        echo username exist
    if email exist
        echo email exist
*/
}
else{/*error*/}

thanks !

[Jacques1]

What is the purpose of this check? Is it the usual uniqueness check for a user registration script? In that case, there are much better alternatives.

 

Also, you generally must not expose the e-mail addresses of your users to the public. Whether a particular address is registered at your site is none of anyone's business. Instead, you would send out a mail telling the user that they already have an account.

[aHMAD_SQaLli]

What is the purpose of this check? Is it the usual uniqueness check for a user registration script? In that case, there are much better alternatives.

 

Also, you generally must not expose the e-mail addresses of your users to the public. Whether a particular address is registered at your site is none of anyone's business. Instead, you would send out a mail telling the user that they already have an account.

 

I need it for a registration script

[Jacques1]

In that case, you'll need a very different approach.

 

When you first check the database and then insert a new row, there's a small timeframe where a different PHP process may also insert a new row and invalidate the result of your check. Your application won't see that and just keeping going. In the worst case, you now have two rows with the same data despite your checks.

 

This situation is unlikely to happen by accident, but the bug can actively be exploited by anybody.

 

A much more reliable solution is to let the database do the check. Add a UNIQUE constraint to the columns and then simply try to insert the new row. If that fails due to a constraint violation, you know the data is not unique. Otherwise everything is fine.

<?php

// database settings
const DB_HOST = '...';
const DB_USER = '...';
const DB_PASSWORD = '...';
const DB_NAME = '...';
const DB_CHARSET = 'UTF8';

// MySQL error codes
const MYSQL_ER_DUP_ENTRY = 1062;



// Enable exceptions for MySQLi.
$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

$database_connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$database_connection->set_charset(DB_CHARSET);



$test_name = 'foo';
$test_email_address = 'bar@example.com';

$user_registration_stmt = $database_connection->prepare('
    INSERT INTO users
    SET
      public_name = ?,
      email_address = ?
');

$user_registration_stmt->bind_param('ss', $test_name, $test_email_address);

// Try to insert row.
$user_registration_errors = [];
try
{
    $user_registration_stmt->execute();
}
catch (mysqli_sql_exception $user_registration_exception)
{
    // Was the error caused by a duplicate entry?
    if ($user_registration_exception->getCode() == MYSQL_ER_DUP_ENTRY)
    {
        $user_registration_errors[] = 'The username or e-mail address is already in use.';
    }
    else
    {
        // It's some other problem, pass the exception on.
        throw $user_registration_exception;
    }
}

if ($user_registration_errors)
{
    foreach ($user_registration_errors as $error)
    {
        echo htmlspecialchars($error, ENT_QUOTES | ENT_SUBSTITUTE | ENT_HTML5, 'UTF-8').'<br>';
    }
}
else
{
    echo 'Registration successful!';
}

There's still a problem left: Usually, the e-mail addresses of users are private, so they must not be exposed, neither directly nor indirectly. Right now, anybody can check if an address is already in your database simply by trying to register with it.

 

Is that different for your site? Do you have an agreement with your users that all e-mail addresses are public?

[aHMAD_SQaLli]

In that case, you'll need a very different approach.

 

When you first check the database and then insert a new row, there's a small timeframe where a different PHP process may also insert a new row and invalidate the result of your check. Your application won't see that and just keeping going. In the worst case, you now have two rows with the same data despite your checks.

 

This situation is unlikely to happen by accident, but the bug can actively be exploited by anybody.

 

A much more reliable solution is to let the database do the check. Add a UNIQUE constraint to the columns and then simply try to insert the new row. If that fails due to a constraint violation, you know the data is not unique. Otherwise everything is fine.

<?php

// database settings
const DB_HOST = '...';
const DB_USER = '...';
const DB_PASSWORD = '...';
const DB_NAME = '...';
const DB_CHARSET = 'UTF8';

// MySQL error codes
const MYSQL_ER_DUP_ENTRY = 1062;



// Enable exceptions for MySQLi.
$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

$database_connection = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$database_connection->set_charset(DB_CHARSET);



$test_name = 'foo';
$test_email_address = 'bar@example.com';

$user_registration_stmt = $database_connection->prepare('
    INSERT INTO users
    SET
      public_name = ?,
      email_address = ?
');

$user_registration_stmt->bind_param('ss', $test_name, $test_email_address);

// Try to insert row.
$user_registration_errors = [];
try
{
    $user_registration_stmt->execute();
}
catch (mysqli_sql_exception $user_registration_exception)
{
    // Was the error caused by a duplicate entry?
    if ($user_registration_exception->getCode() == MYSQL_ER_DUP_ENTRY)
    {
        $user_registration_errors[] = 'The username or e-mail address is already in use.';
    }
    else
    {
        // It's some other problem, pass the exception on.
        throw $user_registration_exception;
    }
}

if ($user_registration_errors)
{
    foreach ($user_registration_errors as $error)
    {
        echo htmlspecialchars($error, ENT_QUOTES | ENT_SUBSTITUTE | ENT_HTML5, 'UTF-8').'<br>';
    }
}
else
{
    echo 'Registration successful!';
}

There's still a problem left: Usually, the e-mail addresses of users are private, so they must not be exposed, neither directly nor indirectly. Right now, anybody can check if an address is already in your database simply by trying to register with it.

 

Is that different for your site? Do you have an agreement with your users that all e-mail addresses are public?

 

thanks for the code !

and thank you for highlighting this problem, actually I'm still new in PHP/MySQLi and this test website will not be published, it's just for improve my coding skills.

[Jacques1]

OK. In that case, the code will be good enough.

 

To distinguish between a duplicate name and a duplicate address, you can either parse the error message or make a second query after the INSERT query failed: Simply fetch all users where the name or the address matches the submitted data, then inspect the resulting row to find out which of the two has caused the problem.

[Psycho]

Also, you generally must not expose the e-mail addresses of your users to the public. Whether a particular address is registered at your site is none of anyone's business. Instead, you would send out a mail telling the user that they already have an account.

 

Great point. This is something that most sites overlook, even some large technology companies. In fact, some sites exacerbate the problem by implementing a service call to specifically check for a duplicate email address. A bot can easily harvest "good" email addresses using these sites for spamming or other nefarious purposes.

 

There is an excellent training video that covered this and other security issues that are commonplace that I would recommend (Requires a license or sign up for a free trial.): https://www.pluralsight.com/courses/play-by-play-website-security-review-troy-hunt-lars-klint. Lots of other great resources on that site.

 

The video covers a lot issues related to business rules, not just coding issues. Programmers need to understand "why" certain things should not be done that a customer or product manager may be requesting to advise them as to the problems their request would create.