Spam Filter ISP Forums : Custom SQL Queries

Custom SQL Queries : I am, but I took at the spam for...

Fri, 11 Feb 2005 13:22:55 +0000

Author: kspare
Subject: 5043
Posted: 11 February 2005 at 1:22pm

I am, but I took at the spam for my domain and the demo account has a ton of spam coming from your hotmail account. :)

Custom SQL Queries : you noticed that??? :) I thought...

Thu, 10 Feb 2005 15:31:50 +0000

Author: Guests
Subject: 5043
Posted: 10 February 2005 at 3:31pm

you noticed that??? :)

I thought you were a busy man like I am...

BTW - I love what you've done with it all...

Custom SQL Queries : For those of you that have been...

Thu, 10 Feb 2005 09:33:43 +0000

Author: Guests
Subject: 5043
Posted: 10 February 2005 at 9:33am

For those of you that have been playing around with my spamfilter ric check back monday. My new version will be online sometime sunday or monday.

Kevin

Custom SQL Queries : I'm not even sure of the...

Sat, 05 Feb 2005 13:25:46 +0000

Author: kspare
Subject: 5043
Posted: 05 February 2005 at 1:25pm

I'm not even sure of the load. But I know it scans 10,000 records in a matter of a second? All the emails go out at 6pm. So I'd say the load is minimal. I also run the delivery asp page right on the webserver.

Custom SQL Queries : Kevin,To be honest, and trying...

Sat, 05 Feb 2005 13:23:30 +0000

Author: Desperado
Subject: 5043
Posted: 05 February 2005 at 1:23pm

Kevin,

To be honest, and trying not to "toot my own horn", my nickname here is "Mr. Cisco". The only reason I was at it all night was because the customer, 2 states away, screwed up installing additional VPN Licenses and managed to blow out the existing keys so I had to walk him through getting SSH access to the device from my IP and I think he was a few fingers short of a full hand.

Back on topic, how much SQL load do you experience gathering your "Notifier" information. I can imagine a high SQL load if the DB has a million or so records and a lot of users are sighed up. Or .. no?

Dan

Custom SQL Queries : Oh I know..i've still got...

Sat, 05 Feb 2005 12:48:04 +0000

Author: kspare
Subject: 5043
Posted: 05 February 2005 at 12:48pm

Oh I know..i've still got more planned for it.h

let me know if you need help with the pix. I do cisco in my sleep...literallly. I've woken up and had configs in my head and had to write them down because I solved the problem in my sleep! lol

Pix's....they are a tamable beast! LOL Most hate them. I love them.

Edited by kspare

Custom SQL Queries : Kevin, What did I say ... something...

Sat, 05 Feb 2005 12:36:36 +0000

Author: Desperado
Subject: 5043
Posted: 05 February 2005 at 12:36pm

Kevin,

What did I say ... something like "Once you start it's hard to stop"?!

I was at work ALL Night working on a PIX Firewall configuration and now ... while I should be going home, I am screwing around trying to figure our why my "favicon" for the SpamFilter manager is intermitant. Why am I doing this? I think it's an illness!

Dan

Custom SQL Queries : I must mention that I started...

Sat, 05 Feb 2005 11:52:27 +0000

Author: kspare
Subject: 5043
Posted: 05 February 2005 at 11:52am

I must mention that I started this code based on some code dan gave me a year or so ago, so thanks Dan :)

Custom SQL Queries : This is my listspam.asp code....

Sat, 05 Feb 2005 11:49:09 +0000

Author: kspare
Subject: 5043
Posted: 05 February 2005 at 11:49am

This is my listspam.asp code. I've modified it with the following features:

a Cell mouseover color change for the line your one.

During the mouse over it will show a popup with the reasons why the message was caught.

You can also click anywhere on the line to check the message.

Notice the security I put at the top of every page, so joe blow can't just access the asp code.

<%If Session("User") = "" Then
Response.Redirect("/spam")
END IF%>

<%
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

Session.Timeout = 30
EmailTo = Session("EmailTo")

'Theoretically, this should never happen.
If Len(EmailTo) < 1 Then
  Session("EmailTo") = Null
  Session.Abandon
  Response.Redirect "ListSpam.asp"
End If

LoopCount = 0
MaxLoop = 500
%>
<%
  'Generate Notification query.
   Email = Session("EmailTo")
   SQL = "SELECT Email, Notification "
   SQL = SQL & "FROM tblLogins "
   SQL = SQL & "WHERE (Email = '" & Email & "') "
   Set rs = con.Execute(SQL)
   Notification = rs("Notification")
   Session("Notification") = Notification
   con.Execute(SQL)

%>
<%
'update the Notification field for this user
IF Request.QueryString("notification") = "off" THEN
SQL = "UPDATE tblLogins SET notification = 0"
SQL = SQL & "WHERE (Email = '" & Email & "') "
Set rs = con.Execute(SQL)
Response.Redirect "ListSpam.asp"
ELSEIF Request.QueryString("notification") = "hourly" THEN
SQL = "UPDATE tblLogins SET notification = 1"
SQL = SQL & "WHERE (Email = '" & Email & "') "
Set rs = con.Execute(SQL)
Response.Redirect "ListSpam.asp"
ELSEIF Request.QueryString("notification") = "daily" THEN
SQL = "UPDATE tblLogins SET notification = 2"
SQL = SQL & "WHERE (Email = '" & Email & "') "
Set rs = con.Execute(SQL)
Response.Redirect "ListSpam.asp"
END IF
%>

Spam Filter - Suspected Spam List

<%
'Determine if a sort order was chosen.
Session("sortby") = Request.QueryString("SortBy")
order_by = Trim(Request.QueryString("SortBy"))

If order_by = "" Then
order_by = "DESC-MsgDate"
Session("SortDir") = "FILO"
End If

Select Case order_by
  Case "Subject"
   order_by = "Subject, MsgDate, EmailFrom"
  Case "EmailFrom"
   order_by = "EmailFrom, MsgDate, Subject"
  Case "MsgDate"
   order_By = "MsgDate, EmailFrom, Subject"
  Case "DESC-MsgDate"
   order_By = "MsgDate DESC, EmailFrom, Subject"
End Select

'Generate query.
SQL = "SELECT QuarID, EmailFrom, Subject, MsgDate, MsgID, RejectDesc, RejectDetails "
SQL = SQL & "FROM tblQuarantine, tblRejectCodes "
SQL = SQL & "WHERE (EmailTo = '" & EmailTo & "') "
SQL = SQL & "AND (Deliver <> 1) "
SQL = SQL & "AND (Expire <> 1) "
SQL = SQL & "AND (tblQuarantine.RejectID = tblRejectCodes.RejectID) "
SQL = SQL & "ORDER BY " & order_by
Set rs = con.Execute(SQL)
RecordCount = rs.RecordCount
%>

Total Quarantined Messages For:
<%=Server.HTMLEncode(Ema ilTo)%>	<%=Server.HTMLEncode(RecordCount)%>
Times Logged In:	<%=Session("Logins")%>
Maximum Entries Displayed:	<%=MaxLoop%>
<% IF Session("Notification") = 0 THEN response.write "Notification:" & vbCRLF ELSEIF Session("Notification") = 1 THEN response.write "Notification:" & vbCRLF ELSEIF Session("Notification") = 2 THEN response.write "Notification:" & vbCRLF END IF %>	<% IF Session("Notification") = 0 THEN response.write "Off" & vbCRLF ELSEIF Session("Notification") = 1 THEN response.write "Hourly" & vbCRLF ELSEIF Session("Notification") = 2 THEN response.write "Daily" & vbCRLF END IF %>

   <% If Session("SortDir") = "FIFO" Then %>

    <% Session("SortDir") = "FILO"
   Else %>

    <% Session("SortDir") = "FIFO"
   End If %>


