Home SQL Server Archive by category "Dynamic SQL"

Category: Dynamic SQL

sql_variant parameters and Dynamic SQL

Let’s start off by saying that this article is not a recommendation to use sql_variant as a data type. There are many articles, written by far more reputable people, that have explained why sql_variant isn’t a good choice of a data type. Saying that, however, if you are using dynamic SQL then yes you could pass one as a parameter to have that dynamic statement correctly cast that parameter to the correct data type. For a "catch all" query, where you are passing a both a dynamic column and value that could have different data types this permits you to…

Continue reading

An in-depth look at injecting

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…

Continue reading

Getting to grips with Dynamic SQL: Debugging

Something that many find difficult with Dynamic SQL is debugging it. When looking at a batch that creates and executes a dynamic statement it can be daunting to understand where exactly the error is happening, or even where the SQL that is generating the error is coming from, as it might be a value that was injected, rather than part of the literal strings. I’ve touched on this before, but to reiterative Formatting is Important. That doesn’t just mean with your statements that are creating the dynamic SQL, it means ensuring that the dynamic SQL you create is well formatted…

Continue reading

Getting to grips with Dynamic SQL: Working with datasets

Introduction Previously I covered how to validate the values that are being received by your dynamic SQL to help ensure that malicious code is even harder to be run. In this article I’m going to cover how you can use a dataset to create a dynamic statement, or dynamic statements. This will likely be as you want to perform a specific task against several objects in your database, or server, and instead of writing the same statement for each object you use a dynamic one to do the work. These are, in my opinion, the more valid reason for dynamic…

Continue reading

Getting to Grips with Dynamic SQL: Validating inputs

In the last article I touched on how you would build a dynamic query which is likely, in the end, accepting an input from a user to produce a dynamic statement. These work well but often, for added security, it’s best to also validate the input value prior to executing the statement. This either means that the dynamic SQL isn’t executed at all, or you can choose to return a custom error message. Validating Objects When using dynamic objects, then use you use the sys or INFORMATION_SCHEMA objects to check that the object does intend exist. Personally I prefer the…

Continue reading