Blocking unwanted email logins at the domain level

• AVB

  SSChampion

  Points: 11418

  More actions

  July 18, 2005 at 7:06 am

  #90113

  We have a free service on our website that gets abused a lot as well as used by the competition! I want to block emails by domain name from a table that contains these domains and emails. This is how it is currently setup in our SP:

  if charindex('@',@Email,1)=0 and charindex('.',@Email,1)=0

  or @Email in  (select email from t_EMAIL_BLOCK where active=1) 

  or charindex('BadDomainName',@Email)>0  RETURN

   

  Although the code below doesn't work my thought process is something along these lines:

  select * from t_Emails where

  email like (charindex(select Domain_Name from t_BLOCK_Emails),email)<0)

  Any Ideas???  Thanks in advance!!!!

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  July 18, 2005 at 7:36 am

  #575177

  LIKE should go with %

  email like (charindex(select Domain_Name from t_BLOCK_Emails),email)<0) and

  email = (charindex(select Domain_Name from t_BLOCK_Emails),email)<0)

  are the same.

  charindex(select Domain_Name from t_BLOCK_Emails),email) < 0 would work for you I guess.

  Regards,
  gova

• AVB

  SSChampion

  Points: 11418

  More actions

  July 18, 2005 at 8:03 am

  #575207

  Thanks for the response. I'm aware of  like 'abcde%' or  like '%abcde%'. I was just using that for an example of how the overall flow was in my head. That query doesn't work anyways. I just want to be able to search the email addresses for  'domainname'  in the blocked table and the only way I know of searching text is CHARINDEX.

  select * from t_Emails where  charindex('DomainName',email)>0

  I want to  replace 'DomainName' with the selected values from the column DomainName of the EmailBlocked Table.

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  July 18, 2005 at 1:51 pm

  #575322

  I don't know about your data and table structure.

  HTH.

  SET NOCOUNT ON

  DECLARE @EMails TABLE

  (

  EMAil VARCHAR(100)

  )

  INSERT @EMails

  SELECT 'AAAA@AAAA.COM' UNION

  SELECT 'BBBB@BBBB.COM' UNION

  SELECT 'CCCC@CCCC.COM' UNION

  SELECT 'DDDD@DDDD.COM' UNION

  SELECT 'EEEE@EEEE.COM' UNION

  SELECT 'FFFF@FFFF.COM' UNION

  SELECT 'GGGG@GGGG.COM' UNION

  SELECT 'GGGG@HAHA.COM' UNION

  SELECT 'dfgdg@AAAA.COM' UNION

  SELECT 'rtytr@BBBB.COM' 

  SELECT * FROM @EMails

  WHERE

   EMAil NOT LIKE '%@AAAA.COM'

  Regards,
  gova