About string parameters to stored procedure

Discussion in 'ASP .Net Security' started by Owen Wong, Sep 4, 2006.

  1. Owen Wong

    Owen Wong Guest

    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
    -------------------
     
    Owen Wong, Sep 4, 2006
    #1
    1. Advertising

  2. 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
    > -------------------
     
    Dominick Baier, Sep 4, 2006
    #2
    1. Advertising

  3. Owen Wong

    Owen Wong Guest

    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?
     
    Owen Wong, Sep 4, 2006
    #3
  4. 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?
    >
     
    Dominick Baier, Sep 4, 2006
    #4
  5. Owen Wong

    Owen Wong Guest

    Thank you very much, Dominick. You're really GREAT.
     
    Owen Wong, Sep 4, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. kavitha N via .NET 247

    passing parameters to stored procedure from crystal reports

    kavitha N via .NET 247, Jun 8, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    10,821
    galahad
    Feb 15, 2011
  2. TaeHo Yoo
    Replies:
    0
    Views:
    404
    TaeHo Yoo
    Aug 14, 2003
  3. =?Utf-8?B?SklNLkgu?=

    Q: number of parameters in stored procedure

    =?Utf-8?B?SklNLkgu?=, Jan 11, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    1,285
    Steve C. Orr [MVP, MCSD]
    Jan 12, 2005
  4. =?Utf-8?B?TWlrZQ==?=
    Replies:
    0
    Views:
    753
    =?Utf-8?B?TWlrZQ==?=
    Jun 22, 2005
  5. Mike P
    Replies:
    0
    Views:
    3,307
    Mike P
    Jun 19, 2006
Loading...

Share This Page