0

e-commerce site is getting hit by a scammer, and one pattern we see is he is avoiding creating duplicate accounts to catch him by simply throwing dots in his email.

ie. "my.em.ail@gmail.com", "m.ye.mail@gmail.com", "mye.ma.il@gmail.com"

I know this would just inconvenience him or others trying this into creating more emails, but that is no reason to not catch them all.

Our fraud prevention looks for high velocity duplicate orders, for example:

$email = 'myemail@gmail.com';

"SELECT * FROM `orders` WHERE `date` > '".date('Y-m-d H:i:s', time() - 60*60*24)."' AND `email` = '".$email."'"

How would I update the WHERE clause to include the above examples in this query?

Roy Bogado
  • 4,299
  • 1
  • 15
  • 31
Mark Nowak
  • 5
  • 2
  • You should also be storing the IP address – dustytrash Sep 18 '18 at 16:55
  • We do, but our WAF is obfuscating them and we just see the edge server's IP. If I can get around that then we'll re-enable looking for duplicates from same IP as well. In this case he's using different IP's anyhow, but using same gmail address with dots randomly strewn about. – Mark Nowak Sep 18 '18 at 16:58
  • May I ask which e-commerce website this is? – dustytrash Sep 18 '18 at 17:01
  • I don't see how that is relevant other than informing people which site they can do this at until i fix this? – Mark Nowak Sep 18 '18 at 17:03

1 Answers1

0

In the examples you gave, you could remove the .s then find any rows with the same email (not counting the periods again).

E.G:

$email = 'myemail@gmail.com';

SELECT * FROM `orders` WHERE `date` > "date('Y-m-d H:i:s', time() - 60*60*24)" 
AND REPLACE(`email`, ".", "")` = REPLACE("$email", ".", "")
)

If you need something more sophisticated, you'll have to create a function to measure the 'likeness' of a string in order to compare them. See here for more details: How to find similar results and sort by similarity?

dustytrash
  • 1,568
  • 1
  • 10
  • 17
  • Thank you! Sticking to the example, this worked: SELECT * FROM `orders` WHERE `date` > '".date('Y-m-d H:i:s', time() - 60*60*24)."' AND REPLACE(`email`, ".", "") = REPLACE('".$email."', ".", ""); – Mark Nowak Sep 18 '18 at 17:27
  • No Problem! I forgot to mention in my answer though, I believe you have a bigger security issue if you cannot identity users from more than just their input/email. I hope the scammer isn't getting anything of monetary value from this. – dustytrash Sep 18 '18 at 17:31
  • Yea, we lost several thousand. The bigger security issue was the false assumption that FedEx was not allowing "customer" redirecting of shipments when to us it looked like we were shipping to AVS verified addresses. I'm well aware of that hole so I'm pretty mad. The IP we see is our WAF's edge servers, so I'm thinking a javascript hack to make browser self report. Far from bulletproof technique, but most of these guys aren't usually the brightest tools in the shed. This email query doesn't offer much, but yet it would have caught him in my order velocity filter. – Mark Nowak Sep 19 '18 at 18:20
  • @MarkNowak There are different ways to track users, I'd recommend looking into `fingerprintJS` for example. It still won't be bullet proof though, as the attacker can switch browsers or hide his fingerprint. I'm hoping you also ensure the email addresses are valid – dustytrash Sep 19 '18 at 18:26
  • 1
    Actually incapsula and cloudflare do pass on real user IP, so I need to look for HTTP_CF_CONNECTING_IP and X-FORWARDED-FOR before defaulting to $_SERVER['REMOTE_ADDR']. Thanks for the push to get this done pronto. – Mark Nowak Sep 19 '18 at 18:33