Fetching data - Security Issue

  • I would like to hear about your opinions about the best way (or the least worst) you believe it would be to fetch data from the server. (From C# to SQL)

    I need to implement a SP that will return data based on a set of keys (an array in C#). As simple as that. I have 3 solutions to propose but one is bothering me about a potential security issue. I'll list the 3 I have in mind and you could comment on them or giving maybe a new solution:

    1- Returning all the data and filtering in the app for the wanted keys (return more rows than needed)

    2- Make a call for each keys in the array (huge overhead each time a connection is made, a lot of keys might be queried so I would like to avoid doing a call to the DB for each individual key)

    3- Make a call having the array of keys stored inside a NVARCHAR(MAX) parameter and implement the SP dynamically with "EXIST IN ('string of keys')" in the query (I believe it could be a security issue having dynamic code to support the array of keys from the application)

    Solution 1 could be improved a bit but that's the idea. I dislike all of them but option 1 seems the less worst scenario.

    What are your though about that?

  • I think I would use another approach. A slight modification to your option 3 without the dynamic SQL.

    Pass the keys in VARCHAR.

    Call a function to convert it's elements to a table variable.

    Use the table variable to link to your target table.

    You can use a function similar to this to split your VARCHAR into table.

    Look for Jeff's article about tally tables. And I think there is an article here about passing arrays into SPs as well.

    CREATE FUNCTION [dbo].[SplitElements](@ElementList VARCHAR(8000), @Separator CHAR(1))

    RETURNS @Elements TABLE

    (

    Element VARCHAR(8000)

    )

    BEGIN

    SET @ElementList = @Separator + @ElementList + @Separator

    INSERT INTO @Elements

    (

    Element

    )

    SELECT

    SUBSTRING(@ElementList, N+1, CHARINDEX(@Separator, @ElementList, N + 1) -N - 1)

    FROM dbo.Tally

    WHERE N < LEN(@ElementList)

    AND SUBSTRING(@ElementList, N, 1) = @Separator

    RETURN

    END

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • yes! Thanks JacekO It does help a lot! Forgot that tally table (never used before, so easier to forget!)

    I have modified a bit the tally table method to include it inside the query and it work pretty well even for a larger number of items!

    My day just got better 🙂 Thanks to you!

  • Here's the code in case: (it does not look like the tally table method but it got from there to this solution)

    DECLARE @keysArray VARCHAR(200)

    SET @keysArray = '1,10,15,100,200'

    SELECT neededRows FROM theTable

    WHERE CHARINDEX

    (

    ','+CONVERT(VARCHAR(12), intColumn )+',',

    ','+@keysArray+','

    ) > 0

  • For optione 3, you could also pass the array of variables via an xml paramater, and use that to load the temp table to use for the lookup.

  • yes you're right Michael Valentine Jones, thanks.

    However from the application point of view it will be easier to have a string array.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply