Migrate SQLPrompt Snippets to VSCode

 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr

Why QUOTENAME Works

One of the core ways of making dynamic sql safe is the use of the QUOTENAME() function. QUOTENAME wraps either square brackets (default), double quotes, or single quotes around a provided string. This is crucial for dynamic SQL because if, for example, you need to concatenate a column name, the only way to make it 100% safe is to wrap it in square brackets so it's treated as an identifier. Conceptually the same as doing this:

select [Not A Valid Column] = @@version

Background
Naively you might think then, that if you had a dynamic sql string like this, you would be safe, since you're wrapping brackets around the identifier
declare @ColumnName nvarchar(128) = ''' union all select ''injected code'';--'
declare @SQL nvarchar(max) = 'select [' + @ColumnName + '] from sys.columns'

select @sql
exec (@SQL)
/* -- dsql string
select [' union all select 'injected code';--] from sys.columns
*/


And in this example, you are. The whole injected code gets evaluated as a column, and it fails. But that's not the whole story. The problem is, that I can insert an closing square bracket which will terminate the identifier region, and then inject my code, like so:
declare @ColumnName nvarchar(128) = 'a] = '''' union all select ''injected code'';--'
/* -- dsql string
select [a] = '' union all select 'injected code';--] from sys.columns
*/

Now we have a problem. I was able to close the identifier region, and now I can do whatever I want.

But getting back to the original point, QUOTENAME makes this attack impossible. I never knew exactly how, other than the fact that it worked where typing the brackets did not. I found out why by accident. Let me ask you a question: What will this return?

select quotename('[ColumnName]')

You would be forgiven for thinking it would be [[ColumnName]]. As it happens, the reason QUOTENAME works, is that whatever the quoting character is (the second argument to the function, or, if none is provided, right square bracket) is escaped in the string. So instead you see [[ColumnName]]]; three square brackets. Similarly if you ran this...

select quotename('"ColumnName"', '"')

...you would get """ColumnName"""!

And that's the secret to it working. QUOTENAMEguarantees your string will start off with either a square bracket, a double quote or a single quote. The first two can be used as object identifiers, and the last two can be used as string identifiers, but the can't be mixed and matched. From then on out, the only way to introduce unexpected behavior is to somehow break out of that identifier or string to do something else.  And the only way to do that is to terminate the opening character with a single, unescaped closing character. So QUOTENAME auto-escapes any of the chosen character found in the string so those can't be introduced.

I encourage you to play around with this; try different combinations of single and double quotes. Turn quoted_identifier on and off. Try to sneak in some varbinary cast or a char(39), or a series of nested DSQL statements. You'll likely find it's impossible to do (I say likely because if there was a loophole, it seems like it would be widely known about and fixed. That said, the single quote case still makes me thing there's a way you might be able to get around it; although I haven't found any such way).

So I guess be aware of that behavior too, but you can also use it to your advantage to save time as well. QUOTENAME is more than just a way to safely escape concatenated dynamic SQL. It can also serve as a means to avoid having to wrap escaped single quotes around strings you're putting into a database, or pulling out. It's just an all around awesome function.


Comments

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

Left-Padding Zeroes

Fixing Git "Unexpected Disconnect while reading sideband packet"