Is it possible to exploit a query via SQL Injection when the input is stripped of all apostrophes?

Question

I have been converting insecure dynamic queries to parameterized queries. I have figured out ways to exploit most of the weaker attempts people have made in an attempt to sanitize their input but I have not figured out how to actually exploit a query that is essentially

DECLARE @input VARCHAR(100) = ''';SELECT ''INJECTED''--'
DECLARE @SQL NVARCHAR(100) = 'SELECT ''example'' WHERE ''1'' = ''' + REPLACE(@input, '''', '') + ''''
EXEC sp_executesql @SQL

or

DECLARE @input VARCHAR(100) = ''';SELECT ''INJECTED''--'
DECLARE @SQL NVARCHAR(100) = 'SELECT ''example'' WHERE ''1'' = ''' + REPLACE(@input, '''', '''''') + ''''
EXEC sp_executesql @SQL

I have seen people online break escaping (in MySQL but never TSQL) but I have not been able to find any way to break stripping single quotes.

I plan on fixing the above instances, too, but I cannot figure out how to demonstrate that they are insecure.

How can you exploit the above queries?

Answer 1

As others mentioned, trying to cleanse the input is the most fraught option available. Best option is to never let the input become executable code...

Here's a safe pattern for VARCHAR(MAX) lists in params. It does require STRING_SPLIT or equivalent function (SQL Server 2016 and higher. Not sure about other SQL flavors)

CREATE PROCEDURE dbo.SelectSomeListOfIDs (
    @AnyDelimitedIDList VARCHAR(MAX)
)
BEGIN

    --The idea here is to make the list creation flexible and non-brittle for non-technical users and external calls.  Overkill for most scenarios.  Depending on the expected data, some delimiters should definitely be taken out.
    REPLACE @AnyDelimitedIDList = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        @AnyDelimitedIDList
        ,CHAR(09),'|')  --Tab
        ,CHAR(10),'|')  --LF
        ,CHAR(13),'|')  --CR
        ,CHAR(44),'|')  --Comma
        ,CHAR(59),'|')  --SemiColon
        ,CHAR(58),'|')  --Colon
        ,CHAR(46),'|')  --dot
        ,CHAR(34),'|')  --DoubleQuote
        ,CHAR(39),'|')  --SingleQuote
        ,CHAR(32),'|')  --Space

    --this enables the input to never become executable code
    DROP TABLE IF EXISTS #IDList
    SELECT ID = x.value
    INTO #IDList
    FROM STRING_SPLIT(@AnyDelimitedIDList,'|') x
    WHERE ISNULL(x.value,'') <> ''

    --Obviates the need for Dynamic SQL 
    SELECT ID
    FROM dbo.MyTable x
    JOIN #IDList  list
        ON x.ID = List.ID --safe from sql injection 

    --Dynamic SQL is no longer needed for the sake of the ID List, but if Dyn is still needed/desired, the list can still be simplified and made safe:
    DECLARE @dummyScalar DATETIME = GETDATE()
    EXEC ('
        SELECT ID
        FROM dbo.MyTable x
        JOIN #IDList  list --safe from sql injection, and a bit simpler
            ON x.ID = List.ID
        WHERE x.date > '''+@dummyScalar+'''
    ')

END

Answer 2

You can and should still parameterize your dynamic sql to make it truly safe. Your example query could be something like this.

declare @z int = 10

EXEC sp_executesql 'SELECT x FROM y WHERE MyColumn = @z', N'@z int', @z

Now we have created a parameterized query inside your dynamic sql and passed the parameter to the execution. This is NOT vulnerable to sql injection.

--EDIT--

Since you wanted an answer to the question of if your original query is vulnerable to sql injection I will demonstrate that it is in fact quite open. You are only dealing with single quotes which is truly on the tip of the iceberg when it comes to sql injection. Have you considered what happens if instead of a string they pass in a binary representation of a string?

Let's say your query is receiving a nvarchar(max) as a parameter. In my example I call this @BadCode. Now in my example I am not doing any harm to your system but in the wild this binary could be literally anything.

Here is a simple stored procedure with a pattern very close to what you demonstrated.

create procedure InjectionTest
(
    @BadCode nvarchar(max)
) as
    set @BadCode = REPLACE(@BadCode, '''', '')
    EXEC sp_executesql @BadCode

GO

Now from the front end I am going to pass in the value 0x730065006C0065006300740020002A002000660072006F006D0020007300790073002E00640061007400610062006100730065007300. Again, this is harmless binary here. If you want to see simply put that inside a convert(varchar(max),...

Here is an example of the above procedure being called. The binary string coming in is what the user would pass in. Notice there are no single quotes in that.

declare @Test nvarchar(max) = 0x730065006C0065006300740020002A002000660072006F006D0020007300790073002E00640061007400610062006100730065007300

exec InjectionTest @Test

There are plenty of much longer explanations and more trickery but this demonstrates the basics of how this can easily be broken.