Tuesday, December 6, 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:


IF OBJECT_ID('dbo.uspCreateLogin', 'P') IS NOT NULL
 DROP PROCEDURE dbo.uspCreateLogin
go

CREATE
PROCEDURE dbo.uspCreateLogin

(
@LoginName varchar(128)

 )
AS

SET NOCOUNT ON;


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

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


--No two consecutive hypens in the name
 IF @Invalid = 0 AND LEN(@LoginName) - LEN(REPLACE(@LoginName, '--', '')) > 0
BEGIN
 SET @Invalid = 1;
 END;


--Only letters, numbers, backslash (\), hyphens (-), underscores (_) and periods (.)
 IF @Invalid = 0
BEGIN
 SET @Index = PATINDEX('%[^0-9A-Za-z-\._]%', @LoginName)

IF @Index > 0
BEGIN
 SET @Invalid = 1;
 END;
 END


IF @Invalid = 1
BEGIN

RAISERROR ('only letters, numbers, a single backslash (\), hyphens (-), underscores (_) and periods (.) and no consecutive hyphens.', 11, 1);

RETURN 1;

END;

-- Check that the login doesn't already exist

IF EXISTS (
 SELECT 1
FROM sys.SysLogins
 WHERE LoginName = @LoginName
)

BEGIN
 RAISERROR ('The login already exists.', 11, 1);
 RETURN 1;
 END;

SET @SQL = 'CREATE LOGIN ' + QUOTENAME(@LoginName) + ' FROM WINDOWS;';


EXEC(@SQL);
 GO


Keywords
TSQL Regular Expression
CREATE LOGIN
CREATE USER
SQL SERVER

No comments:

Post a Comment

IIS Manager Crashes on Load/Start Windows 7, IIS 7.5

IIS Manager Crashes on Start  Windows 7, IIS 7.5, Power Shell 5.1. Here is the error I got in the event viewer. IISMANAGER_CRASH IIS Ma...