About string parameters to stored procedure
 

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

Re: About string parameters to stored procedure
 

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)

---
Dominick Baier, DevelopMentor
http://www.leastprivilege.com

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

Re: About string parameters to stored procedure
 

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?

Re: About string parameters to stored procedure
 

Hi,

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

Just use SqlCommand, CommandType.StoredProcedure and SqlParameter.

---
Dominick Baier, DevelopMentor
http://www.leastprivilege.com

> 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?
>

Re: About string parameters to stored procedure
 

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