About string parameters to stored procedure

[Owen Wong]

Hi, I wrote a stored procedure to check user's name (vartype: chr) and
password (chr, too).
Do I have to check whether there is an apostrophe ("'") in the name
string and password string? I tried to put some "'"s in the name string
and didn't replace them with double "'", but it seemed you can not
cheat the stored procedure about it.
-----------------
My stored procedure (SQL server 2000):
CREATE PROCEDURE dbo.userLogin
(
@userName varchar(20),
@password varchar(20),
@userID int output
)
AS
select @userID=[id] from [user] where [name]=@userName and
[password]=@password
return @userID
-------------------

 

[Dominick Baier]

If you use SqlParameter on the client for parameterized queries, you are
on the safe side with this sproc (and call it directly, not via some handcrafted
sp_execute or exec string)

 

[Owen Wong]

Hi, Dominick,

Thank you for your timely reply. But could you please tell me why
should we "call it directly, not via some handcrafted sp_execute or
exec string"? Anything wrong with calling sproc via sp_execute or exec
string?

 

[Dominick Baier]

Hi,

well that means there is some string concatenation involved - which is again
prone to injection attacks..

Just use SqlCommand, CommandType.StoredProcedure and SqlParameter.

 

[Owen Wong]

Thank you very much, Dominick. You're really GREAT.