Function that removes characters that can cause SQL injection

Question

I need to use dynamic SQL in a stored procedure.

That dynamic SQL will create SQL object, therefore I cannot parameterize it and execute it with sp_executesql.

Is there some SQL function which will check the stored procedure parameter variable and tell me if there are some illegal characters? Or remove them or there is a list of these characters?

Something like

DECLARE @variable = 'password OR 1=1'

IF IsSqlInjectionPossible(@variable)
BEGIN
    RAISERROR('Illegal input characters',16,1)
    RETURN
END

or

SET @variable = removePossibleSqlInjection(@variable)

How do you do that?

Answer 1

Is there some SQL function which will check the stored procedure parameter variable and tell me if there are some illegal characters ?

There is no such function and it just cannot be

Simply because there are NO "characters that can cause sql injection". All characters used in injection are perfectly legal. Your idea of SQL injection is wrong. It is not something alien to the query, like a virus or a bacteria, but just regular SQL. So all you can do is to forbade characters that are used in SQL queries, which will make this function effectively wipe your query.

What character from 'password OR 1=1' statement you consider illegal?

Answer 2

Let us consider you have a form where users can ask for public data of their friends. Let us suppose further that the form posts an ID and you use that as a numeric value in your query:

select public_details
from users
where ID = 5

ID is a value you get from the user. It is not safe at all to allow users to choose the ID they are searching for, but let us ignore that for now for the sake of the example. Now if the user sends a post as follows

5 or 1=1

There is no illegal character, not even apostrophe. The problem is therefore that this is a business-logic issue and should be addressed at application level.