We have an upload tool to upload users via CSV. In this loop we check with MySql if the user exists in this way:
SELECT id FROM user_table WHERE email = 'email@domain.com'
We save the result of this query in $sql_array.
After that we do a count($sql_array). If that count is higher than zero we don´t do anything. Otherwise we insert the user in the user_table.
Now the weird thing we´ve been noticing is that in some cases it inserts users with an email address that already exists.
We have a function that sends us an email if the query results in an error. For example in case of a timeout or connection loss with MySQL. We didn´t receive any emails but still have these duplicate results. Does anybody know how this is possible?
Make sure that the email addresses are really identical and don't contain any stray whitespace before or after the actual string. Capitalization of the email addresses could differ as well, but my first guess is untrimmed strings.
One other thing to note is the performance of your query. Doing a select, transforming the result into an array and then counting the array is not optimal. Better you SELECT COUNT(*) FROM users_table WHERE email = '…' (putting an index on email also helps)
Its possible that your query fails to match a record if:
The collation of your db is case sensitive email@domain.com is not equal to Email@domain.com
Be sure the emails are trimmed and they are stored in database trimmed.
Important update:
If your script is vulnerable to sql injection, a simple quote "'" can cause the query to fail. For example if the $email has value
fdassdfsdf'sdfsfd@.com
will result to a query
SELECT id FROM user_table WHERE email = 'fdassdfsdf'sdfsfd@.com'
this will NOT execute since it has bad syntax, you will get 0 rows as result, and your script will think user does not exist and will allow registration.
Depending on the way your data is saved in the table, there could be white space issues making it a false positive
Something like this may help
$query = 'SELECT id FROM user_table WHERE email = "'.trim($emailFromCsv).'"';