This post is a response to this month's T-SQL Tuesday prompt created by Arun Sirpal.  Adam Machanic created T-SQL Tuesday as a way for SQL users to share ideas about interesting topics. This month's topic is "Your Technical Challenges Conquered".

DBA Skills 101: SQL Logins

While writing last week's post about efficiently scripting database objects, I decided to make progress towards my 2018 learning goals by figuring out what database permissions were needed for running SQL Server Management Studio's "Generate Scripts" tool.

I thought it would be best to start with a clean slate so I created a new SQL login and database user so that I could definitively figure out which permissions are needed.

Normally I use Windows Authentication for my logins, but this time I thought "since I'm getting crazy learning new things, let me try creating a SQL Login instead."

After I created my login, I decided to test connecting to my server before digging into the permissions.  Result?

Watch this week's video on YouTube

I can't connect!

That's right, I tried to connect and I got this very detailed error message :

"Great," I thought.  "I should just switch to a Windows Authentication login, those always work for me."

"BUT NO, THEN I WON'T LEARN ANYTHING!"

On to troubleshooting

First things first, I tried retyping my login and password (I know typing in the password of "password" is really tricky but I've made mistakes doing much simpler things).

No luck.  Maybe when I created the login I fat-fingered the password?

I recreated the login, making sure I precisely typed the password.  Try to connect again...nope.

Ok, ok.  I'm missing something obvious.  I have this error message though - maybe the internet will know!

I find the exact error message in a blog post by Aaron Bertrand - he's a credible guy, I bet I'll find the solution there!

Nope.

(Side note: the answer is there, just buried in the comments.  In my eager "this will be an easy solution" I didn't bother scrolling down that far).

Ok... how about books online?  Even though I created the login through the SSMS GUI, I know the T-SQL command to do the same is CREATE LOGIN.  Maybe I'll find the solution in the documentation?

No luck.

(Side note again: in hindsight you can get to the solution from the above link, but it's buried two further links deep.  While troubleshooting I was in the mindset of "ain't nobody got time for that" - I wanted a solution given to me immediately without having to do any further research!)

I kept searching online, reading through Stack Overflow answers, not finding what I needed.

(Side note (last one, I promise): anyone else having a harder time searching for relevant Stack Overflow answers?  It feels like more and more I find questions/answers that are for older versions and no longer relevant)

At this point I was really frustrated.  "CREATING A LOGIN SHOULD BE LIKE THE FIRST THING A DBA LEARNS!! WHY IS THIS SO HARD?!?!?!?!?!?!"

At that point I was tired and disappointed that I had spent more time trying to solve this login problem rather than actually figuring out the permissions that I wanted to include in my blog post.

Sleep

I decided to take a break for the night and revisit the problem the next morning.

As expected, I searched the internet for the answer again and somehow my keyword selection hit the jackpot - I found the Stack Overflow answer telling me I needed to set the server to mixed authentication mode:

Wow, that was easy.

Takeaway

This wasn't a complex problem.  At least, it shouldn't have been a complex problem.

All in all I spent probably 30 minutes trying to figure it out - not the longest amount of time I've sunk into a problem that ended up having a really simple solution.

However, this stuff happens.  It's amazing what a fresh (rested) set of eyes can do for solving a problem.

Lesson learned: next time I'm getting frustrated by a problem that I think should be easy to solve, I need to step away from the computer and come back once I have a clearer mindset :).

One Last Technical Challenge (BONUS)

I figured I'd add one more technical challenge to this post: submit a pull request to the sql-docs GitHub.

My rationale was that I couldn't be the only person to have ever been stumped by authentication modes.  Maybe I could be helpful to the next person who visits the CREATE LOGIN books online page and give them a hint as to why they can't connect.

Contributing to open source isn't something I've done through Github before, but luckily I had Steve Jones's excellent write up to guide me.

There were no real challenges here since I was just making a simple edit, and low and behold a few days later my PR got merged and is now live in BOL - cool!

Recently I was discussing with Peter Saverman whether it would be possible to take some database tables that look like this:

And output them so that the Cars and Toys data would map to a multi-object JSON array like so:

Watch this week's video on YouTube

Why would you ever need this?

If you are coming from a pure SQL background, at this point you might be wondering  why you would ever want create an object array that contains mixed object types.  Well, from an application development standpoint this type of scenario can be fairly common.

In a database, it makes sense to divide Home and Car and Toy into separate tables.  Sure, we could probably combine the latter two with some normalization, but imagine we will have many different types of entities that will be more difficult to normalize - sometimes it just makes sense to store this information separately.

Not to mention that performing analytical type queries across many rows of data will typically be much faster stored in this three table format.

The three table layout, while organized from a database standpoint, might not be the best way to organize the data in an object-oriented application.  Usually in a transaction oriented application, we want our data to all be together as one entity.  This is why NoSQL is all the rage among app developers.  Having all of your related data all together makes it easy to manage, move, update, etc...  **This is where the array of multi-type objects comes in - it'd be pretty easy to use this structure as an array of dynamic or inherited objects inside of our application.

Why not just combine these Car and Toy entities in app?

Reading the data into the app through multiple queries and mapping that data to objects is usually the first way you would try doing something like this.

However, depending on many different variables, like the size of the data, the number of requests, the speed of the network, the hardware the app is running on, etc... mapping your data from multiple queries might not be the most efficient way to go.

On the other hand, if you have a big beefy SQL Server available that can do those transformations for you, and you are willing to pay for the processing time on an \$8k/core enterprise licensed machine, then performing all of the these transformations on your SQL Server is the way to go.

The solution

UPDATE: Jovan Popovic suggested an even cleaner solution using CONCAT_WS.  See the update at the bottom of this post.

First, here's the data if you want to play along at home:

DROP TABLE IF EXISTS ##Home;
GO
DROP TABLE IF EXISTS ##Car;
GO
DROP TABLE IF EXISTS ##Toy;
GO

CREATE TABLE ##Home
(
    HomeId int IDENTITY PRIMARY KEY,
    City nvarchar(20),
    State nchar(2)
);
GO

CREATE TABLE ##Car
(
    CarId int IDENTITY PRIMARY KEY,
    HomeId int,
    Year smallint,
    Make nvarchar(20),
    Model nvarchar(20),
    FOREIGN KEY (HomeId) REFERENCES ##Home(HomeId)
);
GO

CREATE TABLE ##Toy
(
    ToyId int IDENTITY PRIMARY KEY,
    HomeId int,
    Category nvarchar(20),
    RiderCapacity int,
    FOREIGN KEY (HomeId) REFERENCES ##Home(HomeId)
);
GO

INSERT INTO ##Home (City,State) VALUES ('Cleveland','OH')
INSERT INTO ##Home (City,State) VALUES ('Malibu','CA')

INSERT INTO ##Car (HomeId,Year, Make, Model) VALUES ('1','2017', 'Volkswagen', 'Golf')
INSERT INTO ##Car (HomeId,Year, Make, Model) VALUES ('2','2014', 'Porsche', '911')

INSERT INTO ##Toy (HomeId,Category, RiderCapacity) VALUES ('1','Bicycle', 1)
INSERT INTO ##Toy (HomeId,Category, RiderCapacity) VALUES ('2','Kayak', 2)

SELECT * FROM ##Home
SELECT * FROM ##Car
SELECT * FROM ##Toy

And here's the query that does all of the transforming:

SELECT 
    h.HomeId,
    h.City,
    h.State,
    GarageItems = JSON_QUERY('[' + STRING_AGG( GarageItems.DynamicData,',') + ']','$')
FROM
    ##Home h
    INNER JOIN
    (
        SELECT
            HomeId,
            JSON_QUERY(Cars,'$') AS DynamicData
        FROM
            ##Home h
            CROSS APPLY
            (
            SELECT 
                (
                SELECT  
                    *
                FROM
                    ##Car c
                WHERE
                    c.HomeId = h.HomeId
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                ) AS Cars
            ) d 
        UNION ALL
        SELECT
            HomeId,
            JSON_QUERY(Cars,'$') AS DynamicData
        FROM
            ##Home h
            CROSS APPLY
            (
            SELECT 
                (
                SELECT  
                    *
                FROM
                    ##Toy c
                WHERE
                    c.HomeId = h.HomeId
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                ) AS Cars
            ) d
    ) GarageItems
        ON h.HomeId = GarageItems.HomeId
GROUP BY
    h.HomeId,
    h.City,
    h.State

There are a couple of key elements that make this work.

CROSS APPLY

When using FOR JSON PATH , ALL rows and columns from that result set will get converted to a single JSON string.

This creates a problem if, for example, you want to have a column for your JSON string and a separate column for something like a foreign key (in our case, HomeId).  Or if you want to generate multiple JSON strings filtered on a foreign key.

The way I chose to get around this is to use CROSS APPLY with a join back to our Home table - this way we get our JSON string for either Cars or Toys created but then output it along with some additional columns.

WITHOUT_ARRAY_WRAPPER

When using FOR JSON PATH to turn a result set into a JSON string, SQL Server will automatically add square brackets around the JSON output as if it were an array.

This is a problem in our scenario because when we use FOR JSON PATH to turn the Car and Toy table into JSON strings, we eventually want to combine them together into the same array instead of two separate arrays.  The solution to this is using the WITHOUT_ARRAY_WRAPPER option to output the JSON string without the square brackets.

Conclusion

Your individual scenario and results may vary.  This solution was to solve a specific scenario in a specific environment.

Is it the right way to go about solving your performance problems all of the time? No.  But offloading these transformations onto SQL Server is an option to keep in mind.

Just remember - always test to make sure your performance changes are actually helping.

UPDATED Solution Using CONCAT_WS:

This solution recommended by Jovan Popovic is even easier than above.  It requires using CONCAT_WS, which is available starting in SQL Server 2017 (the above solution requires STRING_AGG which is also in 2017, but it could be rewritten using FOR XML string aggregation if necessary for earlier versions)

SELECT h.*,
'['+ CONCAT_WS(',',
(SELECT * FROM ##Car c WHERE c.HomeId = h.HomeId FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
(SELECT * FROM ##Toy t WHERE t.HomeId = h.HomeId FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
)
+ ']'
FROM ##Home h

Have you ever wanted to find something that was referenced in the body of a SQL query?

Maybe you need to know what queries you will have to modify for an upcoming table rename.  Or maybe you want to see how many queries on your server are running [SELECT *]{.lang:default .highlight:0 .decode:true .crayon-inline}

Below are two templates you can use to search across the text of SQL queries on your server.

Watch this week's video on YouTube

1. Searching Stored Procedures, Functions, and Views

If the queries you are interested in are part of a stored procedure, function, or view, then you have to look no further than the [sys.sql_modules]{.lang:default .highlight:0 .decode:true .crayon-inline} view.

This view stores the query text of every module in your database, along with a number of other properties.

You can use something like the following as a template for searching through the query texts of these database objects:

USE [<database name>];
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    o.type_desc AS ObjectType,
    DB_NAME(o.parent_object_id) AS DatabaseName,
    s.name as SchemaName,
    o.name as ObjectName,
    r.Definition
FROM
    sys.sql_modules r
    INNER JOIN sys.objects o
        ON r.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
WHERE
    -- put your search keyword here
    r.Definition LIKE '%SELECT%'

For example, I recently built a query for searching stored procedures and functions that might contain SQL injection vulnerabilities.

Using the starting template above, I added some filtering in the WHERE clause to limit my search to queries that follow common coding patterns that are vulnerable to SQL injection:

USE [<database name>];
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    o.type_desc AS ObjectType,
    DB_NAME(o.parent_object_id) AS DatabaseName,
    s.name as SchemaName,
    o.name as ObjectName,
    r.Definition
FROM
    sys.sql_modules r
    INNER JOIN sys.objects o
        ON r.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
WHERE
    -- Remove white space from query texts
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        r.Definition,CHAR(0),''),CHAR(9),''),CHAR(10),''),CHAR(11),''),
        CHAR(12),''),CHAR(13),''),CHAR(14),''),CHAR(160),''),' ','')
    LIKE '%+@%'
    AND 
    ( -- Only if executes a dynamic string
        r.Definition LIKE '%EXEC(%'
        OR r.Definition LIKE '%EXECUTE%'
        OR r.Definition LIKE '%sp_executesql%'
    )

