How can I prevent a certain text string from being entered in a SQL Server database?

I have a large SQL Server database with lots of tables containing different kinds of fields.

Is there a global (and if not, an efficient) way to prevent the word "sauerkraut" from being entered anywhere in the database?

Whether "Sauerkraut" is the only word entered, or part of a sentence like "I love sauerkraut very much"? (and if it's part of the sentence, I don't want the record to be updated with any part of that sentence)

(Sauerkraut being an innocent example of what I'm really trying to prevent)

Thank you!
LVL 1
keimpeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason YousefSr. BI  DeveloperCommented:
  CREATE TABLE help
 (id INT IDENTITY(1,1),
 Fname VARCHAR(100) )
 
 ALTER TABLE help
 ADD CONSTRAINT ckname CHECK (Fname not LIKE '%sauerkraut%' )
  
  INSERT  help SELECT  ('sau')
  INSERT  help SELECT  ('I love sauerkraut very much')
  INSERT  help SELECT  ('sauerkrautttttt')

Open in new window

Yes, you would create a CHECK CONSTRAINT on the table and the column you need to prevent that word to be entered .

Alter Table YourTBL
ADD CONSTRAINT chk_Name CHECK (Fname not like '%sauerkraut%' )
keimpeAuthor Commented:
Thanks huslayer, I was indeed aware of CHECK CONSTRAINTs, but I was wondering if there was
some global check constraint or
a quick way to add your suggested constraint to every varchar, nvarchar, text or ntext field in an entire existing database.
Jason YousefSr. BI  DeveloperCommented:
I'm not aware of an easier way, it's a weird requirement though :)

you might create your own CURSOR to run over every DB, every Table and create that...

it would be simple, but do you really want to do that?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

keimpeAuthor Commented:
it's a weird requirement though :)

Well, the problem isn't really the word Sauerkraut (if you pinch your nose and close your eyes, it isn't all that hard), but the fact that our database is being hacked about once a month, possibly through SQL Injection. We have tried a zillion different things, but it keeps happening occasionally. When it happens, every text field is filled with a hyperlink leading to an infested PHP page. So my first and foremost concern (while we're still looking for the actual cause) is to prevent this from happening again, so I need the quickest way to prevent certain text strings from being entered in the database.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I confirm, you cannot have the same contraint added to all your tables/columns.

you could script to get such a constraint added automatically (if not yet existing) ...
Jason YousefSr. BI  DeveloperCommented:
DECLARE @Stmt         NVARCHAR(4000),
        @DBName       SYSNAME

SET @DBName = DB_NAME()

DECLARE @CName    VARCHAR(255),
        @TName    SYSNAME,
        @OName    SYSNAME,
        @Sql      VARCHAR(8000)
       
DECLARE curcolumns CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR

SELECT C.Name AS Colname
	 , T.Name AS DType
	 , QUOTENAME(U.Name) + '.' + QUOTENAME(O.Name) AS Tbl


FROM
	syscolumns C
	INNER JOIN systypes T
		ON C.xtype = T.xtype
	INNER JOIN sysobjects O
		ON C.ID = O.ID
	INNER JOIN sysusers u
		ON O.uid = u.uid

WHERE
	T.Name IN ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
	AND O.xtype IN ('U')
	AND objectProperty(O.ID, 'ismsshipped') = 0

ORDER BY
	3
  , 1


OPEN curcolumns

SET XACT_ABORT ON

BEGIN TRAN

FETCH curcolumns INTO @CName, @TName, @OName

WHILE @@FETCH_STATUS = 0

BEGIN

	SET @Sql = 'ALTER TABLE ' + @OName + 
	' ADD CONSTRAINT chk_' + @CName +' Check ( '+  @CName +' Not like ''%sauerkraut%'' )'


	--EXEC (@Sql) -- change this to print if you need only the script, not the action
PRINT @Sql

	FETCH curcolumns INTO @CName, @TName, @OName
END

CLOSE curcolumns

DEALLOCATE curcolumns

COMMIT TRAN

Open in new window


That will do your weird requirement :)

run it on a test DB first... don't screw your systems :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
keimpeAuthor Commented:
That was fast! I'll try it this weekend (I'm not at the office right now) and give you ten thousand house points if it works ;-)
venk_rCommented:
Or If you want it from the database level, try policy management with the condition.
keimpeAuthor Commented:
Looks interesting! Which Facet would I use? Could you post a screenshot of a condition that would do the trick?
venk_rCommented:
Im sorry .Just realized Policy Based Management would not help you with the data. It would help on the schema level constraints .
Please go by huslayer suggestions.
keimpeAuthor Commented:
I've tested the code and it works like a charm. The only error I got was that it doesn't work for text and ntext field, but I was going to replace them by nvarchar and nvarchar(MAX) anyway, so that's not a problem. All I had to do was change line 28 of your sample into this

	T.Name IN ('varchar', 'char', 'nchar', 'nvarchar')

Open in new window


and everything worked.

Many, many thanks!
Jason YousefSr. BI  DeveloperCommented:
Great :)

which version of SQL are you using?  just to take note of these changes to the columns type
keimpeAuthor Commented:
2008
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.