Someone recently told me that they don't need to worry about SQL injection because they are using an ORM.

Oh boy.

ORMs don't automatically prevent SQL injection

Watch this week's video on YouTube

Object-relational mapping (ORM) tools allow developers to easily access an application's data layer without having to write lots of redundant code.

Most ORMs will safely parameterize certain types of queries.  The following examples use Entity Framework and SQL Server, but these examples should apply to most other major ORMs and RDBMSs).

Our LINQ query making it easy to access our data layer:

A beautiful, clean, LINQ query

And then the SQL query that our ORM generated.

A query structure that only a mother could love

You'll notice the generated SQL query is using sp_executesql that has parameterized our input variable value "TFly37".  In this instance we can say the ORM did a good job following best practices in preventing a successful SQL injection attack.

But while ORMs may prevent some SQL injection attempts, there is no guarantee that they will prevent all injection attempts.

What follows are examples of when ORMs can allow for successful injection attacks.

Programatically assembled SQL

ORMs often provide the ability for developers to map the results of adhoc SQL queries to models.  Adhoc queries mean potential injection vulnerabilities.

Consider the following:

Looks a lot like your typical SQL injection vulnerability doesn't it?

Entity Framework, or any ORM for that matter, won't be able to recognize a parameter concatenated directly into the query string.

Now hopefully the developer has some really strong input validation  on the "username" parameter, but the fact still stands: this query is injectable and the ORM will happily execute it.

Stored Procedures

Does the logic needed in your app already exist as a stored procedure?

Excellent!  Let's be DRY (don't-repeat-yourself) and call our procedure directly from our ORM:

Parameterized with the value AND datatype - give this developer a raise!

So in this example, the ORM and developer have done everything right.  So where's the injection vulnerability?

Uh oh...

Now let me be clear: this injection vulnerability is not the ORM's fault.

However, it is the developer's fault if he is blindly using a procedure without knowing what it does.  It's also the DBA's fault if she provided an injectable procedure assuming the developer would sanitize any input values in the app.

Security is hard.  Everyone needs to work together and be responsible for doing everything within their means to secure their applications.

ORM injection

Technically this isn't an example of SQL injection.

But that's why the title of this post says "2.5" instead of "3".

In this example I'm using a dynamic LINQ to access my data:

Hello concatenation my old friend

If we pass the value " OR 1 == 1 OR UserName== we will have the ORM convert it to the following query:

Game over

Injection takes many forms and doesn't just come straight from SQL.  It's important to follow best practices in all languages and tools to prevent security incidents.

Want to learn more?

If you are interested in learning more about how to protect yourself from SQL injection, be sure to  watch my online session at GroupBy at 9am Eastern on March 16th, 2018.

There are times when you are writing a query while referencing another piece of information: the results of another query, a variable value, a webpage, etc...

If using two monitors you can have a query window on each as a reference, or you can resize two query window tabs so they both fit on the screen.  But did you know there's a better option?

Watch this week's video on YouTube

Vertical Tab Groups

Vertical tab groups are great because they stay contained within the parent SSMS window.  This is nice for when I'm working on a small laptop screen or don't want to receive whiplash from moving my head back and forth between two monitors.

In order to create a vertical tab group, you need to have at least two query tabs open.  Right-click on the tab you want to be on the right-side of of the screen and choose "New Vertical Tab Group"

You can easily change the size of each open tab group by dragging the divider between them:

If you are working with really wide data, you can also try a horizontal tab group instead:

My favorite feature of tab groups is that I can move my entire SSMS window from one monitor to another (or minimize it) and all of my tabs follow suit.  This comes in real handy if working on multiple different projects at the same time.

Today I want to show you a trick that could make your queries run faster.

It won't always work, but when it does everyone will be impressed with your performance tuning prowess.  Let's go!

Watch this week's video on YouTube

Our Skewed Data

Let's create a table and insert some data.

Notice the heavily skewed value distribution.  Also notice how we have a clustered index and a very skimpy nonclustered index:

