none
Passing variables to stored procedure

    Question

  •  

    I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :


    create   procedure SelectDetails
    @Id string
    as
    Select * from DtTable where itemid in(@Id)


    Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
    How can i solve this problem?

    Friday, March 21, 2008 12:49 PM

Answers

  • What are you doing to provide clientside validation of your data.  If you want to use the IN operator like you are trying to, then you generally end up using a dynamic SQL command, which can be extremely dangerous in web applications since anyone can inject SQL into the string if they build it correctly. 

     

    Code Snippet

    CREATE TABLE #TABLE

    (id int)

    INSERT INTO #TABLE VALUES (1)

    INSERT INTO #TABLE VALUES (2)

    INSERT INTO #TABLE VALUES (3)

    INSERT INTO #TABLE VALUES (4)

    INSERT INTO #TABLE VALUES (5)

    INSERT INTO #TABLE VALUES (6)

    INSERT INTO #TABLE VALUES (7)

    INSERT INTO #TABLE VALUES (8)

    declare @ids varchar(100)

    set @ids = '1,3,5,7'

    declare @sql varchar(1000)

    set @sql = 'select * from #TABLE where id in ('+@ids+')'

    select @sql

    exec(@sql)

    DROP TABLE #TABLE

     

     

     

    The below domonstrates how to do what you are asking, but take caution in concatenating unvalidated strings into any dynamic SQL.  Search online for SQL Injection and you will find plenty of articles regarding it.

     

    To see a sample injection replace the @ids with:

     

    Code Snippet

    set @ids = '1,3) or 1=1; select * from sys.objects;--'

     

     

    Friday, March 21, 2008 1:24 PM
    Moderator
  •  

    Erland Sommarskog has an excellent write up on dynamic SQL - a must read.

     

    http://www.sommarskog.se/dynamic_sql.html

     

    HTH...

     

    Joe

     

    Monday, March 24, 2008 6:37 PM
    Moderator

All replies

  • What are you doing to provide clientside validation of your data.  If you want to use the IN operator like you are trying to, then you generally end up using a dynamic SQL command, which can be extremely dangerous in web applications since anyone can inject SQL into the string if they build it correctly. 

     

    Code Snippet

    CREATE TABLE #TABLE

    (id int)

    INSERT INTO #TABLE VALUES (1)

    INSERT INTO #TABLE VALUES (2)

    INSERT INTO #TABLE VALUES (3)

    INSERT INTO #TABLE VALUES (4)

    INSERT INTO #TABLE VALUES (5)

    INSERT INTO #TABLE VALUES (6)

    INSERT INTO #TABLE VALUES (7)

    INSERT INTO #TABLE VALUES (8)

    declare @ids varchar(100)

    set @ids = '1,3,5,7'

    declare @sql varchar(1000)

    set @sql = 'select * from #TABLE where id in ('+@ids+')'

    select @sql

    exec(@sql)

    DROP TABLE #TABLE

     

     

     

    The below domonstrates how to do what you are asking, but take caution in concatenating unvalidated strings into any dynamic SQL.  Search online for SQL Injection and you will find plenty of articles regarding it.

     

    To see a sample injection replace the @ids with:

     

    Code Snippet

    set @ids = '1,3) or 1=1; select * from sys.objects;--'

     

     

    Friday, March 21, 2008 1:24 PM
    Moderator
  •  

    Erland Sommarskog has an excellent write up on dynamic SQL - a must read.

     

    http://www.sommarskog.se/dynamic_sql.html

     

    HTH...

     

    Joe

     

    Monday, March 24, 2008 6:37 PM
    Moderator
  • One approach to this issue is to pass in the string containing ID values, and then in the Stored Procedure, use a function similar to Jens Suessmeyer's SPLIT() function, creating a #Temp table of ID values, and then instead of the IN clause, JOIN against the #Temp table.

     

    Split Function (Jens Suessmeyer)
       http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
       http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

    Monday, March 24, 2008 6:54 PM
    Moderator