Watch this week's video on YouTube

In this week's video with Power BI expert Eugene Meidinger, we're in the kitchen learning about Power BI.

Power BI has two languages available for working with data: Power Query and DAX.

Power Query is most useful cleaning and preparing your data as it comes from a variety of sources: Excel, csv, SQL Server, etc...

DAX is the heavy hitter that allows you mix all of these disparate data sources into a singel Power BI model.

Be sure to visit Eugene's blog post for more detailed information.

Watch this week's video on YouTube

Many people think daylight savings time was created to help align the hours that the sun is up with our waking hours so farmers and school children didn't have to be outside in the dark.  Or something like that.

Well let me fill you in on a little secret - daylight savings time was actually created by a government works project to ensure that programmers could forever write tedious conditional logic in their date-based queries to handle the date time conversions surrounding time zones and daylight savings time.

While these types of datetime conversions have historically been a pain, SQL Server 2016 introduced the AT TIME ZONE feature makes these types of conversions a breeze.

Defining Time Zone

Let's say you have a datetime value that you know is encoded in UTC (if you don't know what timezone your data was originally encoded in you're out of luck):

Besides naming convention, there's nothing that tells us that our datetime is in UTC.  But if we know that to be the case, we can use AT TIME ZONE to add an UTC offset to the timestamp to make it official:

See that +00:00  at the end of our value?  That's our time zone offset - it's basically telling us how many hours and minutes away from UTC our date is stored in.  With this offset in place, our UTC datetime isn't encoded only by the variable name - it's actually encoded in the data itself.

Converting Time Zones

Now that we have a well-defined UTC datetime, we can use AT TIME ZONE to easily convert our data to different time zones.

For example, if we want to convert our UTC datetime to Eastern Standard Time, we can tack on  AT TIME ZONE 'Eastern Standard Time' :

The time portion of our datetime is now showing 7am with the offset indicating we are 4 hours behind UTC.  Easy time zone conversions, yes!

But What About Our Farmer Friends?

So AT TIME ZONE makes it easy to convert between time zones - but how does it handle daylight savings time conversions?

Well, on March 11, 2018 I lost an hour of my life when daylight savings time kicked in at 2am.  Does AT TIME ZONE recognize this theft?

YES! Say so long to conditional conditions that subtract an amount of time based on the date; AT TIME ZONE handles the switch to DST with ease as noted by the offset change from -05:00  to -04:00 .

And just like that, programming for time zone and day light savings time logic just became a little easier.

But I Don't Live In The Eastern Standard Time Zone!

No problem, in addition to AT TIME ZONE, SQL Server 2016 added this nifty table that will give you the names of all of the time zones it supports:

select * from sys.time_zone_info

Watch this week's video on YouTube

One misconception some people have about SQL injection is that it can only happen when concatenating a user input parameter directly into your dynamically built query string:

While this type of injection flaw is easy to spot, there are other less direct ways an injection attack can occur.

Second Order SQL Injection Attacks

SQL injection attacks that delay execution until a secondary query are known as "second order".

