A Sneaky Place For A Scalar Valued Function In A SQL Server Query

Posted on by Erik Darling

Sneaky TOP

Video Summary

In this video, I delve into a clever but sneaky use of user functions within SQL queries that can lead to unexpected behavior and potential security issues. Specifically, I explore how a seemingly innocuous function call can disrupt query parallelism and how different workarounds like using variables or dynamic SQL might seem like solutions but come with their own set of challenges, such as plan caching and performance variability. I also touch on the security implications of these techniques, particularly when it comes to dynamically constructing queries based on user inputs, and offer some practical advice on mitigating risks while still achieving desired functionality.

Full Transcript

Hello, Erik Darling here with Erik Darling Data in the midst of a heat crisis. It’s terrible. I was recently advised that what I thought was fluid in my ears was actually just pressure escaping my sinuses via my most, apparently, the narrowest ear canals that have ever been designed by God or man and put inside someone’s head. So anyway, I wanted to talk about a very sneaky place that I saw a function today. And it was just so stupendously clever of a place to see a function end up. And it looked exactly, the query, I mean, didn’t look like this because it’s not Stack Overflow, but it looked close enough to this to, you know, to give you a good idea of what happened. Where there was a user function, which took a, I mean, it didn’t just take a, this thing, there was like a whole thing that like, you know, figured out what the user was and other stuff and whatever. This is just simpler for me to write. I’m not that smart. So anyway, what happens is if we run this and we just look at what happens when we run this function, we get back to number 100. So the purpose of this function inside this expression right here was to return whatever a particular user setting was for how many rows they wanted to get back at once. So you could run this query, and I bet you thought that would throw an error, but it doesn’t. We can run this query, and we can get back our 100 rows, and we have, you know, whatever, we have an execution plan. And when we dug a little bit deeper into what was going on, the function looked something a little bit like this, where you would go look at a table called user settings or something, and you would return some values and, you know, kind of like stuff that a regular function does. And there was a table that backed it up with a row in it that looked like that. Okay, pretty simple. Not bad.

So, but, you know, when we run it, you know, we go do this, and we run our query with our function in it. This, even though we’re only setting a value for the top expression up here, we still hit the same exact problem that functions cause just about anywhere else in the known SQL universe where they prevent our query from running in parallel. We cannot generate a valid parallel plan with that function even up in the top here. So, that’s kind of a downer, right? So, one way around that, potentially, is to set top equal to a variable, and when we do that, we will get back a slightly faster version of our query that went parallel, right? So, we see a parallel plan down here, and all the pain of, I mean, all the forced serialization associated with that scalar value function happen up here where we declare the variable, and not down here where we mess with anything.

So, that’s an okay solution, and I think I’ve blogged about this before. I might even have another video on it. I’m not sure at this point. I’ve written a lot and videoed a lot. I lose track, and my brain is full of mush and wet newspapers and hairballs, so, and apparently, a lot of pressure that just escapes and feels like there’s fluid in my head, so that’s nice, too. So, yeah, one way around it is to potentially, I mean, like, I messed that up. The problem, we just showed a potential workaround.

The problem with that workaround, there we go, is that no matter what number we declare up here, because this is a declared variable, it has the same-ish local variable effect with being in a top expression, is when you put it in, like, a where clause or something, where SQL Server just has a, like, a solid, a steady guess for what it’s going to use for a number, it doesn’t actually change that. So, we could put 10,000 up there. I’m going to free the proc cache, so we know that we’re not reusing that plan at all, right?

So, we can just be pretty sure that the plan that SQL Server is coming up with is a new one. I can’t use recompile here, and I’ll show you why in a second. So, if we free the proc cache, and I select 10,000 rows, and I look at how many SQL Server is estimating up here, it’ll be 100 rows right there. So, that’s not very good, and it doesn’t matter if I put in 1,000, 5,000, 50,000, if I go and run that and return some rows.

SQL Server’s guess is always going to be 100 right here. So, that’s not ideal, because if you are selecting way more than 100 rows in your top, SQL Server might choose different plans based on different row goals, right?

So, that’s not great. So, your options are, of course, a recompile hint. If we stick a recompile hint in there, then SQL Server will all of a sudden magically understand that we wanted 1,000 rows, and not just 100 rows. So, there’s that, which is okay.