2. Searching Ad-Hoc SQL Queries

Searching across ad-hoc queries is a little tougher.  Unless you are proactively logging the query texts with extended events or some other tool, there is no way to definitively search every ad-hoc query text.

However, SQL Server does create (or reuse) an execution plan for each query that executes.  Most of those plans are then added to the execution plan cache.

Execution plans are eventually removed from the cache for various reasons, but while they exist we can easily search their contents, including searching through that plan's query text.

As a starting point, you can use the following code to retrieve SQL query texts that are currently stored in the plan cache:

USE [<database name>];
GO

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
   query_plan AS [QueryPlan]
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
WHERE
    -- put your search keywords here
    stmt.value('(@StatementText)[1]', 'varchar(max)') LIKE '%SELECT%'

Although the template above searches for the query texts in our execution plans, you can also use it to search for other query plan elements, such as elements that indicate if you have non-sargable query.

I used this technique recently to search for ad-hoc queries that might be vulnerable to SQL injection.  I modified the template above to search the input parameter values instead of the query texts, flagging any values that look like they might have some injection code in them:

USE [<database name>];
GO

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
   query_plan AS [QueryPlan],
   stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') AS [ParameterValue] 
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
WHERE
    -- if single quotes exist in a parameter
    stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%''%'
    OR stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%sys.objects%'
    OR stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%[0-9]=[0-9]%'

So while using this technique won't allow you to search across 100% of ad-hoc queries, it should be able to search the ones that run most frequently and appear in your plan cache.

Intrigued by how I'm searching query texts for SQL injection vulnerabilities? Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group to learn about these queries and how protect yourself from SQL injection.

Photo by Jaanus Jagomägi on Unsplash

Interested in learning more about SQL injection attacks, including how to prevent them?  Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group.

SQL injection continues to be one of the biggest security risks that we face as database professionals.

Every year, millions of users' personal information is leaked due to poorly written queries exploited by SQL injection.  The sad truth is that SQL injection is completely preventable with the right knowledge.

My goal today is to cover four common misconceptions that people have about SQL injection in an effort to dissuade any illusions that an injection attack is not something that can happen to you.

Watch this week's video on YouTube

1. "My database information isn't public"

Let's see, without me knowing anything about your databases, I'm guessing you might have some tables with names like:

• Users
• Inventory
• Products
• Sales
• etc...

Any of those sound familiar?

You might not be publicly publishing your database object names, but that doesn't mean they aren't easy to guess.

All a malicious user needs is a list of common database table names and they can iterate over the ones they are interested in until they find the ones that match in your system.

2. "But I obfuscate all of my table and column names!"

Oh jeez.  I hope you don't do this.

