Protect from sql injection but allow user input

Transact-SQL
1

I want to know the correct way to do this

currently if someone puts in the word set - I replace all set to blank for sql injection issues

but it's now allowing someone to put in address of sunset drive

what is the right way to do this?

2

I would use a parametrised query. Additional benefit is that the query plan will be cached, which will improve performance (for regularly used queries, which remain in cache).

Say that the user can provide two parameters for the report, and both of them are optional (so, if NULL, they are excluded from the Dynamic SQL). its a bit of a simple example, but hopefully demonstrates the principle:

DECLARE @IntegerParam int, @StringParam varchar(100)

DECLARE @SQL NVarchar(MAX)

SELECT @SQL = 'SELECT Col1, Col2 FROM MyTable WHERE 1=1'

IF @IntegerParam IS NOT NULL THEN SELECT @SQL = @SQL + ' AND Col3 = @IntegerParam'

IF @StringParam IS NOT NULL THEN SELECT @SQL = @SQL + ' AND Col4 = @StringParam'

EXEC sp_ExecuteSQL @SQL
                   , N'@IntegerParam int, @StringParam varchar(100)'
                   , @IntegerParam
                   , @StringParam

Erland Sommarskog has an excellent article on Dynamic SQL (and injection etc.)
http://www.sommarskog.se/dynamic_sql.html

3

Rather than do a blanket replace of the "SET" keyword, you should look at how the parameter is being used. Replacing just "SET" is not going to help much anyway, because a bad guy or girl might be trying to TRUNCATE your most critical table to wreak havoc on you. So if you are using the parameter in an insert statement, for example like this, that is not susceptible to SQL injection:

INSERT INTO MyTable (Address) Values (@UserParameter);

If you are using the parameter in dynamic SQL you should use sp_executesql rather than exec.