But with anything, when I’m saying recompile, I’m not worried about burning down the house with CPU. I’m more worried about the forensic aspect of things. So, like, you know, you have this query in there with recompile, and it might be a very important query, and now all of a sudden you’ve got no history of it in your plan cache, right?

So, like, every time this runs, SQL Server’s going to not cache a plan, say, we don’t need to bother with this. And if we wanted to troubleshoot performance with this query, we wouldn’t have a really good way to do it, right? We would have, like, no history of this query at all in there. So, one thing, well, a couple things you can do that can help.

Well, one of them is dynamic SQL. And this is safer or more safe or even more safer or more safer or more safer. But this is ultimately sniffable.

When we use totally safe dynamic SQL, it gets treated like a stored procedure in that this variable gets sniffed, or this parameter gets sniffed because we’re passing it in as a parameter down here. So, if we put 100 in the first time and we use 1,000 the second time, SQL Server will reuse the plan for 100 unless something happened where it needed to come up with a new plan.

Anyway, this is less safe but also less sniffable. Cool. All right. So, you can use a variable like this. And you can use, like, rtrim or you can say convert top barcar whatever 11, 52, 78, 25 hike. Whatever you want to do in here to make this a concatenatable from an integer to a concatenatable string value in here.

And you can execute that. And this will have a lower chance of reusing a plan. I’m not going to say it’s 100% never going to reuse a plan, but it’s a lower chance. A lower chance.

But this kind of comes back to, well, not the same problem as recompile where you would have no plans in the cache, but sort of the opposite problem of recompile where you could, depending on how popular this query is, you could have a bunch of plans in the cache. You could have a whole mess of stuff that’s coming in with all these top however many row queries. And, you know, again, you know, whenever we talk about dynamic SQL like this, you know, everyone has to talk about SQL injection and, you know, the potential security risk and how that can be a pretty big downfall with this stuff.

And I agree it is. You know, I will say that, you know, the chance is, the chance of someone coming in and, like, you know, dropping tables or doing anything crazy is lessened a bit because this is an integer value up here. Right?

So, I mean, to my mind, it’s, like, hard to figure out a way to pass in something as an integer that would be validated here that could then be transformed down here in a way that would, you know, execute an extra command. Right?

Like, it’s hard for me to figure that out. I’m sure someone out there is smart enough to do it. It’s just not me. But one thing you can do, even with totally safe dynamic SQL, is something like this. Now, you can explore a little bit of user function stuff with these functions up here and these built-in functions. And, granted, you can restrict access to these via the appropriate permissions.

But that’s hard. And a lot of times, the app login needs elevated permissions to do weird stuff, create tables, create databases. Oftentimes, applications, like, at minimum, kind of want DB owner.

And so that makes it a little bit harder to, like, you know, revoke or deny sort of basic privileges. But one thing I want to point out here is that if I run these queries, right? So if I run select suzer sid, I get OXO1 back. And I can convert that to an integer, which is 1.

And then if I run suzer name with 1, I get back SA. So SA is always 1. SA always has this OXO1. And so what I can do that’s kind of sneaky, even with totally safe dynamic SQL, is say I want my top to be 1000 plus converting suzer sid to an int. And when I run this, instead of getting 1,000 rows back, I get back, oops, there we go, 1,001 rows, which isn’t, again, a performance issue, but it is one way that someone perhaps a bit on the clever side could mess up or could get some extra information without doing anything too crazy.

They could figure out which, well, I mean, at least which login was coming. I mean, if they were logged in as SA, if they get one back, if they get back a number higher than one, then, you know, they maybe do something else to figure that out.

Anyway, that’s with the safe version of dynamic SQL. Even with that, you can play some tricks. So it would be up to, like, you know, someone on the front end to validate, you know, that there’s nothing weird coming in for, like, wherever someone enters in 1,000. Like, use a drop-down menu with static numbers in it or validate your inputs.

Again, everyone should validate their inputs. Anyway, that’s all I wanted to talk about. We’re a little over 10 minutes, which is longer than I wanted, but I messed up a time or two. Sorry about that.

I blame it on the pressure leaking out of my skull. Anyway, thanks for watching, and I will see you in another video, perhaps. Goodbye. Goodbye.