This means a malicious user can inject a query fragment into a query (that's not necessarily vulnerable to injection), and then have that injected SQL execute in a second query that is vulnerable to SQL injection.

Let's look at an example.

Doggo Pics

Imagine a website where dog owners can share pictures of their best friends:

The internet is a great place

Now imagine that users of Doggo Pics can set a preference for what order they view pictures when they visit the website:

When a user saves their sorting preference, the sort column and order get saved to a preferences table in the database:

No SQL injection vulnerabilities here

The next time the user goes to view the page, the doggo pictures will be sorted based on their saved preferences.  This works because the query pulling the pics/descriptions is dynamically sorting the data based on the user's preference:

We are dynamically sorting based on the user's preference

Viewing doggos sorted by longest tongue length

The above flow is how the website is supposed to function.  So how does a malicious user inject SQL code into these queries if the only query they directly save input into is the UpdateSortOrder procedure?

The Attack

So the developer of Doggo Pics was too busy enjoying pictures of doggos to implement any type of input validation on sort order preferences.  This means a hacker can do something like inject a SQL statement onto the end of the sort order dropdown:

The hacker modifies the "desc" value to include an INSERT statement

When our dbo.UpdateSortOrder procedure executes on the backend, it looks like this:

See where this is going?  Now when our stored procedure that dynamically sorts the picture data executes, the hacker's INSERT statement is going to execute as well:

What happens next is simple: The first time our malicious user goes to view the Doggo Pics, they receive the pictures in their preferred sort order.  Additionally an INSERT INTO statement executes back on the server.

The second time the user views the Doggo Pics page, the values from that previously ran INSERT INTO statement are now visible on the screen:

So even though the first query the user encounters (saving sort order preferences) is entirely SQL injection free, our second order SQL injection attack occurs when our second SQL query dynamically executes the injected code that was stored in our user preferences table in the database.

How do I first and second order SQL injection attacks?

I recently presented at the GroupBy conference where I showed exactly how to protect your data from these types of attacks.

My presentation was recorded and is available for you to watch on YouTube:

https://www.youtube.com/watch?v=qrOLg3wjjOs

You can also read more about different types of SQL injection attacks and preventative solutions by reading through my blog archives.

This post is a response to this month's T-SQL Tuesday #100 prompt by the creator of T-SQL Tuesday himself, Adam Machanic.   T-SQL Tuesday is a way for SQL Server bloggers to share ideas about a different database or professional topic every month.

This month I'm going down the science fiction route and pretending that I'm writing about a new SQL Server feature in 2026.

Fully Automatic Tuning

Watch this week's video on YouTube

I was really excited when automatic tuning capabilities were first introduced in SQL Server 2017.  I couldn't wait to say so-long to the days where I had to spend time fixing basic, repetitive query tuning problems.

And while those first versions of automatic plan choice corrections were fine, there was a lot left to be desired...

Fortunately, Microsoft has fully leveraged its built-in R and Python services to allow for advanced automatic tuning to make the life of SQL Server DBAs and developers that much easier.

On By Default

Perhaps the coolest part of these new automatic tuning capabilities is that they are on by default.  What this means is that databases will seem to perform better right out of the box without any kind of intervention.

I think the fact that Microsoft is confident enough to enable this by default in the on-premise version of SQL Server shows how confident they are in the capabilities of these features.

Optimize For Memory and Data Skew

While the first iterations of automatic query tuning involved swapping out query plans when SQL Server found a regression in CPU performance, the new automatic plan correction is able to factor in many more elements.

For example, instead of optimizing for CPU usage, setting the new flag OPTIMIZE_FOR_MEMORY = ON  allows SQL server to minimize memory usage instead.

Also, with the addition of the new  "Optimized" cardinality estimator (so now we have "Legacy", "New", and "Optimized" -  who's in charge of naming these things???) SQL Server is able to swap out different estimators at the query level in order to generate better execution plans!

What time is it?

Another new addition to automatic plan corrections is SQL Server's ability to choose an appropriate execution plan based on historical time-of-day server usage.

So if a query is executing during a lull period on the server, SQL Server is intelligent enough to realize this and choose a plan that is more resource intensive.  This means faster query executions at the cost of a more intensive plan - but that's OK since the server isn't being fully utilized during those times anyway.

Making use of hardware sensors

As the world continues to include more data collecting sensors everywhere, SQL Server makes good use of these data points in 2026.

Tying into the server's CPU and motherboard temperature sensors, SQL Server is able to negotiate with the OS and hardware to allow for dynamic CPU overclocking based on server demands.

While this option is not turned on by default, enabling dynamic overclocking allows for SQL Server to give itself a CPU processing boost when necessary, and then dial back down to more stable levels once finished.

This obviously won't be a feature used by everybody, but users who are willing to trade off some stability for additional analytical processing performance will love this feature.

How I Stopped Worrying And Learned To Love Automatic Tuning

At the end of the day, we are our own worst enemies.  Even with the latest and greatest AI technology, we are capable of writing queries so terrible that even the smartest machine learning algorithms can't grasp.

While SQL Server's automatic tuning features work wonderfully on the boring and mundane performance problems, there are still plenty of performance problems that it leaves for us to troubleshoot.

And I love that.  Let the software optimize itself and maintain a "good enough" baseline while letting me play with the really fun performance problems.

I'm sure these features will continue to evolve - but so will we, working on new problems and facing new data challenges.

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.