Tuesday, January 16, 2007

3355.aspx

Using regular expressions in SQL Server

Regular Expressions are very powerful when working with text, and in my case when detecting blog spam. I didn't feel like modifying .TEXT so I modified the DB to match comments against a list of "bad words". I replaced MT Blacklist with regular expressions after it went offline

The user function below matches a string against a regular expression. It works in all flavors of SQL Server that I have tried it on (SQL Server 2000 MSDE, SQL Server 2005 standard, Express and Express with Advanced Services). It has caught more than 12.800 spam comments since it went online :-) But more on the anti spam later, this is the function:

2 comments:

To use Stored Procedure bound to OLE Object (I mean the sp_OA* family) the login has to be in the SysAdmin Role, and in such case you lose completely the security of your DB since who can hack your WebServer will gain SysAdmin access to your DB.

Nevertheless it remains a not real world usable but good SQL exercise.