Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - help with sql script
  FAQ FAQ  Forum Search   Register Register  Login Login

help with sql script

 Post Reply Post Reply
Author
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Topic: help with sql script
    Posted: 28 December 2004 at 3:20pm

I'm seeing a large problem where spam getting caight in the database is simply virus. So instead of getting caught by the attachment it's getting caught by the ip. Since the rules order isn't going to be changed anytime soon, I need to find a way to delete all messages out of the quaruntine that have been blocked by IP and have attachment extentions that are common to virus' IE. .htm .bat .scr etc

Any suggestions?

Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post Posted: 28 December 2004 at 4:53pm
Kevin,

I am looking at this but I do not think it will be easy.  One starting suggestion ... in the SpamFilter "Customized Items", change the "ResponseBlacklistLocalIP=" from the default to some unique code so that you have something to search on.  Mine is set as follows:
ResponseBlacklistLocalIP=521 5.2 The IP used to deliver this message, (%IP%) is Blacklisted. Contact that IP block's admin.

This allows me to query "521 5.2" and I only get the Local Blacklist IP stuff.
 
Once this is done, the query may be easier but I need to look at this more closely and get back to you.
 
Regards (for now)
 
Dan S.
Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post Posted: 28 December 2004 at 5:27pm

Kevin,

I too have some quarantined stuff that was caught by other filters an is, in fact, a virus but if the customer reies to send it, my virus gateway will catch it.  For example:

147  Text OR HTML    llucas@lauraltonhall.org hostmaster@aol.com Confirmation 12/28/2004 9:16:18 AM SPF Sender Policy Framework match 550 The sender did not meet Sender Policy Framework rules. Please see http://spf.pobox.com SID=11 Razor

This actually has an email virus but was trapped by SPF.

Did the forum already have a discussion on the attachment BL order?  SF-ISP is not (yet) intended to be an anti-virus gateway but I can see that quarantining the above, while not causing any issues in my setup, is less than desireable.

Question ... Does MS-SQL have any options to do a virus scan on a per-field basis?  I have not ever seen anything like that but it may be possible.

Dan

Back to Top
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Posted: 28 December 2004 at 6:14pm

Dan, you are seeing the same thing as me. Spamfilter if setup properly can catch virus' quite efficiently. Before we continue on with this, I think we need to talk about this with roberto. Having the ip moved to last would solve most of our problems, or atleast move the attachment filter first, that would help all of us to filter out our spam alot easier without having to come up with complicated custom solutions.

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 28 December 2004 at 10:53pm
Kevin,

Having SpamFilter search for IP and domain blocks first allows SpamFilter to be very efficient, as it does not have to scan thru an email's content to look for attachments/keywords. Furthermore, the IP/domains are available as soon as the remote servers connect, *before* any email content is even sent thru the network. SpamFilter's speed is one of the features we're very happy with, and changing the filter order will have large impact on performance.

To prevent banned-attachment files from being displayed in the web interface, an option could be to use ASP to filter out/hide/unlink emails that have the attachment in the email body. The Msg field in the tblMsgs table would have a section similar to the following for an attachment of joe.txt:

------=_NextPart_000_003B_01C4ED2F.4D510E20 Content-Type: text/plain; name="joe.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="joe.txt"

Some ASP could be developed to look for that extension(s).

Roberto F. LogSat Software
Back to Top
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Posted: 28 December 2004 at 11:33pm

Fair enough roberto, but consider this. Your efficiency assumes we don't want to quaruntine the message if it's deemed spam.

For the people like dan and myself who quaruntine ipblocked spam, it wouldn't matter if the dns lookup came last because either way we want to quaruntine all mail do the database for users and so the baysean filter can add the entire email to it's database.

So efficiency is really irrelevant. Useability is a little more important at this point as we have a huge problem with users sorting through obvious spam that is becoming a big hassle because your product is working so well :)

