Preventing SELECT * Queries – The “I’ve Had Enough” Edition

A while back, I was talking to someone about how it was just a matter of time before I do something spectacularly wrong. That person replied with a picture of a cigar cutter saying “I keep this for that day, even though I don’t smoke. You can imagine what other things, besides cigars, it can cut”.

Was it a joke? – Yes, most likely. Were any major mistakes made since then? – Absolutely not. So, in a nutshell, that’s how you prevent SELECT * queries. With a very graphic threat.

Thank you for reading.

Just kidding. I’m all about love. But, the solution we’re going to see today also has something mean to it. I’m not advocating it, it’s more like “Here, keep this bat in the trunk of your car, you never know…”.

Satan’s Favorite Query

So, first and foremost, it’s important to stress that not all SELECT * queries are evil. It largely depends on many factors, such as the volume of data being read / retrieved, and how the query is written. I was watching Brent Ozar talking about this, labeling all those aspects in a scale from “Pain Free” to “Unimaginable / Unspeakable”, while detailing how each level should be approached. The last level was hilarious. He credited this approach to a guy called Jorge Segarra (b), who I imagine that had one of those days where the love doesn’t shine, because he simply had enough of asking people to stop doing SELECT * queries, so he came up with a solution similar to this one:

“YOU get an error, YOU get an error, EVERYBODY gets an error”

By creating a computed column that holds the result of a division by zero, he successfully prevented any SELECT * query from retrieving data. How funny is that?

But, despite the extra points for the malevolent creativity, this solution comes with the drawback of being “nuclear”, in the sense that the blast radius will also hit innocent queries and people that know what they are doing.

Of course, one might argue that it’s never a good idea to use such a pattern, and that is almost always true, for a couple of reasons. For example, an inoffensive query can become problematic in terms of performance, or even generate unexpected applicational errors as time goes by and columns are added to the table. We’re potentially consuming more CPU and stealing memory from other queries, to retrieve unnecessary data. That doesn’t seem smart.

But, to every rule there is an exception. I guess. For example, you may have people running queries in the same database, trying to troubleshoot data-related issues. Sometimes they might be trying to figure out what is wrong (if something is actually wrong), without a specific hint of the root cause. In those cases where we’re trying to identify divergent patterns without having a clear and defined notion of the information we need, a SELECT * query, in a particular subset of data, might be necessary. Furthermore, the above trick can be particularly painful if this troubleshooting is being done against the clock. Under those circumstances, the above error will cause valuable time loss and potentially provoke a spectacular amount of curse words.

Two Words, One Finger

With that in mind, I thought in a small adaptation, keeping the solution as evil as the above, but slightly fairer. We’re still going to create a new column, but this time we won’t force an exception:

ALTER TABLE dbo.Users 
ADD StopSelectingStar AS 0 + NULL

Imagine that you have identified the user fire.starter as the root of all evil. That user is systematically executing SELECT * queries to the Users table with no WHERE clause and that’s impacting other running queries. You’ve already tried multiple pedagogical approaches, but all failed. That guy just wants to see the world burn and you just had enough of it. You want to go the trunk and reach for that bat. So, simply DENY the permission on the previously created column:

DENY SELECT 
ON Users (StopSelectingStar ) 
TO [fire.starter];

Now, that user won’t be able to use SELECT * on that particular table anymore:

While the remaining users will still be able to perform SELECT * queries to that table:

I regret having said “Thank you for reading” in the beginning, because I don’t know what else to say now.



Categorias:Sem categoria

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão /  Alterar )

Google photo

Está a comentar usando a sua conta Google Terminar Sessão /  Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão /  Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão /  Alterar )

Connecting to %s

%d bloggers like this: