How can I limit inserted varchars. eg

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 2000 Forums

Transact-SQL (2000)

How can I limit inserted varchars. eg

Author

Topic

midi25
Starting Member

24 Posts

Posted - 2005-05-22 : 16:38:40

Say I want to limit an insertion into a password field to between 8 and 25 characters.

Could I simply use a column level check or would I have to use a Trigger

thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-22 : 17:09:11

I would assume you want to store the password field encrypted. In that case, you should be validating the length at the presentation layer, then passing an encypted string to the database. So, the answer in this case, is probably neither.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-22 : 22:47:06

options:
1. limit the size of field to 25
2. control/validate the length at the application layer

--------------------
keeping it simple...

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-23 : 03:56:53

You can restrict it at the database level if necessary, using CHECK CONSTRAINTS.


CREATE TABLE #Test (col1 varchar(100), col2 int)
go

insert into #test values('abcd', 100)
insert into #test values('abcdegffgj', 100)
insert into #test values('abcd', 100)

SELECT * FROM #test
GO 

ALTER TABLE #test WITH NOCHECK 
ADD CONSTRAINT test_limit CHECK (LEN(col1) > 8 AND LEN(col1) < 15)
go

insert into #test values('abcd', 100)
insert into #test values('abcdegffgj', 100)
insert into #test values('abcd', 100)
GO

DROP TABLE #test

midi25
Starting Member

24 Posts

Posted - 2005-05-23 : 05:51:15

Thanks for the replies.

After reading a lenghty article on SQL Injection attacks I have picked up a few best practices.

Its seem validation at the UI is the best way to trap data before it reaches the DB.

I,ll just use .Nets validation controls. RegularExpression Server Control will help. I can then restrict entered characters and length.

Maybe with an additional db column level check added just incase someone circumvents the UI security.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources