Hot Tips For Safer Dynamic SQL In SQL Server

Hot Tips For Safer Dynamic SQL In SQL Server

---

Thanks for reading!

Video Summary

In this video, I delve into the world of dynamic SQL security and how to keep your data safe from potential threats like SQL injection. I walk through a practical example where we create a table called `DropMe`, insert a row, and then execute a query that inadvertently drops the table due to SQL injection. This serves as a stark reminder of why it’s crucial to parameterize dynamic SQL properly. I also discuss how to safely handle cases where you need to pass in column names or other schema-related elements using SQL Server’s system views like `sys.columns`, `sys.tables`, and `sys.schemas`. By leveraging these views, we can ensure that only valid objects are used within our dynamic SQL queries, significantly reducing the risk of accidental or malicious commands being executed. Additionally, I touch on some practical tips for maintaining security, such as using string functions like `STRING_SPLIT` (available in SQL Server 2016 and later) to safely parse input parameters and logging invalid inputs for further review if needed. Overall, this video provides a comprehensive guide on how to secure your dynamic SQL queries against potential threats while ensuring they remain performant and efficient.

Full Transcript

Erik Darling here with Darling Data, and my microphone is on, but the sound bar just looks funny. Okay, well, we’ll deal with that, hopefully. Anyway, today was my oldest daughter’s fifth grade graduation, which I know sounds a little weird. A lot of people just have like a kindergarten and then an eighth grade, but, you know, in New York they shuffle you from a K-5 to a 6 to 8 school, so fifth grade is apparently a big deal celebration here. And, of course, you know, being here in America, they sang, they do the Pledge of Allegiance and they sang the National Anthem with the Star Spangled Banner and it kind of, kind of struck me sitting there in the audience that, um, there are a few words in the Star Spangled Banner that, um, people, they, like, aren’t common words anymore and just people might not know the meaning of. Like, uh, Gallantly or Rampart or even Spangled might send some people running for the dictionary. Who knows? Kind of, I don’t know, might need, might need some, uh, might need to throw some TikTok lingo in there if we want the Star Spangled Banner to remain a true top 40 hit. So, anyway, uh, in this video, uh, from Erik Darling of Darling Data, we’re going to talk about how you can keep your Darling Data, uh, safer, uh, in the, in the face of Dynamic SQL. And I’m going to show you a few things that I, I will sometimes do when I’m, when I’m working with Dynamic SQL to make sure that no, there’s no, there’s no, there’s no hijinks, there’s no shenanigans, there’s no tomfoolery going on and, and when, when, when queries get executed. So, uh, just, just the first thing we’re going to do is a small primer on, uh, the, the perils, the potential perils of the data.

So, uh, we’re going to create a table called DropMe. We’re going to insert a row into DropMe. And what we’re going to do is just, just run a very simple query where someone has SQL injected a drop table command onto a, onto a, onto a per, onto an input. And we’re going to execute that. And we’re going to see that, uh, at the very end of this, we no longer have our table called DropMe. In fact, we have, we have an error message saying invalid object name DropMe. And we, the reason we have that, that error message is because this is what our, our executed SQL string looked like. We had a totally valid select query, which executed, and I’ll show you that in a second.

And then after that ran, we had a drop table command. Also run. That’s, that’s unfortunate. If we go over to the results though, uh, we will see, well, there’s the result of our first select from DropMe up there. And then here’s the, here’s the result of our, oh, that jumped around a little bit. So you Dickens, zoom it. There’s the result of our first query. And there’s the result of our database name search. And you can see all the stack overflows that I have, uh, on my server, right? Obviously not good.

And obviously the solution is to parameterize the dynamic SQL. And so if we, we sort of rerun that portion of the script again, when we rewrite our dynamic SQL in a safe way, we will get no error messages, but more importantly, we don’t get any search results, which is, which is a good thing, right? Why is it a good thing? Because we didn’t execute an unsafe SQL command, right? I mean, I don’t think it’s a very, very, uh, prodigious way of working through a SQL Server.

If you, you’re okay with user searches running, uh, and returning results and also executing out of bounds commands. So if we look over in the messages tab now, there’s a printout of what we put into our, our database name, right? It’s just S percent wildcard, you know, any old thing that blows after that, uh, and a drop table command.

But in here, this, this looks different now, doesn’t it? We just have the parameter name. We don’t have that string sitting in there. And that’s great for things that you can actually parameterize.

There are lots of things in SQL Server that you can’t parameterize. At least not, like, in a meaningful way. Uh, that, uh, you might need to pass into dynamic SQL.

Like, you might, you might need to pass in column names or a schema name or a table name or even a database name. And none of those things can really be supplied as parameters to dynamic SQL. It’s not allowed.

So, here’s, here’s some stuff that I do to make sure that when, for cases where that, that does have to happen, uh, we, we stay safe. So, uh, SQL Server has all sorts of views in it that enumerate different objects. Like, we can enumerate databases from sys.databases and half a dozen other views.

Uh, we can enumerate, uh, schema from sys.schemas. We can enumerate tables from sys.tables. And we can enumerate columns from sys.columns.

So, when, if I’m going to allow someone to pass in a list of columns or a list, like, a schema and a table or a list of columns, that they want to return from a user query, I need to make sure that those are all safe as well. So, what I, what I’ll do in those cases is, uh, declare some placeholders in here.

And then I’ll do stuff like lookup the schema name and table name that got passed in. And since this, this isn’t dynamic SQL, we don’t have to worry about any filthy, dirty inputs in here. And then I’ll set the, uh, the safe schema and table names, uh, with quote name, because quote name is very important.

Um, uh, I, I was working with a database that had, database name that had a space and a dot in it earlier this week. And that was a real adventure in a number of ways. Um, um, found myself using a lot of parentheses for that one.

Uh, and this will make that safe. And then what, what’s nice to do is if you’re going to, if you’re going to use something like this over and over again, uh, rather than constructing this constantly, uh, just, you just have like one, like, thing that you concatenate the schema name and the dot.

And, you know, one of my big gripes about dynamic SQL is that, or rather dynamic SQL and SQL Server is that like, there’s no, there’s not like enough like typing for it. I mean, not like there’s a lot of like keyboard typing, but there’s no like, like types built into SQL Server to handle stuff like this.

So like, it would be cool if there was, you know, like, like an object type or something where if you passed in, like, you know, like a, like a table or a schema or a database or whatever else, that you would get back like a constructed object with, with the right, the dots in the right places. That’d be cool. It’d be nice if you could just, if you could, if you could, there was some constructor for that.

Like quote name, you, you, you pass something into that and it puts quotes around. It’d be nice if there was something to put dots in the right places too, so that you didn’t have to do foolish things like this and realize that you forgot a dot somewhere. You left a dot in and you didn’t mean to leave a dot and it becomes quite a hassle.

There’s a reason why dynamic SQL is, is a real art, is, is practice as a, is a true art form by few. A lot of hacks out there, a lot of hackers out there, but not a lot of true dynamic SQL artisans. And then I’m going to, because, you know, I’m, I’m, I’m living in the future here on SQL Server 2022, but you know, this, we have this string split function, which came around in SQL Server 2016.

And we have this string ag function, which came around in SQL Server 2017. The problem is, and this is where Microsoft really screws up again, is that you have to be in the database compatibility level that the function was released in, in order to use it. So to be, to use string split, you have to be in compat level 130 or better.

To use string ag, you have to be in 140 or better. And it doesn’t help. Now, now Microsoft was cool from like a query optimization standpoint, where it was like, oh, we’re going to give you all these use hints to like specify which like, like compatibility level you want the database, this query to be optimized in, in this database. Which allows for like, you know, some neat stuff to happen.

Like in 2016, you can like do a parallel, like insert select into a temp table or any other table, but temp table is particularly helpful for. And like, you know, like 140, 150, 150 and up, you can get all sorts of like batch mode on rowstore type stuff and other neat things, right? Like, you know, batch mode or memory grant feedback and other intelligent query processing features, which are lovely.

But, um, not, not these for some reason. For some reason, changing the compatibility level of the query doesn’t unleash these functions. So if you are using a really old version of SQL Server or a really old compatibility level, you are going to have to go the old school route of, rather than using string ag, you’re going to have to use some XML hijinks to construct whatever list, whatever comma separated list of things you want.

And if you are not using, uh, the, the 2016 or better compatibility level, you will have to work out your own string splitting scheme. Now, normally I would be pretty picky about this, right? Because this is not a good SARGA-able thing.

I see a lot of queries with string split in them or like some string splitter in them. It doesn’t have, not necessarily the built-in one. Uh, whether it’s like a, it’s a, it’s a meaningful query, not just hitting some dynamic management views. And, you know, like it’s an aware clause or a join or something else.

And, uh, that’s, it’s not a good idea for performance because SQL Server has no idea what’s in there. And it makes no attempt at, like, um, like, like, like creating a statistics histogram for the results of a string split. So you can get some really bad cardinality estimates when you start getting that stuff involved in a where clause.

But since this is just a very simple query against the dynamic management view, um, I’m not gonna get picky here. Right? This is, like, I can deal with this.

Right? We’re not performance tuning in this one. We are, we are trying to keep our query safe from the hackers. We’re concerned about the security of our stuff. Right?

Not performance tuning in this one. And then, um, if any of these come back as null, someone clearly did something either devious or mischievous or maybe just wrong. Uh, there’s a quote about, like, malice and ignorance and all that.

I suppose that could, I suppose that could, that could apply here. I’m not, I’m not sure how I feel about it. But, you know, just some, some general handling.

And if you wanted to take this further, uh, you know, especially for, like, the column list, you could even, like, you know, have a table variable or some other logging feature. Some other logging thing happened where you, like, logged a list of things that someone passed in that were incorrect. Right?

If you really cared about it, you could totally do that. I don’t care enough to do that here. It’s just gonna make the code longer. But you could absolutely, like, you know, stick a, like, a list of stuff from the column names parameter, uh, that were invalid column names. Right?

It’s a totally doable thing. And then down here, so this is what I kind of meant by, like, some things can’t be passed in as parameters. Like, you couldn’t directly pass this to dynamic SQL as a parameter. SQL Server would say, I don’t know what you’re talking about.

Likewise, you couldn’t pass this to the, you can’t pass, like, you know, database schema object names, table names, stuff like that, without SQL Server saying, no. Can’t do it, not happen. And what this allows for is for if someone wants to select the top 100 rows from the user’s table, they can do that.

And the query will look like this. And one thing that is very nice for you to do in all your dynamic SQL is to put a comment with the store procedure name in there so people know where the hell these things come from. It’s not just some buck-naked query running around in your plan cache or query store that no one can, no one can identify.

But what’s cool about this is then if we were to run our store procedure with something devious, mischievous, and malicious in it like that, it would not show up in there. Right? Our messages tab would still just show the three columns that were actually valid for the user’s table and the comment list.

So, good tips on keeping your dynamic SQL extra safe. Right? And if we look at our drop-me table, it still exists despite someone’s best efforts to drop our poor drop-me table.

Right? Now, this is just one kind of example with object names, like, you know, schema, table, database, column name, stuff like that. You might have other instances where, you know, and I grant you, I grant you every grudge you have against this store procedure for not needing, not actually needing dynamic SQL.

But you might have a more involved case, and without really, like, doing something somewhat complicated, this is kind of just a good general example of, you know, an alternate thing you could do that might make life better, might actually perform better, might cut some crap out of your query plan, stuff like that. Where, let’s say we have a store procedure where we want to search display names in the user’s table of the Stack Overflow Data. You probably won’t be working on this particular store procedure, but you might, you know, let people search like this.

And you might have dynamic SQL. Well, again, an unsanitary dynamic SQL that looks a little something like this, where, you know, you’re just passing in some bare-naked parameters to a thing like this, which is, you know, obviously this is a horrible idea. You shouldn’t, you shouldn’t trust anyone this much.

Wouldn’t, wouldn’t, I wouldn’t trust us, I wouldn’t trust anybody with something like this. But, one thing that you might find useful to do is, rather than have all this stuff in your dynamic SQL, you might find it performs better, and it keeps you safer if you do something like this, where you just, you create a temp table, right, and then you enumerate just the IDs of the things you care about. You stick that into a temp table, and again, like, this doesn’t matter much, right?

If you want to throw a recompile hint on here, whatever you want to do, you can do it to tune up this query, but, you know, for the context of what we’re doing, this is pretty quick and fast. And just dump the contents into a temp table, right? Nice little primary key on there, too, right?

Look at our primary key. Look at our nice clustered primary key on this table. Isn’t it gorgeous? Most beautiful thing you’ve ever seen? I think so.

