Posts

Showing posts from December, 2011

Regular Expression for SQL Login Name / Database User Name

I am no expert in regular expressions but I had to work on a expression to validate sql login name and database user name to avoid sql injection as the login and database user creation proc uses dynamic sql.


^([A-Za-z0-9\._]*[\\]?[A-Za-z0-9\._]*)|((?!([A-Za-z0-9-\._])*\-\-)([A-Za-z0-9-\._])*)$


Satisfies the following condition:
letters, numbers(alpha numeric), a single backslash (\), hyphens (-), underscores (_) and periods (.) no more than one consecutive hyphen.

You can use the expression above in the .net code to do the validation in the UI.


This is the proc that acheives the same result:


IFOBJECT_ID('dbo.uspCreateLogin','P')ISNOTNULL
DROPPROCEDURE dbo.uspCreateLogin
go

CREATEPROCEDURE dbo.uspCreateLogin
(
@LoginName varchar(128)
)
AS

SETNOCOUNTON;


DECLARE
@Index INT,
@SQL VARCHAR(500),
@Invalid BIT;

--No two slashes in the login name
IFLEN(@LoginName)-LEN(REPLACE(@LoginName,'\',''))> 1
BEGIN
SET @Invalid = 1;
END;


--No two consecutive hypens in the name
IF @Invalid = …