Jump to content

mysqli check if 2 value exist


aHMAD_SQaLli
Go to solution Solved by Jacques1,

Recommended Posts

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 !

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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.

  • Like 1
Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.