Injecting into SQL is something I have covered multiple times, but today I wanted to cover it a bit more in full to touch on why doing it incorrectly is a problem, and also the different ways to inject properly. Some of this will definitely be repetition of stuff I've said before, but having I felt that having it in one article isn't a bad thing.
When SQL Injection goes wrong
The biggest problem with injecting is doing it incorrectly, and thus opening your instance up to SQL injection attacks. For those of you that you that don't know what SQL injection attacks are, this allows for someone whom wouldn't normally be able to run statements against your instance run them. This could be something as simple as returning data from a table that they don't have the user permissions to get to in the application, or it could be (if the LOGIN has more permissions that it should) something like create a brand new LOGIN with sysadminprivileges. What ever happens, very likely someone that manages to inject successfully on your environment is going to cause a data breach very shortly afterwards; in today's world that can be disastrous.
A simple example of something that could be injected into would be something like following poorly written dynamic statement:
CREATE PROC dbo.InjectionProc @TableName sysname, @ID int AS
BEGIN
DECLARE @SQL nvarchar(4000) = CONCAT(N'SELECT * FROM dbo.[', @TableName, N'] WHERE ID = ', @ID, N';');
EXEC (@SQL);
END;The injection point here is the parameter @TableName. @ID isn't one, due to it's data type, however, that doesn't make the way it was used correctly; parameters should be parametrised not injected (you'll see examples of that later, though it is not the aim of this article). Here someone malicious could pass a value to perform many other operations, and no the brackets ([]) around the parameter don't help. One of the first things someone trying to perform an injection attack will do is try to escape out of the delimit identifier. For an object that'll be brackets or double quotes (") and for a string parameter that'll be a single quote ('). So, for the above simply using a right bracket (]) will close them, and then after that the user is "free" to input what ever they like (within the 128 character restriction). So, for example, they could set the value of @TableName to 'RealTable]; CREATE LOGIN NewSA WITH PASSWORD = ''1'', CHECK_POLICY = OFF; ALTER SERVER ROLE sysadmin ADD MEMBER NewSA;--' and if the LOGIN being used in the connection has high enough privileges then a new SA will have been created on the instance.
Of course, for more complex dynamic queries, injecting isn't as simple, but that doesn't mean it isn't. I'm not, however, here to teach you have to perform SQL Injection attacks but how to avoid them. This, however, gives you a little bit of understanding of how they work so that when you are writing your own dynamic statements you know what you should be avoiding.
Injecting object names
Injecting object names, in my opinion, is the easiest thing to get right. That's because T-SQL has a function that does does this for you, QUOTENAME. QUOTENAME accepts 2 parameters, the first is the value to be "quoted" (delimit identified), and the second is the character used to do said quoting, which is optional. By default, if the second parameter is omitted, then it will default to brackets ([]).
The data type of first parameter for QUOTENAME is sysname, which is a synonym for nvarchar(128) NOT NULL. This is an important early thing to note, as it means it can't be used to quote long strings. The return type also reflects this, as it's nvarchar(258) ((128 * 2) + 2 which makes more sense as you read this article). The second parameter is a char(1), and only accepts certain characters as well; if you pass a character it doesn't support the function will return NULL. The acceptable characters are the following:
- Brackets (
[]) - A single quote (
') - A double quote (
") - Parenthesis (
()) - Greater Than/Less Than(
><) - Braces(
{}) - A backtick(
`)
Most of the time, when injecting an object's name, you're therefore going to omit the second parameter, as you want to use Brackets, however, you may well find that you're injecting the object name for a dynamic query being run on a different RDBMS, such as Oracle or MySQL; then you would be making use of the double quotes or backtick. You may also want to inject it as a string, thus using the single quote, but I'm going to intentionally cover that separately. Therefore the most likely syntax you're going to have for your object's name is QUOTENAME({Table Name}) (obviously replacing {Table Name} with the variable/parameter/column that contains the value for your dynamic object. For the prior example, your query would therefore look something like this:
CREATE PROC dbo.InjectionProc @SchemaName sysname = N'dbo', @TableName sysname, @ID int AS
BEGIN
DECLARE @SQL nvarchar(MAX) = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WHERE ID = @ID;';
EXEC sys.sp_executesql @SQL, N'@ID int', @ID;
END;You'll note that I also changed the prior procedure a couple of other ways. Firstly adding a parameter for the Schema; though not required it "future proofs" the process in case you ever need to handle multiple schemas. You can see in the above you need to wrap each part of the object's name in QUOTENAME as well, not the whole thing (as that would wrap the 2+ part name in brackets, not each individual part) and you need to ensure you put the dot (.) between each object part (as you normally would). I also move to using sys.sp_executesql, and with parametrisation, as it's far better practice.
Object names as a string
I wanted to cover this under a separate heading, as when you inject an object's name as using single quotes (') there can be a bit of a "gotcha" moment. Object names, as I mentioned earlier, use the data type sysname, which remember is a synonym for nvarchar(128) NOT NULL. This is important, as an nvarchar literal needs to start with the notation character N; for example N'YourTable'. For many of us, not using the notational character isn't a problem, as we don't have characters in our object names that use characters outside of the code page defined in the database's collation for a varchar. Some of us, however, this isn't true for and so could be problem. Let's assume, for example, that I have received the DDL and DML for a table from someone I know that doesn't use a Latin based language, and they have a table named Πελ?της (which according to Google is Greek for "Customer"). If you want to inject the value as a string into a dynamic value, using N'SELECT ' + QUOTENAME(@TableName,'''') is not going to give you the result you want with the wrong code page; it'll result in SELECT '?e??t??'.
When injecting your object names as a literal string, you actually need to ensure that you put the notation character in your dynamic SQL. So for the prior simple example, that would be N'SELECT N' + QUOTENAME(@TableName,''''). I admit, this looks a little odd, however, ensures that no data/characters are lost if the value was explicitly declared as a varchar within the dynamic statement when it contains characters outside of the code page.
If we were to expand the prior procedure to include the object's name in the columns, then it could look something like this:
CREATE PROC dbo.InjectionProc @SchemaName sysname = N'dbo', @TableName sysname, @ID int AS
BEGIN
DECLARE @SQL nvarchar(MAX) = N'SELECT CONCAT(N' + QUOTENAME(@SchemaName,''''), ',''.'',N' + QUOTENAME(@TableName,'''') + N') AS ObjectName,' +
N', * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WHERE ID = @ID;';
EXEC sys.sp_executesql @SQL, N'@ID int', @ID;
END;You'll notice that putting the entire object's name into a single column, with 2+ naming is pretty messy. I personally don't recommend doing it that way, and instead having a separate column for the object, schema, and database (and linked server if needed). Wrapping the entire lot in a single QUOTENAME could easily truncate the value if you have a couple of lengthy object names. If you really want it as a full string, then perhaps define the parts as separate columns in a VALUES table construct or a CTE, and then concatenate the values in the SELECT as it'll be far "cleaner".
Injecting values that aren't objects
Though you can use QUOTENAME for values that aren't objects, you do need to remember that the string value it accepts is 128 characters in length. If you have a value that could be longer, then you should not be using the function, as you could easily experience truncation issues. As a result you need to use a different method, and the simplest is actually to use REPLACE when quoting longer values, and to concatenate the quoting character(s) to both end.
The way to use REPLACE to ensure that you properly quote values if to escape the character you are using are the delimit identifier, or "double them up". If you are using an identifier that has both left and right characters, such as brackets ([]) or parenthesis (()), then you only need to escape the right character (] and ) respectively); do not escape the left one as that'll actually cause you syntax errors.
So, if you're using the default character for QUOTENAME, brackets, you would only need to use REPLACE to escape the right bracket. This would result in an expression like CONCAT(N'[', REPLACE(@LongString,N']',N']]'),N']'). For a single quote, due to having to already escape them, when writing it literally, it looks quite "messy", but the correct syntax would still be CONCAT(N'''',REPLACE(@LongString,N'''',N''''''),N'''').
Provided that you escape the correct character, then this'll greatly help reduce the exposure your dynamic SQL scripts have to injection. We could use this method to quote the full object name, as this would avoid injection issues:
CREATE PROC dbo.InjectionProc @SchemaName sysname = N'dbo', @TableName sysname, @ID int AS
BEGIN
DECLARE @SQL nvarchar(MAX) = N'SELECT N' + CONCAT(N'''', REPLACE(@SchemaName + N'.' + @TableName,N'''', N''''''), N'''') + N' AS ObjectName,' +
N', * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WHERE ID = @ID;';
EXEC sys.sp_executesql @SQL, N'@ID int', @ID;
END;Injecting for operators that require literals
Some parts of T-SQL require you to pass a literal string, you can't use an expression or variable. For example if you're using OPENQUERY, you can't have syntax like SELECT * FROM OPENQUERY(@LinkedServer, @SQL);'. This also means you can't parametrise them in the same way you would when using a dynamic query, as they don't accept variables being passed to them. For queries like this you have to first create your dynamic statement, and then inject it into a further dynamic statement. This can be a little "daunting" for some, but it's actually not really any harder than what I've already covered here; we just need to combine what I've already discussed.
The first thing is the build the inner dynamic statement; in the little prior example that would be the SQL inside @SQL. You would do that very similarly to the above, however, you will need to ensure that you inject any parameters as literals as well. When doing so it's therefore very important to ensure you take note that the data type of the column you are passing the value to, as you're going to need to inject everything as a string value. If you are therefore passing something like a date (and time) value, you'll need to ensure you wrap the value in single quotes, and also inject an unambiguous date format using CONVERT and a style code. Most likely you are going to want to use the style yyyyMMdd (112) or yyyy-MM-ddThh:mm:ss.nnnnnnnn (126, or 127 if you want "Z" appended for UTC). For numerical data types (excludiing float/real) then you can simply convert the value, without including quotes. Note, however, that that isn't an excuse to incorrectly decalre the parameter type in your calling SQL; if your comparison column is an int then declare the parameter/variable as an int and then explicitly convert in the SQL to create the dynamic statment. Using the wrong data type for the parameter/variable reopens injections paths.
So, to generate the SQL that needs to be run at the remote instance, you might end up with something like this:
--Example parameters
DECLARE @ClientName nvarchar(50) = N'Mc''Fadden',
@Age int = 18,
@Registered date = '20210101',
@RemoteServer sysname = N'MyLinkedServer',
@TableName sysname = N'MyTable';
DECLARE @SQL nvarchar(4000), --OPENQUERY is limited to 8,000 bytes, so can't use MAX
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT *' + @CRLF +
N'FROM ' + QUOTENAME(@TableName,'"') + @CRLF + --Using double quotes, as might not be a SQL Server instance
N'WHERE ClientName = N''' + REPLACE(@ClientName, N'''', N'''''') + N'''' + @CRLF + --QUOTENAME would also be safe here, as @ClientName is under 128 characters
N' AND Age >= ' + CONVERT(varchar(3), @Age) + @CRLF +
N' AND Registered <= ' + QUOTENAME(CONVERT(varchar(8),@Registered,112),N'''') + N';'; --QUOTENAME is safe, as under 128 charactersFor the above, this results in the following statement:
SELECT *
FROM "MyTable"
WHERE ClientName = N'Mc''Fadden'
AND Age >= 18
AND Registered <= '20210101';This gives us the statement we want to execute at the remote server, however, as I mentioned, we can't just pass @SQL to OPENQUERY and expect it to work; it requires a literal. As a result we need to put the above into a further dynamic statement, and then execute that. I've also, for this example, made it so that the Linked Server's name is also a dynamic value. As a result, you need to (again) replace things like single quotes in your already dynamic statement; thus doubly them up again.
So, after the above SQL, you would add the following to get your final dynamic statement:
DECLARE @Query nvarchar(MAX); --This can be MAX length
SET @Query = N'SELECT *' + @CRLF +
N'FROM OPENQUERY(' + QUOTENAME(@RemoteServer) + N',N''' + REPLACE(@SQL, N'''',N'''''') + N''') OQ;';To finally execute the statement you would pass the value of @Query to sys.sp_executesql, and you "parametrised" statement would be executed against your dynamic linked server.
Of course, it's probably unlikely you'd use such SQL in a real world environment, however, this demonstrates the methods if you do run into a scenario where you need to pass a literal for your SQL, rather than a SQL statement/batch within a variable.
UDFs to quote strings
Using REPLACE and concatenation can be a little cumbersome, or "ugly" to look at, thus what you could do is create some functions to quote longer strings for you instead. I, personally, have created 4 to handle varchar and nvarchar values separately, as well as non-MAX and MAX length datatypes. This avoids any unexpected implicit conversions. I've created them as scalar functions, though they would be easy enough to turn into inline table-value functions. As they make no calls to tables, etc, they can easily be inlined in SQL Server 2019 and should still be performant in older versions. To keep behaviour the same, they only support the same characters that QUOTENAME does, however, both parameters are mandatory. Please feel free to deploy these to any of your instances.
CREATE OR ALTER FUNCTION dbo.QuoteVarchar (@String varchar(8000), @Character char(1))
RETURNS varchar(8000)
AS BEGIN
--Opening Character
RETURN CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = ']' THEN '['
WHEN @Character = ')' THEN '('
WHEN @Character = '}' THEN '{'
WHEN @Character = '>' THEN '>'
ELSE @Character
END +
--Escape characters
REPLACE(@String,
CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = '[' THEN ']'
WHEN @Character = '(' THEN ')'
WHEN @Character = '{' THEN '}'
WHEN @Character = '<' THEN '>'
ELSE @Character
END,
REPLICATE(CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = '[' THEN ']'
WHEN @Character = '(' THEN ')'
WHEN @Character = '{' THEN '}'
WHEN @Character = '<' THEN '>'
ELSE @Character
END,2)) +
--Closing Character
CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = '[' THEN ']'
WHEN @Character = '(' THEN ')'
WHEN @Character = '{' THEN '}'
WHEN @Character = '<' THEN '>'
ELSE @Character
END;
END;
GO
CREATE OR ALTER FUNCTION dbo.QuoteNvarchar (@String nvarchar(4000), @Character nchar(1))
RETURNS nvarchar(4000)
AS BEGIN
--Opening Character
RETURN CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N']' THEN N'['
WHEN @Character = N')' THEN N'('
WHEN @Character = N'}' THEN N'{'
WHEN @Character = N'>' THEN N'>'
ELSE @Character
END +
--Escape characters
REPLACE(@String,
CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N'[' THEN N']'
WHEN @Character = N'(' THEN N')'
WHEN @Character = N'{' THEN N'}'
WHEN @Character = N'<' THEN N'>'
ELSE @Character
END,
REPLICATE(CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N'[' THEN N']'
WHEN @Character = N'(' THEN N')'
WHEN @Character = N'{' THEN N'}'
WHEN @Character = N'<' THEN N'>'
ELSE @Character
END,2)) +
--Closing Character
CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N'[' THEN N']'
WHEN @Character = N'(' THEN N')'
WHEN @Character = N'{' THEN N'}'
WHEN @Character = N'<' THEN N'>'
ELSE @Character
END;
END;
GO
CREATE OR ALTER FUNCTION dbo.QuoteVarcharMAX (@String varchar(MAX), @Character char(1))
RETURNS varchar(MAX)
AS BEGIN
--Opening Character
RETURN CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = ']' THEN '['
WHEN @Character = ')' THEN '('
WHEN @Character = '}' THEN '{'
WHEN @Character = '>' THEN '>'
ELSE @Character
END +
--Escape characters
REPLACE(@String,
CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = '[' THEN ']'
WHEN @Character = '(' THEN ')'
WHEN @Character = '{' THEN '}'
WHEN @Character = '<' THEN '>'
ELSE @Character
END,
REPLICATE(CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = '[' THEN ']'
WHEN @Character = '(' THEN ')'
WHEN @Character = '{' THEN '}'
WHEN @Character = '<' THEN '>'
ELSE @Character
END,2)) +
--Closing Character
CASE WHEN @Character NOT LIKE '[\[\]''"()><{}`]' ESCAPE '\' THEN NULL
WHEN @Character = '[' THEN ']'
WHEN @Character = '(' THEN ')'
WHEN @Character = '{' THEN '}'
WHEN @Character = '<' THEN '>'
ELSE @Character
END;
END;
GO
CREATE OR ALTER FUNCTION dbo.QuoteNvarcharMAX (@String nvarchar(MAX), @Character nchar(1))
RETURNS nvarchar(MAX)
AS BEGIN
--Opening Character
RETURN CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N']' THEN N'['
WHEN @Character = N')' THEN N'('
WHEN @Character = N'}' THEN N'{'
WHEN @Character = N'>' THEN N'>'
ELSE @Character
END +
--Escape characters
REPLACE(@String,
CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N'[' THEN N']'
WHEN @Character = N'(' THEN N')'
WHEN @Character = N'{' THEN N'}'
WHEN @Character = N'<' THEN N'>'
ELSE @Character
END,
REPLICATE(CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N'[' THEN N']'
WHEN @Character = N'(' THEN N')'
WHEN @Character = N'{' THEN N'}'
WHEN @Character = N'<' THEN N'>'
ELSE @Character
END,2)) +
--Closing Character
CASE WHEN @Character NOT LIKE N'[\[\]''"()><{}`]' ESCAPE N'\' THEN NULL
WHEN @Character = N'[' THEN N']'
WHEN @Character = N'(' THEN N')'
WHEN @Character = N'{' THEN N'}'
WHEN @Character = N'<' THEN N'>'
ELSE @Character
END;
END;
GOA call to the functions would look very similar to that to QUOTENAME:
SELECT dbo.QuoteNvarchar(N'My bad [Table] Name',N']'); --[My bad [Table]] Name]
SELECT dbo.QuoteNvarchar(N'My bad [Table] Name',N'['); --[My bad [Table]] Name]
SELECT dbo.QuoteNvarchar('Mc''Fadden',''''); --'Mc''Fadden'
