Parametrizing input using sql server?

Question

I want to parametrize my stored procedure's input to prevent sql injection. The problem is MY database has no application(It's just for school) & as there's no client language like C# etc, I have to do it with sql itself. i did this

ALTER procedure [dbo].[drop_tt]
@ss varchar(40)
as
EXEC sp_executesql N'SELECT *
FROM    tt
whERE   ss = @Ss', N'@ss varchar(40)', @ss

but when I execute this statement the tt table was droped :( exec drop_tt 'www';drop table tt--'

anyone can help?

Answer 1

In short: why are you altering sp? you just need to create a parametrized stored procedure like:

CREATE PROCEDURE uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

Just look at this very simple tutorial , you don't need to alter your procedures.

Edit: my approach would be to get rid off the statement EXEC sp_executesql and naming that starts with drop. Just try to simplify your stored procedure execution statement in the body.