Some people do this for job security ("since only I can understand my naming conventions, I'm guaranteeing myself a job!") and that's a terrible reason in and of itself.

Doing it for security reasons is just as horrible though.  Why?  Well, have you ever heard of some system tables like sys.objects and sys.columns?

SELECT 
    t.name, c.name 
FROM 
    sys.objects t
    INNER JOIN sys.columns c 
        on t.object_id = c.object_id

A hacker wanting to get into your system can easily write queries like the ones above, revealing your "secure" naming conventions.

Security through obscurity doesn't work.  If you have table names that aren't common, that's perfectly fine, but don't use that as your only form of prevention.

3. "Injection is the developer's/dba's/somebody else's problem"

You're exactly right.  SQL injection is a problem that should be tackled by the developer/dba/other person.

But it's also a problem that benefits from multiple layers of security, meaning it's your problem to solve as well.

Preventing sql injection is hard.

Developers should be validating, sanitizing, parameterizing etc...  DBAs should be parameterizing, sanitizing, restricting access, etc..

Multiple layers of security in the app and in the database are the only way to confidently prevent an injection attack.

4. "I'm too small of a fish in a big pond - no one would go out of their way to attack me"

So you run a niche business making and selling bespoke garden gnomes.

You only have a few dozen/hundred customers, so who would bother trying to steal your data with SQL injection?

