Posts

Showing posts from March, 2018

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
Post a Comment
Read more

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
Post a Comment
Read more