SQL Injection

Transact-SQL
1

hi

I have this Stored Proc for SQL Injection:

ALTER Proc [dbo].[UserLoginProc]
@username varchar(50),
@password varchar(50)
as
DECLARE @sql nvarchar(500);
SET @sql = 'SELECT [UserId] ,[UserName] ,[UserPassword]
FROM [dbo].[Users] where UserName = ''' + @username + ''' AND UserPassword = ''' + @password + ''' ';
EXEC(@sql);

I tried Exec [dbo].[UserLoginProc] 'test'' or 1 =1''''--''' ,'''''' but not able to get all the data.

I tried using the raw query and it works well. SELECT [UserId] ,[UserName] ,[UserPassword]
FROM [dbo].[Users] where UserName = 'test' or 1 = 1 -- AND UserPassword = ''' + @password + '''

I guess is the single quote is giving me the problem but just could not get it to work.

How should I go about it? Thanks a lot

2

is there a reason you want to do this as dynamic query?
why not just do a straight query?

SELECT [UserId] ,[UserName] ,[UserPassword]
FROM [dbo].[Users] 
where UserName =  @username 
AND UserPassword =   @password ;

am I missing something here?

3

Hi

Thanks for the reply. Just want to demonstrate the danger of dynamic sql.

Thanks

4

hi

I have found the solution.

Exec [dbo].[UserLoginProc] 'test'' or 1 = 1 --',''

Thanks a lot