Video Summary

In this video, I delve into a clever but sneaky use of user functions within SQL queries that can lead to unexpected behavior and potential security issues. Specifically, I explore how a seemingly innocuous function call can disrupt query parallelism and how different workarounds like using variables or dynamic SQL might seem like solutions but come with their own set of challenges, such as plan caching and performance variability. I also touch on the security implications of these techniques, particularly when it comes to dynamically constructing queries based on user inputs, and offer some practical advice on mitigating risks while still achieving desired functionality.

Full Transcript

Hello, Erik Darling here with Erik Darling Data in the midst of a heat crisis. It’s terrible. I was recently advised that what I thought was fluid in my ears was actually just pressure escaping my sinuses via my most, apparently, the narrowest ear canals that have ever been designed by God or man and put inside someone’s head. So anyway, I wanted to talk about a very sneaky place that I saw a function today. And it was just so stupendously clever of a place to see a function end up. And it looked exactly, the query, I mean, didn’t look like this because it’s not Stack Overflow, but it looked close enough to this to, you know, to give you a good idea of what happened. Where there was a user function, which took a, I mean, it didn’t just take a, this thing, there was like a whole thing that like, you know, figured out what the user was and other stuff and whatever. This is just simpler for me to write. I’m not that smart. So anyway, what happens is if we run this and we just look at what happens when we run this function, we get back to number 100. So the purpose of this function inside this expression right here was to return whatever a particular user setting was for how many rows they wanted to get back at once. So you could run this query, and I bet you thought that would throw an error, but it doesn’t. We can run this query, and we can get back our 100 rows, and we have, you know, whatever, we have an execution plan. And when we dug a little bit deeper into what was going on, the function looked something a little bit like this, where you would go look at a table called user settings or something, and you would return some values and, you know, kind of like stuff that a regular function does. And there was a table that backed it up with a row in it that looked like that. Okay, pretty simple. Not bad.

So, but, you know, when we run it, you know, we go do this, and we run our query with our function in it. This, even though we’re only setting a value for the top expression up here, we still hit the same exact problem that functions cause just about anywhere else in the known SQL universe where they prevent our query from running in parallel. We cannot generate a valid parallel plan with that function even up in the top here. So, that’s kind of a downer, right? So, one way around that, potentially, is to set top equal to a variable, and when we do that, we will get back a slightly faster version of our query that went parallel, right? So, we see a parallel plan down here, and all the pain of, I mean, all the forced serialization associated with that scalar value function happen up here where we declare the variable, and not down here where we mess with anything.

So, that’s an okay solution, and I think I’ve blogged about this before. I might even have another video on it. I’m not sure at this point. I’ve written a lot and videoed a lot. I lose track, and my brain is full of mush and wet newspapers and hairballs, so, and apparently, a lot of pressure that just escapes and feels like there’s fluid in my head, so that’s nice, too. So, yeah, one way around it is to potentially, I mean, like, I messed that up. The problem, we just showed a potential workaround.

The problem with that workaround, there we go, is that no matter what number we declare up here, because this is a declared variable, it has the same-ish local variable effect with being in a top expression, is when you put it in, like, a where clause or something, where SQL Server just has a, like, a solid, a steady guess for what it’s going to use for a number, it doesn’t actually change that. So, we could put 10,000 up there. I’m going to free the proc cache, so we know that we’re not reusing that plan at all, right?

So, we can just be pretty sure that the plan that SQL Server is coming up with is a new one. I can’t use recompile here, and I’ll show you why in a second. So, if we free the proc cache, and I select 10,000 rows, and I look at how many SQL Server is estimating up here, it’ll be 100 rows right there.

So, that’s not very good, and it doesn’t matter if I put in 1,000, 5,000, 50,000, if I go and run that and return some rows. SQL Server’s guess is always going to be 100 right here. So, that’s not ideal, because if you are selecting way more than 100 rows in your top, SQL Server might choose different plans based on different row goals, right?

So, that’s not great. So, your options are, of course, a recompile hint. If we stick a recompile hint in there, then SQL Server will all of a sudden magically understand that we wanted 1,000 rows, and not just 100 rows. So, there’s that, which is okay.

