«

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

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

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?

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

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