CODEWITHSUNDEEP
sqlt-sql
CodeCaptain
CodeCaptain

Reputation: 429

Dynamic sql - Is this a good approach?

I have a table that has a column named category. The column category has values like "6,7,9,22,44". I would like to know if this approach is an efficient approach for SQL Server 2005 or 2008. I basically pass in the "6,7,9,22,44" value to the stored procedure. I am open links/suggestions for a better approach.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Note: I should clarify that I am maintaining an app written by someone else and the category column already has comma separated values. I would have placed the data in a separate table but I won't be able to do that in the short term. With regards to SQL Injection - The column is populated by values checked from a checkbox list. Users cannot type in or enter any data that would affect that specific stored proc. Is SQL injection still possible with that scenario? Please explain how. Thanks. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE PROCEDURE [dbo].[GetCategories]
    -- Add the parameter that contain the string that is passed in
    @catstring varchar(300)
AS
DECLARE @SQLQuery nvarchar(500); 
BEGIN

    /* Build Transact-SQL String with parameter value */
    SET @SQLQuery = 'select id,category from categoryTable where id in ('+ @catstring +')' 
    /* Execute Transact-SQL String */
    EXECUTE(@SQLQuery)
END
GO

Upvotes: 0

Views: 136

Answers (2)

TimothyAWiseman
TimothyAWiseman

Reputation: 14863

As Jack said, this creates a possibility of SQL injection. Sommarskog has an excellent look at different ways of handling this at: The Curse and Blessing of Dynamic SQL.

Upvotes: 0

Jason
Jason

Reputation: 1325

This is a very bad idea because it leaves your system open to SQL injection. A malicious user might be able to pass in a string like "') DROP TABLE categoryTable --" (notice the single quote at the beginning of the string), or something worse.

There are plenty of ways to protect yourself against SQL injection, and there are plenty of resources out there. I would suggest you research it on your own since it's too large an area to cover in a single question.

But for your particular problem, I would suggest this website to learn how to create a Table Valued Parameter to pass in a list of integer IDs: http://www.sommarskog.se/arrays-in-sql-2008.html

It's only valid for SQL Server 2008, you should follow the links in that article for solutions for SQL Server 2005 if you need to.

Upvotes: 2

Related Questions