<% Count = 1 %>
<%

  While (Not rs.EOF) AND (LoopCount < MaxLoop)
   LoopCount = LoopCount + 1
   QuarID = rs("QuarID")
   MsgID = rs("MsgID")
%>

<% If Count = 0 Then %>

& lt;%=Chr(13)%>Details:<%=Chr(13)%><%=rs("Rejectd etails")%>">
<% ELSE %>
& lt;%=Chr(13)%>Details:<%=Chr(13)%><%=rs("RejectD etails")%>">
<% end if %>

.checked==true){<%="chk_" & QuarID%>.checked=false;} else{<%="chk_" & QuarID%>.checked=true;}">
   <%Scount = Scount + 1%>

<%
Count = Count + 1
If Count = 2 Then Count = 0 %>
<%
rs.MoveNext
Wend
%>

Sel	No.	From	Subject	Date	Date
">	<%=Scount%>	view.checked==true){<%="chk_" & QuarID%>.checked=false;} else{<%="chk_" & QuarID%>.checked=true;}"> <%=Server.HTMLEncode(rs("EmailFro m"))%>	<%=Server.HTMLEncode(rs("Subject"))%>	<%=rs("MsgDate")%>

<%
End If

rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
%>

Custom SQL Queries : I also modified the register page...

Sat, 05 Feb 2005 11:27:35 +0000

Author: kspare
Subject: 5043
Posted: 05 February 2005 at 11:27am

I also modified the register page to work with autoauth, so once you register, you can click the link on the screen and it will automatically log you in.

This also works with the notification email page, everyone in the database will recieve an email, if you have setup an account it delivers the email according to your notfication setting, which is off, hourly and daily.

If you haven't registered, the notification email will include a link to the register page which will automatically fill in your email address for you, requiring you to only enter your full name.

Sending Reminders to those with messages in queue and Daily reminders tuned on.

<%
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
X = 0
SQL = "SELECT * FROM tbllogins WHERE notification = 2 ORDER BY Email ASC"
Set rs = con.Execute(SQL)
IF NOT (rs.eof AND rs.bof) THEN
rs.MoveFirst
DO UNTIL rs.eof
   SQL = "SELECT COUNT(*) AS MessageCount FROM tblQuarantine WHERE (EmailTo = '" & rs("Email") & "') AND Expire = 0 AND Deliver = 0"
   Set rs2 = con.Execute(SQL)
   IF clng(rs2("MessageCount")) > 0 THEN
    X = X + 1
    SendMessage1 rs("Email"), rs2("MessageCount")
    response.write rs("Email") & " has " & rs2("MessageCount") & " messages in queue... reminder sent.
" & vbcrlf

   END IF
   rs2.close
   set rs2 = nothing
   rs.MoveNext
LOOP
END IF
response.write "
" & X & " daily reminder messages sent.

" & vbCRLF
response.write "Sending daily reminders to those with messages, And no accounts.

" & vbCRLF & vbCRLF
X = 0
SQL = "SELECT DISTINCT EmailTo "
SQL = SQL & "FROM tblquarantine "
SQL = SQL & "WHERE Expire = 0 AND Deliver = 0 "
SQL = SQL & "ORDER BY EmailTo"
Set rs = con.Execute(SQL)
If rs.EOF Then
   Response.Write "

No addresses in database!

"
Else
   rs.movefirst
   While Not rs.EOF
    EmailTo = lcase(rs("EmailTo"))
    OKToMail = True
    SQL2 = "SELECT * FROM tbllogins WHERE Email = '" & EmailTo & "'"
    Set TestRS = con.Execute(SQL2)
    IF not (TestRS.eof AND TestRS.bof) THEN
     IF TestRS("Notification") =< "2" THEN
      OKToMail = False
     END IF
    END IF
    TestRS.close
    Set TestRs = nothing
    IF OKToMail THEN
     SQL = "SELECT COUNT(EmailTo) AS MessageCount FROM tblQuarantine WHERE (EmailTo = '" & EmailTo & "')"
     Set MessageCountRS = con.Execute(SQL)
     IF MessageCountRS(0) > 0 THEN
      SendMessage2 EmailTo, MessageCountRS(0)
      X = X + 1
      response.write EmailTo & " has " & MessageCountRS(0) & " messages in queue... reminder sent.
