help parsing string for URL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help parsing string for URL

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-02-24 : 18:54:44

Hello,

I'm having a major spam problem (phishing) on my web app, and I'm trying to manage it the best way I can. I already have multiple layers of defense, but its not enough.

I have a simple query that retrieves messages, what I want to do is enhance the query a bit so it can tell me if there are URL's in the string.

I'm flexible on how the data I bring back would be formed, but ultimately the purpose is just so that in my front end code I can determine whether to display a warning on the message.

I'm not sure how I would want to handle multiple URL's in a message. Perhaps instead of a boolean I could bring back the count of URL's, and then a comma delimited string of URLs for the value column ?

I am guessing all this has to be done via a REGEX command ? I have been googling but not been able to find anything on this topic (via sql anyways)

If anyone has experience with something like this, it would be immensely helpful and very appreciated! I have been putting this off for weeks but can't wait any longer :)

Thanks VERY much once again!!

Mike123

The current stored procedure is below

CREATE PROCEDURE [dbo].[select_Message_View]
(
@MessageID int,
@messageToID int
)
AS
SET NOCOUNT ON

SELECT nameOnline, Message.date, message, messageFromID, mobile,

FROM tblMessage JOIN tblUserDetails on tblMessage.messageFromID = tblUserDetails.userID WHERE MessageID = @MessageID AND messageToID = @messageToID

UPDATE [tblMessage] SET [Checked] = 1 WHERE ([MessageID] = @MessageID AND [MessageToID] = @MessageToID)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-02-25 : 11:36:10

Do you have a grammar that defines when a string contains a URL(s)? How robust does the matching algorithm nned to be; will you be okay with false positives?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-02-25 : 12:34:18

Hello,

I do not have grammar that defines when a string contains a URL, no. I am flexible on figuring out the best way to determine this.

I am OK with false positives. Of course my goal is 100% accuracy but I realize that may not be achievable and of course thats OK.

When I bring this data back to my web app, I will loop thru the list of URLS (usually just 1) and I will run http requests and parse the destination URLs to further analyze to see if its spam :)

Any help is greatly appreciated..:)

Thanks!
Mike

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-02-25 : 20:16:21

found a couple good articles, but not sure exactly how to apply them .... maybe if anyone is looking to help these would be helpful ? :)

http://blog.netnerds.net/2007/01/t-sql-parse-top-level-domain-from-url/
http://www.mssqltips.com/tip.asp?tip=1325

Again any help here appreciated !!

Thanks!
Mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-02-27 : 23:10:32

any help hugely appreciated!! :)

thanks again!
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-03-01 : 17:59:06

pounding my head away at this with no luck ....... surely someone out there has done something like this before ? :)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-02 : 01:41:18

quote:
Originally posted by mike123

I have a simple query that retrieves messages, what I want to do is enhance the query a bit so it can tell me if there are URL's in the string.

Are you looking for kind of "http://" or any other specific pattern in the incoming messages?

Cheers
MIK

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-03-02 : 14:40:44

quote:
Originally posted by MIK_2008

quote:
Originally posted by mike123

I have a simple query that retrieves messages, what I want to do is enhance the query a bit so it can tell me if there are URL's in the string.

Are you looking for kind of "http://" or any other specific pattern in the incoming messages?

Cheers
MIK

Unfortunately not =\ Often times urls are spammed without the http and I want to be able to detect these too .....

any suggestions? I know I am making it difficult here! =
thanks again!
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2011-03-14 : 01:02:28

bump ! .. still stuck on this one, if anyone can offer any help is hugely appreciated! .. this spam is killing me =\

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 09:05:35

Are you on SQL 2008 R2 by any chance? If you are there are some *pretty cool* new functions you can use from Master Data Services, of which full fledged regular expressions are supported. Check the link in my sig for further details :)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

kaarthikeyan
Starting Member

8 Posts

Posted - 2011-03-17 : 04:14:08

hai mike how r u ur from cbe right

karthik k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources