Learn T-SQL With Erik: Just Use QUOTENAME!

Learn T-SQL With Erik: Just Use QUOTENAME!

---

Video Summary

In this video, I dive into the exciting world of Dynamic SQL and why using `QUOTENAME` is far superior to simply adding square brackets in your code. I explore how `QUOTENAME` can protect you from potential SQL injection attacks and handle strange characters or names that might appear as column or object names. Additionally, I share some amusing bonus content at the end, including a clever workaround for dealing with very long dynamic SQL strings that are too big to print in one go. Whether you’re looking to enhance your T-SQL skills or just want a laugh, there’s something for everyone!

Full Transcript

It’s me, Erik Darling with Darling Data. And today’s video, we are going to go over some of the kind of fun things with quote name when you’re using Dynamic SQL and of course why it is far superior to the old, I’m just going to stick some square brackets in my code because that doesn’t give you the same level of protection. And then there’s going to be some rather amusing bonus content at the end, which I do hope you’ll enjoy because without your enjoyment, I die. No pressure. Anyway, if you look down in the video description, there are many important links in there. And they’re very clearly labeled places where you can hire me for consulting, places where you can buy my training and of course at a nice discount because you watch my videos and you know, I think I think, I think you’re a nice person and I think you deserve a break in life, especially if your employer is not willing to pay for training. It is difficult to afford the very, very high priced, often not worth it training out there on the internet. You can also become a subscribed channel member where you actually give me a small monthly donation in exchange for all of the wonderful content that I produce. You can ask me questions for my Office Hours episodes where as long as I’m going to be able to get a little bit more information, I’m going to be able to get a little bit more information.

As long as I count correctly, I answer five user questions. And of course, if you enjoy this content, I would encourage you to like subscribe and of course, tell a friend. The more friends you bring, the more people I have to talk to and gosh, then I might bring joy to more people and I might live longer, which is hopefully good for all of us. Speaking of things that are good for all of us, I’m getting out of the house. Actually, I’m getting out of the country a couple of times. Once, once that, oh, I mean, is Texas another country yet? I don’t know. Hard to keep track. So much going on in the world. But the nice folks at Redgate are dragging me around to Dallas, September 15th through 16th. Utrecht, October 1st and 2nd. And of course, past data community summit in Seattle from November, from the 17th to the 21st, where I will be hog tying and spit roasting two days of T-SQL pre-cons with Miss Kendra Little. So I do hope you attend those. Can’t wait to see you there. Again, no pressure. Might die if you don’t come. Wouldn’t be good for either of us. And then, I don’t know, whatever. So with that out of the way, let’s talk about some of the joys of quote name here. Now, I am in such a busy state that I need multiple SSMS spawns open. So please don’t judge me.

No, I’m not, I’m not here to bring pain to anyone. Anyway, important reasons why you need to use quote name. Well, well, it is somewhat unlikely that you will encounter someone naming a column, the clown emoji out in the world. You never know. You never know what kind of rough scallions you’re going to see out there. So what you need to do is protect yourself. Cause there are other things that people might do, like put spaces or dots or dashes or put weird things in, in, in, as column names that, that really cause issues. If they are not properly quoted in a way where SQL Server can identify the full text as an object name.

So let’s, let’s create this table, which, which, which happens successfully, even though we’re not using quote name here, but we do have to use it while we’re constructing dynamic SQL. That is if we’re smart. Now, one thing that I like to do when I’m trying to write very safe dynamic SQL is maybe not depend on user input, uh, very much for things. Um, you know, I, I very much believe in, uh, maybe taking the user input, but using it to value, like, uh, especially when it’s, um, you know, when you’re using dynamic SQL and you need to take, uh, like sort of dynamic input for things like database name, schema name, table name, column name, all of that information is in your, your server’s metadata.

Uh, more, probably more specifically, well, I guess from server level down to database level. And so I’d like to take that input and not use that maybe directly in the dynamic SQL. I like to generate my own inputs based on that metadata that’s in the, in the server where I, where there’s no chance of SQL injection of anything malicious happening.

And I can sort of weed any of that out. So whenever someone, uh, will pass in a list of columns or a database name or a table name or a schema name, I always validate that stuff against server. Metadata.

And I, I always get, uh, like the, the stuff, like the parameters and variables that I’ll use in dynamic SQL from the metadata directly, because it’s much, much harder for someone to tamper with that maliciously. Of course, with, with the remote DAC, you can do many fun things. So like one thing that I’ll do.

And let’s like, you know, if, if, if let’s like, let’s pretend that column names was, uh, like a parameter, right. Uh, to a store procedure on someone could put in a CSV list of column names. I would even use, uh, go even further and validate those column names when the metadata for whatever table we’re looking at.

So there are all sorts of things that you can do to protect yourself from a SQL injection. Using quote name is a very good, not only good for helping you avoid SQL injection, but also for helping you deal with straight objects with strange characters in them. So let’s, uh, run this, right.

Let’s get this, let’s get a list of column names and let’s look at what dynamic SQL this generates because this is going to fail mightily. If we look at what happened in here, SQL Server tried to run this select query. And this select query is not going to run with any of these things as column names without square brackets.

And it’s certainly not going to be able to select anything from a table name with the space in it. If you’re like, if you keep in mind, there’s an absolutely no like underscore or anything here. It’s just a bare ass table name.

So we want to use quote name to fix these things in order to encapsulate them correct correctly. So if we rerun this and now instead of, uh, just taking the name from, uh, sys.columns, we’re going to say quote name, uh, c.name from sys.columns. And down here, we’re going to wrap these various things in quote name as well.

Now, if this were like a big old complicated store procedure, there’s a pretty good chance that I would, um, have maybe like an inner variable that I would set to being like quote name, database name. But of course, uh, after like verifying it and like sys.databases or something. So just here for sort of brevity, I’m putting quote name around these, but like in real, like big code, I probably don’t want to like have quote name everywhere.

Cause I may forget at some point. So it’s much easier to set it in one place and then just have the object name you care about quoted. But now, of course, when we run all this and we get down to the print and the execute, this works just fine, right?

But there’s no data in the table, but we at least run the select successfully and we get back our column names here. And the dynamic SQL that gets printed out would, uh, completely, uh, uh, avoid any of the terrible errors that we saw before, because now we have square brackets around everything. Now, the nice thing about quote name is that, um, it will produce a valid output for anything that is a legal valid object name, uh, where you have to be careful as if things get longer than that.

But we’ll talk about that in a moment. But the reason why, um, square brackets do not work the way that quote, like the, as well as quote name works is because if you were to do something like this, right? Like, uh, you could like, if you were just to put your own square brackets and can concatenate those into a string at some point in the dynamic SQL, they, they are not, they, they, they do act as an object identifier, but they do not quote out additional square brackets.

So quote name would, would fix this, right? Right. Quote name would actually protect you would like double quote these things. So you would get valid results back, but, or rather it would, it would escape those things.

So you would get valid results back just adding in square brackets does not do that. So if we run this whole thing, we will see that, uh, we get valid results, but we actually executed SP who right here, right? We got all this dumb information back that no one ever understands and is very confusing, but over in the messages tab, this is what we ran, right?

We executed SP who, and then we dropped a table, right? And then when we try to select from that table at the end, we say invalid object name, right? And the reason for that is because just adding in square brackets will not escape any additional square brackets in here.

So this does not have the same power. If we were to do this and use quote name instead, right? So like, rather than say like square bracket plus this string plus square bracket, if we just say quote name, that same string, well, all of a sudden SQL Server is very, very happy to, uh, say, well, I’ve, I’ve never heard of the store procedure SP who dropped table.

All right. That’s not a thing. So like notice that when, uh, when I made this string, there was no double square bracket here, but when I use quote name, look what happens here.

We get double square brackets here. So quote name does some extra stuff that just rolling your own square brackets doesn’t do. Now you could totally, you know, do something on your own and be like, well, I’m just, if there’s a square bracket, I’m going to replace it with double square brackets to just use quote.

Right. And unless there’s some strange reason why you can’t use quote name, you’re just allergic to the letter Q. Maybe I don’t know.

I don’t know. Use quote name. Very, very simple. But, uh, just, you know, there are some cases where quote name will return a null if you, uh, make the string too long. So please do try to avoid that.

Um, if you, if you do have a situation where you need to, uh, square bracket something that is longer than quote name will return a string for, uh, where if it would just return a null, you have my blessing to use square brackets and replace square brackets within the string with double square brackets. Okay. Okay.

You can do that. But for most cases, you know, database schema, object table view, store procedure, anything like that, any, like, you know, system, like, uh, system identified object, uh, a mix of sys name and, uh, and quote name is totally fine. Um, there, you can use different things for quote name.

Uh, so like you are not just stuck with square brackets. Uh, you can use, uh, single quotes. You can use parentheses.

You can use curly brackets. You can use double quotes. You can use those funny little ticks, but you, you cannot use tildes. I’ve learned. Uh, sorry, Tilda Swinton. I do apologize. But if we run this, you will see that the word Eric has been, uh, bracketed, single quoted, parenthesized, curly bracketed, double quoted, single ticked, but, uh, we cannot tilde Eric.

Tilda and Eric were not meant to be. I will not be one of her husband’s sad to say. Uh, but one thing that is, uh, useful to talk about is that if you, if you like, so when I was talking earlier about how I will often, uh, in my store procedures, I will have a, uh, like an inner variable that I’ll set to be like quote name.

Um, something like, so like, let’s just say it’s a, uh, someone, I like, I let someone pass in a database name. I’ll hit sys.databases. Right.

And I’ll, I’ll get the database name from there where the database name parameter they pass in equals like the database name and sys.databases. And I’ll put quote name around that in that internal local variable. Where that can mess things up is if you have to do anything with it later to validate other metadata.

So now you have a database name with square brackets around it, but that your, your database name probably isn’t stored like that in sys.databases. Your schema names aren’t going to be stored like that in sys.schemas. Your table names aren’t going to be stored like that in sys.tables and so on and so on and so on.

So if you need to locate other data with, um, something that you have already quote names, uh, it’s, it’s really helpful to use parse name. So parse name is another built in function that will actually remove those square brackets. So you see up here, I have, uh, declared this local variable DBO.

And if I run this, you’ll see that, uh, just adding quote name again, we’ll double. Well, actually, well, you can actually see there’s three there, right? So we actually add in another square bracket quote name tries to do us a solid, but when we use parse name, we remove the square bracket.

So sometimes if you, in any of my procedures, you see like where some object name equals parse name, some local variable. It’s because that local variable has a quote name quoted identifier in it. And it’s messing up me looking up other metadata.

But of course, if we pair, uh, parse name and quote name, we will go back to just having single brackets. So there are all sorts of neat things you can do. And, um, last but not least, as I promised, I’m going to show you some, some, a couple of amusing things.

Now, one thing that, uh, I run into a lot and that I run into people sort of, uh, having issues with a lot is when, if you’re writing dynamic SQL, or, uh, you have your, whatever string you’re building is very, very long. So, uh, you might not be able to print it successfully in one go. It’s not a good time.

It’s not a good time. Because now you’re looking at like having to chunk print statements with like sub string or something, maybe write like a while loop to produce like a, like continuously printing thing until you run out of characters. That’s not a lot of fun.

Now I think generally for me, it’s a lot easier to, uh, produce like an XML cookie column where I can just click on it and get the full thing somewhere else. That’s, that makes my life a lot easier. So, uh, let’s pretend that I have, this is a very long string.

One way of achieving that is to use, uh, this processing instruction alias. And I just have an underscore in here to avoid like having any, like, like, like I could put anything between these, uh, these parentheses. I could put like query text something, but I just have an underscore in here to sort of eliminate lots of strange XML stuff.

But if I select processing and processing dash instruction with parentheses, and I, again, I just have the underscore in here for brevity. Uh, and I say for XML path, well, I get back a clicky column. All right.

And if I click this, I get the, that’s where the underscore comes in. But now I have this thing that I can, I could copy and paste out and do whatever I want with, you know, however it is. But what’s kind of funny is, uh, you see that it produced a column with this name, which is XML and then sort of a funny looking pseudo gooey looking thing. And what’s really funny is if you, uh, maybe skip the processing instruction thing.

And you just, uh, alias a column with that same name, you will get an XML clicky column. And this one doesn’t have any of the sort of festive XML bracketing on it. So maybe, maybe that’s more to your liking.

And what’s, what’s also very funny is, uh, I don’t know if you’ve ever used like set statistics XML on, but if you use set statistics XML on, it produces like an XML clickable for a query plan. And if you, the alias of set statistics XML on is Microsoft SQL Server, 2005 XML show plan. And if you alias a column with that name, you also get an XML clicky that you can click on and open, have open in a brand new tab for you.

So that’s my amusing long print, long printing tricks. All right. Perhaps I’ve gone too far.

Thank you for watching. Hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video, uh, where we will talk about, let’s see, what is it? Ah, choosing temporary objects.

Well, we’ve got some work ahead of us, don’t we? Still, I still have to talk people out of table variables and they still make all the same excuses. So I’m going to ride that train till it dies.

I guess I guess this is coming up on the 25th anniversary of people being wrong about table variables. So happy anniversary table variables. Happy anniversary.

All right. Goodbye. Bye. All right.

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.