But with anything, when I’m saying recompile, I’m not worried about burning down the house with CPU. I’m more worried about the forensic aspect of things. So, like, you know, you have this query in there with recompile, and it might be a very important query, and now all of a sudden you’ve got no history of it in your plan cache, right?

So, like, every time this runs, SQL Server’s going to not cache a plan, say, we don’t need to bother with this. And if we wanted to troubleshoot performance with this query, we wouldn’t have a really good way to do it, right? We would have, like, no history of this query at all in there.

So, one thing, well, a couple things you can do that can help. Well, one of them is dynamic SQL. And this is safer or more safe or even more safer or more safer or more safer.

But this is ultimately sniffable. When we use totally safe dynamic SQL, it gets treated like a stored procedure in that this variable gets sniffed, or this parameter gets sniffed because we’re passing it in as a parameter down here.

So, if we put 100 in the first time and we use 1,000 the second time, SQL Server will reuse the plan for 100 unless something happened where it needed to come up with a new plan. Anyway, this is less safe but also less sniffable.

Cool. All right. So, you can use a variable like this. And you can use, like, rtrim or you can say convert top barcar whatever 11, 52, 78, 25 hike. Whatever you want to do in here to make this a concatenatable from an integer to a concatenatable string value in here.

And you can execute that. And this will have a lower chance of reusing a plan. I’m not going to say it’s 100% never going to reuse a plan, but it’s a lower chance.

A lower chance. But this kind of comes back to, well, not the same problem as recompile where you would have no plans in the cache, but sort of the opposite problem of recompile where you could, depending on how popular this query is, you could have a bunch of plans in the cache.

You could have a whole mess of stuff that’s coming in with all these top however many row queries. And, you know, again, you know, whenever we talk about dynamic SQL like this, you know, everyone has to talk about SQL injection and, you know, the potential security risk and how that can be a pretty big downfall with this stuff.

And I agree it is. You know, I will say that, you know, the chance is, the chance of someone coming in and, like, you know, dropping tables or doing anything crazy is lessened a bit because this is an integer value up here.

Right? So, I mean, to my mind, it’s, like, hard to figure out a way to pass in something as an integer that would be validated here that could then be transformed down here in a way that would, you know, execute an extra command.

Right? Like, it’s hard for me to figure that out. I’m sure someone out there is smart enough to do it.

It’s just not me. But one thing you can do, even with totally safe dynamic SQL, is something like this. Now, you can explore a little bit of user function stuff with these functions up here and these built-in functions.

And, granted, you can restrict access to these via the appropriate permissions. But that’s hard. And a lot of times, the app login needs elevated permissions to do weird stuff, create tables, create databases.

Oftentimes, applications, like, at minimum, kind of want DB owner. And so that makes it a little bit harder to, like, you know, revoke or deny sort of basic privileges. But one thing I want to point out here is that if I run these queries, right?

So if I run select suzer sid, I get OXO1 back. And I can convert that to an integer, which is 1. And then if I run suzer name with 1, I get back SA.

So SA is always 1. SA always has this OXO1. And so what I can do that’s kind of sneaky, even with totally safe dynamic SQL, is say I want my top to be 1000 plus converting suzer sid to an int.

And when I run this, instead of getting 1,000 rows back, I get back, oops, there we go, 1,001 rows, which isn’t, again, a performance issue, but it is one way that someone perhaps a bit on the clever side could mess up or could get some extra information without doing anything too crazy.

They could figure out which, well, I mean, at least which login was coming. I mean, if they were logged in as SA, if they get one back, if they get back a number higher than one, then, you know, they maybe do something else to figure that out.

Anyway, that’s with the safe version of dynamic SQL. Even with that, you can play some tricks. So it would be up to, like, you know, someone on the front end to validate, you know, that there’s nothing weird coming in for, like, wherever someone enters in 1,000.

Like, use a drop-down menu with static numbers in it or validate your inputs. Again, everyone should validate their inputs. Anyway, that’s all I wanted to talk about.

We’re a little over 10 minutes, which is longer than I wanted, but I messed up a time or two. Sorry about that. I blame it on the pressure leaking out of my skull. Anyway, thanks for watching, and I will see you in another video, perhaps.

Goodbye. Goodbye.

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.

Related Posts