In short, if you quaruntine mail blocked by it's ip, your efficiency is lost because you have to read the whole message anyway right? So putting the other filters first may add some over head but in the end it helps to sort out the spam because 90% of my spam is blocked by ip...

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 29 December 2004 at 10:15pm
If emails are quarantined, as it's for a major part of the installs, yes, bandwidth is still used because the message is still being received. However my statements about the efficiency still hold true, as the message does not have to be scanned and text searches be performed against it if the other filters have already tagged it. Performance is impacted not as much by SpamFilter receiving the messages, but in SpamFilter having to parse its contents.

We have been working to implement an antivirus solution, but technical issues have hampered the process. When they are overcome, I believe that will take care of your concerns as well.

Roberto F. LogSat Software
Back to Top
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Posted: 29 December 2004 at 11:24pm
That may help alot yes. But my biggest problem right now is that in my database I have 10,000 messages. 9500 of them have been caught by the ip. I would like to simplify spam for myself and my users, but I can't just delete anything caught by IP because that is the main source of legit emails being caught. and probably 75% of those emails are virus'. Would it be possible to just add the file attachment filter first?
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 30 December 2004 at 8:40pm
The attachment filter is embedded within the keywords filter, they can't be separated. Moving one would also mean moving the other, sorry.

Roberto F. LogSat Software
Back to Top
pcmatt View Drop Down
Senior Member
Senior Member
Avatar

Joined: 15 February 2005
Location: United States
Status: Offline
Points: 116
Post Options Post Options   Thanks (0) Thanks(0)   Quote pcmatt Quote  Post ReplyReply Direct Link To This Post Posted: 03 January 2005 at 10:34pm

Kevin,

Not everyone uses the quarantine.  So, it only suits some users to hard code the solution you ask for.  That would make the software perform worse for those of us that do not quarantine. 

This in addition to the valid points that Roberto has made regarding efficient high performance design of the software.

-Matt

Back to Top
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Posted: 03 January 2005 at 10:42pm

Matt. Let's not put our foot in our mouth again shall we?

Most people do use the quaruntine.

Who said anything about hardcoding it? Generally every feature request roberto adds is configured via the ini file.

There is also a fine line between efficiency and how well and how convienient a product works. Right now, it's a pain in the ass because 90% of the spam is caught by ip. 80% of that is files caught because of their attachment. So now all my users have to sift through all of this spam to delete a bunch of useless messages that are deemed spam but I have no method to delete them. You explsin to all my customers that they have to deal with deleting all of that spam because the spam software I use is efficient instead of co-operative.

Back to Top
pcmatt View Drop Down
Senior Member
Senior Member
Avatar

Joined: 15 February 2005
Location: United States
Status: Offline
Points: 116
Post Options Post Options   Thanks (0) Thanks(0)   Quote pcmatt Quote  Post ReplyReply Direct Link To This Post Posted: 04 January 2005 at 12:35am

No foot for me this time. I never claimed any majorities used or did not use the quarantine features.  The fact is that not everyone does and that's what I stated. 

This is a rehash of an old post.  What you ask for is not unreasonable, but in fact quite sensible, which is what has also been stated before.  The problem is the complex and tedius amount of work that would be needed.  It's likely we'll see SpamFilter anti virus features before you see the product with the complex configuration options you are asking for.

What you really need is an anti virus "plug in" for your database to scan and detect viruses.  I'm sure this is doable, but I've never looked into writing such a program. 

-Matt

Back to Top
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Posted: 04 January 2005 at 12:40am

Ok. So why do I need this antivirus plugin? I'm already blocking most of my virus' with spamfilter already. anything that gets past is scanned by norton and/or mcafee.

But let's say for examply people don't have norton or mcafee. Most likely they have something on their server right?

Now by going by your theory Matt, the antivirus plugin would help us to find virus' right?

But we already know what most of them are, because we blocked them by fileattachment.

So instead of having the file attachment before the ip scan. we now have an antivirus plug in as well.

I want you to explain to me how THIS is going to help efficiency. All this can do is slow down the process, due the fact that now the entire message needs to be scanned for virus'.

Back to Top
pcmatt View Drop Down
Senior Member
Senior Member
Avatar