Well, most SQL injection attacks can be completely automated with tools like sqlmap.  Someone might not care about your business enough to handcraft some SQL injection code, but that won't stop them from stealing your handcrafted garden gnome customers' data through automated means.

No app, big or small, is protected from the wrath of automated SQL injection tooling.

Interested in learning more about SQL injection attacks, including how to prevent them?  Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group.

Photo by Fischer Twins on Unsplash

You probably tune your queries for maximum performance.  You take pride in knowing how to add indexes and refactor code in order to squeeze out every last drop your server's performance potential.  Speed is usually king.

That's why you probably don't use SQL Server's WAITFOR command regularly - it actually makes your overall query run slower.

However, slowness isn't always a bad thing.  Today I want to show you two of my favorite ways for using the WAITFOR command.

Watch this week's video on YouTube

You can also watch this week's content on my YouTube channel.

1. Building A Human

Modern day computers are fast.  CPUs perform billions of actions per second, the amount of RAM manufactures can cram onto a stick increases regularly, and SSDs are quickly making disk I/O concerns a thing of the past.

While all of those things are great for processing large workloads, they move computers further and further away from "human speed".

But "human speed" is sometimes what you want.  Maybe you want to simulate app usage on your database or the load created by analysts running ad hoc queries against your server.

This is where I love using WAITFOR DELAY - it can simulate humans executing queries extremely welll:

-- Run forever
WHILE (1=1)
BEGIN
    --Insert data to simulate an app action from our app
    EXEC dbo.BuyDonuts 12

    -- We usually average an order every 3 seconds
    WAITFOR DELAY '00:00:03'
END

Throw in some psuedo-random number generation and some IF statements, and you have a fake server load you can start using:

WHILE (1=1)
BEGIN
    -- Generate command values 1-24
    DECLARE @RandomDonutAmount int = ABS(CHECKSUM(NEWID()) % 25) + 1

    -- Generate a delay between 0 and 5 seconds
    DECLARE @RandomDelay int = ABS(CHECKSUM(NEWID()) % 6)

    EXEC dbo.BuyDonuts @RandomDonutAmount

    WAITFOR DELAY @RandomDelay
END

2. Poor Man's Service Broker

Service Broker is a great feature in SQL Server.  It handles messaging and queuing scenarios really well, but requires more setup time so I usually don't like using it in scenarios where I need something quick and dirty.

Instead of having to set up Service Broker to know when some data is available or a process is ready to be kicked off, I can do the same with a WHILE loop and a WAITFOR:

DECLARE @Quantity smallint = NULL

-- Keep checking our table data until we find the data we want
WHILE (@Quantity IS NULL)
BEGIN
    -- Delay each iteration of our loop for 10 seconds
    WAITFOR DELAY '00:00:03'

    -- Check to see if someone has bought a dozen donuts yet
    SELECT @Quantity = Quantity FROM dbo.Orders WHERE Quantity = 12
END

-- Run some other query now that our dozen donut purchase condition has been met
EXEC dbo.GenerateCoupon

Fancy? No.  Practical? Yes.

No longer do I need to keep checking a table for results before I run a query - I can have WAITFOR do that for me.

If you know there is a specific time you want to wait for until you start pinging some process, you can incorporate WAITFOR TIME to make your checking even more intelligent:

DECLARE @Quantity smallint = NULL

-- Let's not start checking until 6am...that's when the donut shop opens
WAITFOR TIME '06:00:00'
-- Keep checking our table data until we find the data we want
WHILE (@Quantity IS NULL)
BEGIN
    -- Delay each iteration of our loop for 10 seconds
    WAITFOR DELAY '00:00:03'

    -- Check to see if someone has bought a dozen donuts yet
    SELECT @Quantity = Quantity FROM dbo.Orders WHERE Quantity = 12
END

-- Run some other query now that our dozen donut purchase condition has been met
EXEC dbo.GenerateCoupon