DROP DATABASE IF EXISTS ORUnionAll
CREATE DATABASE ORUnionAll
GO

CREATE TABLE ORUnionAll.dbo.TestData
(
    Col1 int,
    Col2 char(200),
    Col3 int 
)
GO

INSERT INTO ORUnionAll.dbo.TestData VALUES (1,'',1)
GO 10000
INSERT INTO ORUnionAll.dbo.TestData VALUES (2,'',2)
GO 50
INSERT INTO ORUnionAll.dbo.TestData VALUES (3,'',3)
GO 50

CREATE CLUSTERED INDEX CL_Col1 ON ORUnionAll.dbo.TestData ( Col1 )
GO

CREATE NONCLUSTERED INDEX IX_Col3 ON ORUnionAll.dbo.TestData (Col3)
GO

If we write a query that filters on one of the low-occurrence values in Col3, SQL Server will perform an index seek with a key lookup (since our skimpy nonclustered index doesn't cover all of the columns in our SELECT):

SELECT 
    Col2, Col3
FROM 
    ORUnionAll.dbo.TestData
WHERE
    Col3 = 2

If we then add an OR to our WHERE clause and filter on another low-occurrence value in Col3, SQL Server changes how it wants to retrieve results:

SELECT 
    Col2, Col3
FROM 
    ORUnionAll.dbo.TestData
WHERE
    Col3 = 2 OR Col3 = 3

Suddenly those key-lookups become too expensive for SQL Server and the query optimizer thinks it'll be faster to just scan the entire clustered index.

In general this makes sense; SQL Server tries to pick plans that are good enough in most scenarios, and in general I think it chooses wisely.

However, sometimes SQL Server doesn't pick great plans.  Sometimes the plans it picks are downright terrible.

If we encountered a similar scenario in the real-world where our tables had more columns, more rows, and larger datatypes, having SQL Server switch from a seek to a scan could kill performance.

So what can we do?

Solutions...maybe

The first thing that comes to mind is to modify or add some indexes.

But maybe our (real-world) table already has too many indexes.  Or maybe we are working with a data source where we can't modify our indexes.

We could also use the FORCESEEK hint, but I don't like using hints as permanent solutions because they feel dirty (and are likely to do unexpected things as your data changes).

One solution to UNION ALL

One solution that a lot of people overlook is rewriting the query so that it uses UNION ALLs instead of ORs.

A lot of the time it's pretty easy to refactor the query to multiple SELECT statements with UNION ALLs while remaining logically the same and returning the same results:

SELECT 
    Col2, Col3
FROM 
    ORUnionAll.dbo.TestData
WHERE 
    Col3 = 2
UNION ALL
SELECT 
    Col2, Col3
FROM 
    ORUnionAll.dbo.TestData
WHERE
    Col3 = 3

Sure, the query is uglier and will be a bigger pain to maintain if you need to make changes in the future, but sometimes we have to suffer for ~~fashion~~ query performance.

But does our UNION ALL query perform better?

Well the plan shows seeks, but as Erik Darling recently pointed out, seeks aren't always a good thing.

So let's compare the reads of the OR query versus the UNION ALL query using SET STATISTICS IO ON:

So in this case, tricking SQL Server to pick a a different plan by using UNION ALLs gave us a performance boost.  The difference in reads isn't that large in the above scenario, but I've had this trick take my queries from minutes to seconds in the real world.

So the next time you are experiencing poor performance from a query with OR operators in it, try rewriting it using UNION ALLs.

It's not always going to fix your performance problem but you won't know until you give it a try.

This post is a response to this month's T-SQL Tuesday prompt created by Aaron Bertrand.  Adam Machanic created T-SQL Tuesday as a way for SQL users to share ideas about interesting topics every month.  This month's topic is "Your passions outside of SQL Server".

Getting away

I spend most of my days in front of a computer.

While this is great because I love technology, a few times a year I like separating myself from all forms of semiconductors. I do this by going camping.

Watch this week's video on YouTube

I highly recommend watching this week's video on YouTube - I was able to include way more photos and video clips there!

Camping is a mental reset where I can focus on activities that are completely opposite of what I do every day. It involves different challenges and skill sets, and provides a literal breath of fresh air when compared to working in a typical office environment.

How did I come to enjoy sleeping on the ground and learning to survive with only 20lbs of gear? Let me describe three distinct eras in my camping evolution.

I didn't grow up camping

High school Bert's first camping trip in Vermont

My family never went on camping trips. I always enjoyed the outdoors, but I never learned any basic outdoors skills like you might learn in scouts.

My friends during high school came from similar upbringings. We didn't know anything about building campfires or sleeping in tents, but we wanted to learn.

So, during the summer before college we decided we'd drive up to Vermont, rent a campsite at a state park, and learn to live outdoors.

While an immensely fun trip, our skills were lacking since we basically ended up eating ramen and sleeping in our cars (because our incorrectly set up tent gushed with rainwater during the middle of the night).

We kept at it though, going back every summer, until we managed to figure out how things should work. After a few trips we were no longer eating just ramen and not needing to sleep in our cars anymore.

Have tent, will travel

My outdoor skills improved as I continued to camp through my college years; I learned to cook food over coals, stay dry during the harshest rain storms, and light fires with a single match instead of half a can of camping stove fuel.

During this time I also realized that could travel almost anywhere and have my accommodations cost less than \$20 per night PER CAMPSITE.

I must have been thinking "the water is too clear and warm for this New Englander"

This meant my friends (and future wife Renee) spent college spring break camping through the Everglades and the Florida Keys. We spent our days kayaking, zip lining, and eating key lime pies before returning to our ocean front campsite for the evening. I think in all that trip cost us less than \$400 per person for 9 days of fun, including gas for the minivan.

Ultra cheap vacation to Yellowstone National Park

Ultra cheap vacations continued and I now graduated to cooking single pot meals and foil packet dinners. My car was still parked nearby but I wasn't having to sleep in it at night.

I even ventured back to Vermont and proposed to Renee there while camping (she said yes even after three days of no showers. That's when I knew she was a keeper).

Backpacking and gourmet food

Everything we need, carried in and carried out.

Camping eventually evolved into backpacking - instead of having a car a few feet away with a cooking stove and cooler of cold food, now I was learning about how to go into the wilderness for days with everything I would need carried in on my back.

Backpacking is truly exciting. You get to go and see places only accessible by a long hike on foot. The trips require more planning, both in terms of hiking routes, food preparation, water scouting, wildlife management, etc.... all things that invigorate my researching, detail oriented personality.

Living out of a backpack also means that you can easily fly to locations and still have a cheap vacation. I got to see beautiful National Parks like Yellowstone, the Grand Tetons, and Shenandoah all because I knew how to fit everything I need to survive into a carry-on and personal item (except for stove fuel, bear spray, and pocket knife ...some things you just have to buy on location).

Not only is visiting these places cool to begin with, but camping overnight in them means you get to see the park early before any car driving tourists arrive and you get to stay out way longer after they all leave the park to go grab dinner.

By this point my car is parked miles away from where I am sleeping and my food game has stepped up. Although I am limited to carrying everything I need in a pack, we frequently dine on pad thai, pizza, and cinnamon rolls.

After a long day spent hiking...

... these are the only foods I need to eat.

Who says you need to be roughing it while camping?

Watch this week's video on YouTube

This weekend I caught up with Drew Furgiuele at SQL Saturday Cleveland and learned how to get involved with the open-source dbatools PowerShell module.

If you don't use dbatools yet, what are you waiting for?  It's an amazing community project that will help you automate your SQL Server work with over 350 ready-to-use commands.

After you start using dbatools, you might think of new commands you want to add or other features you want to improve - and the best part is that you can!

Even if writing PowerShell commands isn't one of your strengths, there are many ways you can contribute to this excellent community project as Drew mentions in the video above.