And then we can use that temp table within the dynamic SQL block, because when you, you know, it’s state, like, we still have that temp table created in this context, and we can execute that dynamic SQL referencing that temp table, which is created outside. Now, there’s funny things about creating temp tables inside of dynamic SQL, in that, like, the first one is that this is not reciprocal. So if you create a temp table inside of dynamic SQL, you can’t reference it outside of the dynamic SQL.

But if you create a temp table outside of the dynamic SQL, you can reference it inside of dynamic SQL, which is pretty cool. I like it. I’m into it.

I dig it. I can get on board with that. The other funny thing about creating temp tables inside of dynamic SQL is that it makes them not cacheable, which might be good or bad. Paul White has three magnificent, wonderful blog posts about temp tables and temp table caching in store procedures, stored procedures.

I end up sending people to those quite a bit, because they are, as Paul is wont to do, he has written wonderful, detailed, comprehensive blog posts about these topics. So, yeah, so, like, you might have an issue where, you know, you create a temp table and SQL Server caches that temp table and part of what it caches is the histogram. And then the next time you go to use that temp table or you go to run that store procedure, SQL Server reuses the histogram for that temp table, which is a wild thing.

Absolutely wild thing. But if you create a temp table inside of dynamic SQL like this, it is not cacheable, and the histogram or nothing else will be cached along with it. Now, that, of course, has upsides and downsides, right?

If you’re running a store procedure a ton, that temp table caching might be a good thing. You might find a lot less contention in temp DB if you, you know, are reusing cached temp tables. You might, you know, you might even find that performance isn’t that big a deal because you meet the thresholds to, like, uncache or whatever you want to call it and update the statistics on that temp table.

But, you know, in general, this is kind of like a more stranger angle on performance tuning an issue with temp table caching. But fun nonetheless, because every, sometimes you get to pull these weird tricks out and, boy, they, they work, work like gangbusters. I don’t, I don’t really like that phrase that much.

I don’t know. It’s kind of, again, it’s kind of old-timey. It’s like a, it’s like a gallant rampart. If my gangbusters on this gallant rampart are all spangled.

Anyway, that’s about all I had for this one. I think some useful tips on making dynamic SQL safer in your database. Again, you know, if you’re letting people pass in database, schema, table, column names, you’re much safer if you look those up in dynamic management views based on what people pass in and either give them the yes, no based on that.

If you, you know, if you’re using other stuff, right, if you’re not using object names and you’re still writing dynamic SQL like this, you are doing yourselves a bad, you are doing your company a bad, you are doing your data a bad. And you should really start writing parameterized dynamic SQL instead. You can follow along with the examples up above if you need to.

And if you, if you really want to start performance tuning stuff in dynamic SQL, you might even find that simplifying the amount of work done in the dynamic SQL and taking, you know, larger, more complex parts out and dumping just like simple lists of keys into a temp table and using that temp table in the dynamic SQL instead gets you far better performance. So anyway, thank you for watching. Hope you enjoyed yourselves.

Hope you learned something. If you like this video, thumbs, thumbs upsies and, you know, helpful praising comments are always welcome. If you think I messed something up or I’m wrong about something, you can, you can tell me that too.

But I didn’t mess anything up and I’m not wrong about anything. So you might, you might, you might, you might be unsatisfied with the response there. If you like this sort of SQL Server content, usually it’s performance tuning, but this is sort of like a hot dynamic SQL tips because here at Darling Data, we believe in hot SQL action.

So, you know, I got all sorts of useful stuff up my sleeve. Don’t, don’t, don’t, don’t make a judgment on account of my sleeves being short. I can, can stuff a lot of useful stuff in here.

But if you like this sort of SQL Server content, you can join nearly 3,700 and, hold on, let’s wait for it, 29 other dedicated, observant, faithful SQL Server or whatever you are, professionals, by subscribing. And I like subscribers because then I get to keep saying bigger numbers every time I do one of these. So that’s nice, that’s nice for me.

It’s a good, good pat on the back for all E-Darling of Darling Data. Anyway, it’s starting to, starting to get hot in here and I, I want to open the door and let the air conditioning in. So I’m going to, going to can this one, upload it and, and hope and pray that you watch it and that you, you follow my instructions here and that you, you don’t end up in the newspapers for, for bad reasons.

So anyway, thank you for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.