sp_executesql > Stored Proc > Parameters

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

Transact-SQL (2005)

sp_executesql > Stored Proc > Parameters

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-19 : 22:23:33

I tried following and I was able to inject and delete a row and finally drop the table.... DANGEROUS...Can anyone provide a solid fix for this issue...

CREATE PROCEDURE uspGetEmployee
@LastName NVARCHAR(50) = Null

AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(300),
@paramlist nvarchar(300)
SELECT @sql='SELECT E.* FROM EMPLOYEES_A E
WHERE 1=1 '
IF @LastName IS NOT NULL
SELECT @sql = @sql + ' AND E.LASTNAME= @LastName'
select @paramlist = '@lastname varchar(50)'
EXEC sp_executesql @sql, @paramlist, @lastname
end
GO

========================================
DECLARE @RC int
DECLARE @LastName nvarchar(50)
EXECUTE @RC = [Northwind].[dbo].[uspGetEmployee]
delete from employees_a where lastname='fuller'

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-19 : 22:46:46

I tried following with the northwind database and no luck. i was able to delete and drop...

CREATE PROCEDURE uspGetEmployee
@LastName VARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(300)
SELECT @sql = ' SELECT * ' + ' FROM EMPLOYEES
WHERE 1=1 '
IF @LastName IS NOT NULL
SELECT @sql = @sql + ' AND [LASTNAME] = @LastName'
EXEC sp_executesql @sql, N'@lastname VARCHAR(50)', @LastName
end
GO

Here is how I deleted and drop the table...

DECLARE @RC int
DECLARE @LastName varchar(50)
EXECUTE @RC = [Northwind].[dbo].[uspGetEmployee]
delete from employees where lastname='king'

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-11-20 : 10:19:37

http://www.tek-tips.com/faqs.cfm?fid=5267

I found this link and wondering if this was my last resort?

mandm
Posting Yak Master

120 Posts

Posted - 2009-11-20 : 13:19:02

The solution is to not use dynamic SQL. Try the following instead.

CREATE PROCEDURE uspGetEmployee
@LastName NVARCHAR(50) = Null

AS
BEGIN
SET NOCOUNT ON;

SELECT E.*
FROM EMPLOYEES_A E
WHERE (@LastName IS NULL OR E.LASTNAME= @LastName)

END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources