CODEWITHSUNDEEP
phpmysql
Jan
Jan

Reputation: 5815

MySQL Regexp to match domain with or without subdomain

I have:

I try to see if the domain string (with subdomains) matches the column (without subdomain) as such:

SELECT * FROM MyTable
   WHERE 'www.facebook.com' REGEXP '^([a-zA-Z0-9]+\.)*' + allowed_domain + '$'

The string "www.facebook.com" is extracted from the referrer in my actual code.

You can see the regexp I'm using in action here

My problem is that the regexp isn't matching in MySql. What's wrong with it?

Upvotes: 0

Views: 1857

Answers (3)

Victor Volpe
Victor Volpe

Reputation: 111

The solution you found is inaccurate. That regex can match unwanted domains like fakefacebook.com. Please try this:

SELECT * FROM MyTable WHERE allowed_domain='www.facebook.com' OR 'www.facebook.com' LIKE concat('%.', allowed_domain)

I'm using this query in my Squid ACL External Helper and it working like a charm.

Upvotes: 0

Jan
Jan

Reputation: 5815

Found the solution here: Using Columns in a RegExp in MySQL. I needed to send a single string object into the regexp as such:

REGEXP concat('^([a-zA-Z0-9]+\.)*', domain, '$')

Upvotes: 1

rekire
rekire

Reputation: 47945

Try better a like query:

... WHERE domain like '%.facebook.com'

You may also add an rule for facebook.com with no sub domain but that should work.

Upvotes: 0

Related Questions