Joined: 15 February 2005
Location: United States
Status: Offline
Points: 116
Post Options Post Options   Thanks (0) Thanks(0)   Quote pcmatt Quote  Post ReplyReply Direct Link To This Post Posted: 04 January 2005 at 12:59am

You complained about false positives if you drop those messages blocked by IP instead of quarantining.  Your overall configuration must be problematic for that to be a bigger problem than presenting your users with their personal virus stores.  This is a perfect example of why the quarantine is a bad idea.  It turns perfectly good ISP's into junk and virus collectors.   A good reporting system that allows users to see reports on every email that has been accepted or rejected is a far better way to go.

You can't be helped if you are not looking for other than the one solution that you have asked for repeatedly which simply is not going to happen. 

-Matt

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 04 January 2005 at 10:21pm
Kevin,

SpamFilter's job is to catch spam and all unwanted emails and block them so the end user does not see it. If your customers comlain because SpamFilter blocked too much spam and now their quarantine is full of junk... sorry, but this is *exactly* what SpamFilter is supposed to do. We tried to add features and options to allow in flexibility for administrators to have some control of what is quarantined and what is instead deleted right away, but we are not inclined to sacrifice performance for functionality that is extra to the ability to block spam.

As a side note, this is our own opinion, but end users usually do not "routinely" go in their quarantine and check it for emails. At this point one may as well eliminate a spam filter since the users end up looking at all the blocked emails anyhow. But again this is our opinion and you may have requirements that we are not aware of.

The order of the filter will be made user-defineable in future versions, but whether the IP-based rules will be processed before all others or not is still not decided. In any case, this is a major change that cannot be made using simple ini file parameters, it will require a lot of development (and discalimers for loss of performance if used improperly).

Roberto F. LogSat Software
Back to Top
kspare View Drop Down
Senior Member
Senior Member


Joined: 26 January 2005
Location: Canada
Status: Offline
Points: 334
Post Options Post Options   Thanks (0) Thanks(0)   Quote kspare Quote  Post ReplyReply Direct Link To This Post Posted: 05 January 2005 at 12:26am

People need to listen.

Spamfilter is working PERFECTLY. I don't know how to make this more clear. However, with the amount of spam that does get caught alot of it is virus' that are caught but I am unable to delete from the database because they are tagged by ip first. Which is fine. If someone could help write an sql script that could delete anything with an attachment, my problem would be solved. All I want to do is simplify my customers experiences with spam and make everyone's life easier.

Basically my request is this. How can I delete messages blocked by ip that have an attachment?

Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 05 January 2005 at 1:48am

Kevin,

For this I'd go back to my 1st reply in this thread:

==============
The Msg field in the tblMsgs table would have a section similar to the following for an attachment of joe.txt:

------=_NextPart_000_003B_01C4ED2F.4D510E20
Content-Type: text/plain;
	name="joe.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="joe.txt"

==============

If as you ask for in this last posting you wish to only be able to delete all emails blocked by IP that have any attachment, you could construct a SQL query that looks for the string "Content-Disposition: attachment;", as follows:

UPDATE    tblQuarantine
SET              tblQuarantine.Expire = 1
FROM         tblQuarantine, tblMsgs
WHERE     (tblQuarantine.MsgID = tblMsgs.MsgID) AND (tblMsgs.Msg LIKE '%Content-Disposition: attacdment;%') AND (tblQuarantine.RejectID = 12)

This will cause all messages with RejectID=12 and the attachment string to be tagged for deletion by SpamFilter. Please note that RejectID 12 includes both blacklisted IPs and rejects caused by the MAPS RBL servers. SpamFilter makes no distinctions between the two. If that is not satisfactory, the query could be modified to also look at the RejectDetails field in the tblQuarantine table to look for "is Blacklisted" (a subset of "The IP aa.bb.cc.dd is Blacklisted").

DISCLAIMER - This SQL statement was thought of while responding to this post, it is in no way supported/guaranteed to work by LogSat!

Roberto F.
LogSat Software

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.094 seconds.