ALTER FUNCTION [dbo].[FuncGetSmartDepartmentRules] 
(
	-- Add the parameters for the function here
	@DepartmentID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	-- Declare the return variable here

	
	DECLARE @Sql VARCHAR (MAX),@SqlFilter VARCHAR(MAX)
	-- Declare cursor
	DECLARE	smart_deps CURSOR FAST_FORWARD FOR
	SELECT SQLFilter 
	FROM SmartDepartments
	WHERE DepartmentID = @DepartmentID
	
	OPEN smart_deps
	
	FETCH NEXT FROM smart_deps
	INTO @SqlFilter
	--first part of sql
	SET @Sql = @SqlFilter
	
	FETCH NEXT FROM smart_deps
	INTO @SqlFilter
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		SET @Sql = @Sql + ' OR ' + @SqlFilter
		
		FETCH NEXT FROM smart_deps
		INTO @SqlFilter
	END
	
	CLOSE smart_deps
	DEALLOCATE smart_deps

	-- Return the result of the function
	RETURN @Sql

END

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT *
FROM someTable
WHERE
    1 = 0'


DECLARE @foo TABLE (
    [injectionAttack] NVARCHAR(MAX)
    )

INSERT @foo ([injectionAttack])
           SELECT '1 = 1'
UNION SELECT '1 = 1; DROP TABLE Sales; --'
UNION SELECT 'x = y'

-- Concatenate the string
SELECT @sql = sql + ' OR ' + [InjectionAttack]
FROM @foo

PRINT @sql

quote:

---

Originally posted by Transact Charlie

cool. Do you ever check them again once they are in there?

At least you can remove that cursor now!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by Transact Charlie

bulk import / some other mechanism that bypasses your std insert checks.

basically just be really, really, really cautious. I always get nervous when people propose code like that.

If I have to do any kind of general purpose search (searching a dynamic number of columns from a table). I'd much rather implement a dynamic sql solution that doesn't run the risk of sql injection at all.

Gail posted a good article on 'catch all' queries that might be of interest to you.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---