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?
It has been a long time but with the help of a coworker I did finally find one instance of this pattern that we were able to break out of and show that it was insecure. The specific scenario was in a website running ASP.Net 4.8 and concatenating a T-SQL query from user input. If the "replace" sanitization is done in dotnet and the SQL parameter is `VARCHAR` we were able to pass in Prime (U+2032) which bypassed the dotnet string replace and was implicitly converted to Apostrophe (U+0027) by Sql Server.
To anybody who finds this question or answer, input sanitization is not safe or reliable for security purposes. When in doubt, there will be another, safer, more correct way. It just takes one oversight, server setting, collation difference, or other error to expose potentially all data in your production database to anybody clever enough to find it.