MySQL select a column with replaced text where it equals a value

Question

I was recently alerted to the fact that gmail email addresses are the same whether you put a "." before "@gmail.com". So "[email protected]" and "[email protected]" both send to the "[email protected]".

As a result, when users sign up on my website, I want to check that they aren't using this exploit to make multiple accounts with essentially the same email address.

I sanitize the email address they send me, log into my database with PDO and then try running this code:

$data=$db->query("SELECT REPLACE(email,'.','') AS email_without_periods FROM account_data HAVING email_without_periods LIKE '".str_replace($sanitizedEmail,".","")."'");

if($row=$data->fetch()){
//It found a match between the sanitized email without decimals and the email rows without decimals. Hey, this dude's trying to create multiple accounts!
    $error="You're trying the email decimal trick! You sneaky devil... ";
}

However, this input doesn't work: it doesn't seem to register any rows.

When I replaced LIKE '".str_replace($sanitizedEmail,".","")."'" with LIKE '%".str_replace($sanitizedEmail,".","")."%'", it brought back all of the rows.

I basically want to search for str_replace($postEmail,".","") in the database, but to remove all the periods in email rows first. I'm using PDO.

How can I do this?

Since you're using PDO, why aren't you using prepared statements instead of concatenating into the SQL? — Barmar
You're replacing all the periods with commas, not removing them. Use replace(email, ',', '') to remove them. But you're also removing the period in gmail.com, so it will become gmailcom. — Barmar
$sanitizedEmail might be [email protected]" Basically any email address that the form gets with $_POST and then sanitizes. — Josh Powlison
So if the user enters my.email in the form, you'll sanitize it to myemail? — Barmar

Barmar Barmar · Accepted Answer · 2015-11-14T19:20:16

You have the arguments in the wrong order in str_replace. It should be:

str_replace('.', '', $sanitizedEmail)

You were using the same argument order as SQL's REPLACE function, but they're different.

MySQL select a column with replaced text where it equals a value

1 Answers