regex in query 1

[jges]

I have a table of members for a small website, it was hit pretty hard by spammers recently. I've noticed that many (but not all) of the spammer email addresses have 3 or more groups after the '@' character, like these:
[ul]
[li]someone@d.beardtrimmer.com[/li]
[li]someone@get.cheap.meds.biz[/li]
[/ul]

I'm trying to run a query to select these email addresses for further scrutiny. The regex runs correctly when I check it with rubular or regex hero, but when run against the database, it matches all the addresses. Here is the regex:
@([0-9 a-z]+\.){2,}[0-9 a-z]+

and here is the full query:
SELECT * FROM 'members' WHERE 'email' REGEXP '@([0-9 a-z]+\.){2,}[0-9 a-z]+';

MySQL database version 5.1.73 and the server is using utf8 codeset.

Is there an error in my regex or perhaps a database setting that needs to be changed? I have run other queries with simple regex with no problems.

 

[feherke]

Hi

Ask MySQL how is he seeing that regular expression :

test> SELECT '@([0-9 a-z]+\.){2,}[0-9 a-z]+';
+------------------------------+
| @([0-9 a-z]+.){2,}[0-9 a-z]+ |
+------------------------------+
| @([0-9 a-z]+.){2,}[0-9 a-z]+ |
+------------------------------+
1 row in set (0.00 sec)

See ? The escaping of the dot vanished as it was treated like a misplaced string escaping. You have to escape the backslash to survive the string parsing and reach the regular expression parsing phase :

[b]SELECT[/b] * [b]FROM[/b] 'members' [b]WHERE[/b] 'email' REGEXP '@([0-9 a-z]+[COLOR=red yellow]\[/color]\.){2,}[0-9 a-z]+';

Feherke.
feherke.ga

 

[jges]

Thank you! worked as expected.