" & vbcrlf
     END IF
    END IF
    rs.MoveNext
   Wend
END IF
rs.close
set rs = nothing
con.close
set con = nothing
response.write "
" & X & " daily reminder messages sent to users with no accounts.

" & vbCRLF

SUB SendMessage1(strEmail, lMessageCount)
Set rs3 = Server.CreateObject("ADODB.Recordset")
Set rs3.ActiveConnection = con
rs3.CursorType = 1
rs3.LockType = 3

SQL = "SELECT Password "
SQL = SQL & "FROM tblLogins "
SQL = SQL & "WHERE (Email = '" & rs("Email") & "')"
Set rs4 = con.Execute(SQL)

MessageDate = Now
Message = "From: KSP Spam Administrator <support@ksphosting.com>" & vbCRLF
Message = Message & "To: " & stremail & vbCRLF
Message = Message & "Subject: Daily SPAM Notification: You have SPAM!" & vbCRLF
Message = Message & "Date: " & FormatDateTime(MessageDate, vbLongDate) & " " & FormatDateTime(MessageDate, vbLongTime) & vbCRLF
Message = Message & "Content-Type: text/html;charset=""ISO-8859-1""" & vbCRLF & vbCRLF
Message = Message & "

We have detected " & lMessageCount & " suspicious E-Mail(s) for your email account " & strEmail & ".

Please click on http://www.ksphosting.com/spam/autoauth.asp?username="& strEmail &"&password="& rs4("Password") & """>http://www.ksphosting.com/spam to look at the message(s).

From this menu you may choose to delete all suspicious e-mail(s) or send through to your you mail program.
Please note: if you send the message through this individual sender they will be automatically cleared for all future e-mails.

What is SPAM? Click here for the answer: http://computer.howstuffworks.com/spam.htm""> color=""#000080"">http://www.ksphosting.com/answers/spam& amp; lt;/font>

" & vbCRLF
Message = Message & "." & vbCRLF
SQL = "SELECT * FROM tblMsgs WHERE 0 = 1"
rs3.Open(SQL)
rs3.AddNew
rs3("Msg") = Message
rs3.Update
MsgID = rs3("MsgID")
response.write "MsgID= " & msgID & "
" & vbCRLF
rs3.Close
SQL="SELECT * FROM tblQuarantine WHERE 0 = 1"
rs3.Open(SQL)
rs3.AddNew
rs3("EMailFrom") = "support@ksphosting.com"
rs3("EMailTo") = strEMail
' rs3("EMailTo") = "kevin@pare.ca"
rs3("Subject") = "Daily Reminder: You have messages being held in quarantine"
rs3("MsgID") = MsgID
rs3("MsgDate") = MessageDate
rs3("Expire") = 0
rs3("Deliver") = 1
rs3("RejectDetails") = ""
rs3("RejectID") = 0
rs3("ServerID") = 4
rs3.Update
rs3.Close
Set rs3 = Nothing

END SUB

SUB SendMessage2(strEmail, lMessageCount)
Set rs3 = Server.CreateObject("ADODB.Recordset")
Set rs3.ActiveConnection = con
rs3.CursorType = 1
rs3.LockType = 3
MessageDate = Now
Message = "From: KSP Spam Administrator <support@ksphosting.com>" & vbCRLF
Message = Message & "To: " & stremail & vbCRLF
Message = Message & "Subject: Daily SPAM Notification: You have SPAM!" & vbCRLF
Message = Message & "Date: " & FormatDateTime(MessageDate, vbLongDate) & " " & FormatDateTime(MessageDate, vbLongTime) & vbCRLF
Message = Message & "Content-Type: text/html;charset=""ISO-8859-1""" & vbCRLF & vbCRLF
Message = Message & "