Category: SQL Server

Don’t Rely On Square Brackets To Protect You From SQL Injection: Use QUOTENAME Instead

Posted on by Erik Darling

Uno Mal

I see a lot of scripts on the internet that use dynamic SQL, but leave people wide open to SQL injection attacks.

In many cases they’re probably harmless, hitting DMVs, object names, etc. But they set a bad example. From there, people will adapt whatever dynamic SQL worked elsewhere to something they’re currently working on.

Here’s a simple script to show you how just sticking brackets into a string doesn’t protect you from SQL injection:

DROP TABLE IF EXISTS #t
CREATE TABLE #t(id int);

DECLARE 
    @s nvarchar(max) = N'[' + N'PRINT 1] DROP TABLE #t;--' + N']';

PRINT @s

EXEC sys.sp_executesql
    @s;

SELECT
    *
FROM #t AS t;
GO 

DROP TABLE IF EXISTS #t
CREATE TABLE #t(id int);

DECLARE 
    @s nvarchar(max) = QUOTENAME(N'PRINT 1] DROP TABLE #t;--')

PRINT @s

EXEC sys.sp_executesql
    @s;

SELECT
    *
FROM #t AS t;
GO

You can run this anywhere, and the results look like this:

[PRINT 1] DROP TABLE #t;--]
Msg 2812, Level 16, State 62, Line 572
Could not find stored procedure 'PRINT 1'.
Msg 208, Level 16, State 0, Line 583
Invalid object name '#t'.
[PRINT 1]] DROP TABLE #t;--]
Msg 2812, Level 16, State 62, Line 587
Could not find stored procedure 'PRINT 1] DROP TABLE #t;--'.

In the section where square brackets were used, the temp table #t got dropped. In the section where QUOTENAME was used, it wasn’t.

When you’re writing dynamic SQL, it’s important to make it as safe as possible. Part of that is avoiding the square bracket trap.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

A Couple Quick Notes On Using XML For String Aggregation In SQL Server

Posted on by Erik Darling

Cat Skins

If you’re on a new-ish version of SQL Server, using STRING_AGG is your best bet for this. Please use it instead.

Unlike STRING_SPLIT, it’s not compatibility-walled. You don’t need to be on level 130+ to use it.

If you’re on an earlier version, your most reliable bet is using XML. Using the local variable method can have quite unexpected results at times. I’ve seen it go from returning a full list of values to only returning the last value more times than I can count.

Let’s look at the XML version, though. Just in case you’re not on SQL Server 2017.

Weirdoverse

The purpose of these queries is to show you hot to remove XML elements, and handle XML control characters like &, <, >, etc. All of these results return a single row, just to keep the examples simple.

SELECT
    x = 
    (
        SELECT 
                b.Name
        FROM dbo.Badges AS b
        WHERE b.Id = 100564
        FOR XML 
            PATH('')
    );

This will return two things we don’t want:

  1. XML elements
  2. An ampersand turned into &amp;
monkeybone

To get rid of the XML elements, we can alias the inner results as [text()]

SELECT
    x = 
    (
        SELECT 
            [text()] =
                b.Name
        FROM dbo.Badges AS b
        WHERE b.Id = 100564
        FOR XML 
            PATH('')
    );

That will give us this back, still with the ampersand all mangled up.

malt shop

To fix that, we need to add a little bit to the XML-ing:

SELECT
   x = 
   (
       SELECT 
           [text()] = 
               b.Name
       FROM dbo.Badges AS b
       WHERE b.Id = 100564
       FOR XML 
           PATH(''),
           TYPE
   ).value
     (
         './text()[1]', 
         'nvarchar(max)'
     );

First, we need to add TYPE to the XML PATH syntax. That’ll give us an XML typed return type to use the .value method on.

From there, we can grab the text element, and give it a data type. You don’t specifically need to get ./text()[1] though, but I tend to use it because I’ve seen some very weird performance issues when using less verbose expressions like .[1] or just . instead.

Plans

Here are the plan differences, which are negligible for a single row.

SQL Server Query Plan
monikers

Of course, local factors may require deviating from what generally works best.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Read Committed Is A Garbage Isolation Level In SQL Server

Posted on by Erik Darling

Yep.


My friend Forrest ran into a similarly fun problem, too.

Thanks for watching!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Collation Is Weird In SQL Server

Posted on by Erik Darling

Particular String

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

A Silent Bug With Clustered Column Store Indexes And Computed Columns In SQL Server 2019

Posted on by Erik Darling

Oh Boy

This bug has been reported to the proper authorities, and this blog post is not to dunk on the Summer Intern responsible for column store indexes, but to warn you to not do this, because I don’t know what it will break under the covers.

If you read the documentation for column store indexes, it says that column store indexes can’t be created on persisted computed columns.

And that’s true. If we step through this script, creating the column store index will fail.

/*Bye Felicia*/
DROP TABLE IF EXISTS dbo.cci;
GO 

/*Hi Felicia*/
CREATE TABLE dbo.cci(id int, fk int, sd date, ed datetime);
GO 

/*COMPUTER!*/
ALTER TABLE dbo.cci ADD cc AS id * 2;
ALTER TABLE dbo.cci ALTER COLUMN cc ADD PERSISTED;
GO 

/*Rats*/
CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.cci;
GO

The error message we get is pretty clear:

Msg 35307, Level 16, State 1, Line 76
The statement failed because column 'cc' on table 'cci' is a computed column. 
Columnstore index cannot include a computed column implicitly or explicitly.

80 Blocks from Tiffany’s

However, if we change the order of things a little bit, we’ll find that we can add a persisted computed column to a table with a clustered column store index on it.

/*Bye Felicia*/
DROP TABLE IF EXISTS dbo.cci;
GO 

/*Hi Felicia*/
CREATE TABLE dbo.cci(id int, fk int, sd date, ed datetime, INDEX c CLUSTERED COLUMNSTORE);
GO 

/*???*/
ALTER TABLE dbo.cci ADD cc AS id * 2;
ALTER TABLE dbo.cci ALTER COLUMN cc ADD PERSISTED;
GO 

/*With data?*/
INSERT dbo.cci
(
    id, fk, sd, ed
)
VALUES
(
    10, 10, GETDATE(), GETDATE()  
);

/*yepyepyepyepyepyep*/
SELECT
    c.*
FROM dbo.cci AS c

/*Eh?*/
ALTER INDEX c ON dbo.cci REBUILD;
ALTER INDEX c ON dbo.cci REORGANIZE;
DBCC CHECKTABLE('dbo.cci');

And everything seems to work. However, if we drop the column store index, it can’t be re-created.

The key here is that the computed column is added, and then the persisted attribute is added second. That is where an error should be thrown.

But what do I know? I’m just a bouncer.

Bomber

Again, I’m telling you not to do this. I’m telling you that it might break things in gruesome and horrible ways.

I don’t think that this is a preview of upcoming support for persisted computed columns in clustered column store indexes.

Thanks for reading!

p.s. Really, don’t do this

p.p.s. If you do this you’re asking for trouble

p.p.p.s. This was fixed in CU12

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

sp_QuickieStore: Searching For Wait Stats

Posted on by Erik Darling

Money Money

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

No THREADPOOL Waits In Query Store

Posted on by Erik Darling

Yep Nope

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Wait Stats SQL Server’s Query Store Doesn’t Actually Track

Posted on by Erik Darling

Miss Leading

There are some oddities in the documentation for query store wait stats.

One is that RESOURCE_SEMAPHORE_QUERY_COMPILE is listed as a collected wait, but with an asterisk that says it’s not actually collected. I’ve tested workloads that generate lots of that wait, and just like the docs say, it doesn’t end up there.

Of course, since I added wait stats recently to sp_QuickieStore, I wanted to make sure other waits that I care about actually show up in there.

THREADPOOL Doesn’t

I ran a workload where 747 queries waited on THREADPOOL before running and completing.

THREADPOOL
el threado

But nothing shows up in query store wait stats. The stored procedure I ran to create the THREADPOOL waits shows up, and it has other waits — CPU and stuff — but nothing about workers. My guess why is because that’s a pre-compilation wait. When they pile up, there’s no execution context.

They’re quite not-figuratively spidless.

THREADPOOL
lezz than zero

So, uh, I had to pull filtering for that wait out.

RESOURCE_SEMAPHORE Does

Running a similar workload, I can see where queries start to rack up RESOURCE_SEMAPHORE waits. At least, I’m pretty sure they do.

See, those waits are buried in a bunch of memory waits that are usually insignificant — and hell, on well-functioning server so is RESOURCE_SEMAPHORE — but I see the time spent on that, plus some other memory related waits.

RESOURCE_SEMAPHORE
carry the um

And that’s probably good enough for hand grenades.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Updates to sp_QuickieStore, sp_PressureDetector, and sp_HumanEvents

Posted on by Erik Darling

A Thing Of Things

sp_QuickieStore:

  • Added the ability to search query store wait stats for queries that generated a large amount of a specific wait category.
  • Noticed a couple TOP queries were missing DESC for the ordering
  • Increased length of all_plan_ids column to max after someone said they hit a truncation error with a length of 1000
  • Updated documentation to reflect more specific version support: 2016 SP2+, SQL Server 2017 CU3+, 2019+, and Probably Azure

sp_PressureDetector:

  • Added a parameter to skip getting execution plans when server is really hammered
  • Added database name to memory query
  • Added a missing isolation level set
  • Increased decimal length for servers with larger amounts of memory

sp_HumanEvents:

  • Updated procedure to format queries to suit current formatting memes I enjoy
  • Organized the procedure a little bit better, putting all the declared variables and temp table creation at the beginning
  • Fixed some inevitable bugs that come with formatting and reorganizing really long stored procedures
  • Cleaned up error handling
  • Added a check for the signed query hash action; I found the procedure failed on some earlier builds of 2014 and 2012

So uh, go get those. Use’em. Have fun.

Do a SQL.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Empty Strings Aren’t Better Than NULLs

Posted on by Erik Darling

Scene

Prove me wrong.