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.

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.

In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again.

Coming from a .NET background, I'm used to receiving relatively helpful error messages that point me to the precise location of the error in my code:

Pinpoint error finding

SQL Server Management Studio does a decent job too - **except when it doesn't. ** Different versions of SSMS provide error line numbers differently.

Watch this week's video on YouTube

In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query.  This means if you have a window full of T-SQL batch statements, your error message will be relative to the last "GO" in the editor before the syntax error:

In SSMS 2014, batch start locations aren't considered:

Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:

While all of these error messages point me in the right direction, I'm too lazy to try and remember how each version of SSMS refers to the error location.  This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

You can double click on the error message to be taken directly to the line number referenced!

I know this sounds simple, but it's one of those small things I never thought of trying until I saw someone else do it.  Little did I know what I was missing out on - especially in those giant hundreds/thousands of line long scripts.  but now that I know about it it's the only way I locate my query errors.

This functionality also works if you have multiple errors listed in the Messages window.

But what about if your SQL query is all on one giant line (like if it was copied from somewhere or generated dynamically)?  You can use a regular expression to format your query first and then click on the error message to quickly navigate to the correct lines.

Last week we looked at how easy it is to import GeoJSON data into SQL Server's geography datatype.

Sometimes your source data won't be perfectly formatted for SQL Server's spatial datatypes though.

Today we'll examine what to do when our geographical polygon is showing us inverted results.

Watch this week's video on YouTube

Colorado Is A Rectangle

If you look at the state of Colorado on a map, you'll notice its border is pretty much a rectangle.

Roughly marking the lat/long coordinates of the state's four corners will give you a polygon comprised of the following points:

Or in GeoJSON format (set equal to a SQL variable) you might represent this data like so:

DECLARE @Colorado nvarchar(max) = N'
{
    "type": "FeatureCollection",
    "features": [{
        "type": "Feature",
        "properties": {},
        "geometry": {
            "type": "Polygon",
            "coordinates": [
                [
                    [-109.05005693435669,
                        41.0006946252774
                    ],
                    [-102.05157816410065,
                        41.002362600596015
                    ],
                    [-102.0421314239502,
                        36.993139985820925
                    ],
                    [-109.04520750045776,
                        36.99898824162522
                    ],

                    [-109.05005693435669,
                        41.0006946252774
                    ]
                ]
            ]
        }
    }]
}
'

Note: four points + one extra point that is a repeat of our first point - this last repeated point let's us know that we have a closed polygon since it ends at the same point where it began.

Viewing Our Colorado Polygon

Converting this array of points to the SQL Server geography datatype is pretty straight forward:

SELECT
    geography::STPolyFromText(
        'POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))'
        ,4326) AS StateBoundary
FROM
    (
    SELECT 
        Long,
        Lat
    FROM
        OPENJSON(@Colorado, '$.features[0].geometry.coordinates[0]')
        WITH
            (
                Long varchar(100) '$[0]',
                Lat varchar(100) '$[1]'
            )
)d

We can then take a look at SQL Server Management Studio's Spatial Results tab and see our polygon of Colorado drawn on a map.  You might notice something looks a little funny with this picture though:

WHY IS MY POLYGON AREA INVERTED?!!??!

Discerning eyes might notice that SQL Server didn't shade in the area inside of the polygon - it instead shaded in everything in the world EXCEPT for the interior of our polygon.

If this is the first time you've encountered this behavior then you're probably confused by this behavior - I know I was.

The Left-Hand/Right-Hand Rules

There is a logical explanation though for why SQL Server is seemingly shading in the wrong part of our polygon.

SQL Server's geography datatype follows the "left-hand rule" when determining which side of the polygon should be shaded.  On the contrary, the GeoJSON specification specifies objects should be formed following the "right-hand rule."

The left hand rule works like this: imagine you are walking the path of polygon - whatever is to the left of the line you are walking is what is considered the "interior" of that polygon.

So if we draw arrows that point in the direction that the coordinates are listed in our GeoJSON, you'll notice we are making our polygon in a clockwise direction:

If you imagine yourself walking along this line in the direction specified, you'll quickly see why SQL Server shades the "outside" of the polygon: following the left-hand rule, everything except for the state of Colorado is considered the interior of our polygon shape.

Reversing Polygon Direction

So the problem here is that our polygon data was encoded in a different direction than the SQL Server geography datatype expects.

One way to fix this is to correct our source data by reordering the points so that the polygon is drawn in a counter-clockwise direction:

-- Note: The middle three sets of points have been included in reverse order while the first/last point have stayed the same
DECLARE @ColoradoReversed nvarchar(max) = N'
{
    "type": "FeatureCollection",
    "features": [{
        "type": "Feature",
        "properties": {},
        "geometry": {
            "type": "Polygon",
            "coordinates": [
                [
                    [-109.05005693435669,
                        41.0006946252774
                    ],
                    [-109.04520750045776,
                        36.99898824162522
                    ],
                    [-102.0421314239502,
                        36.993139985820925
                    ],

                    [-102.05157816410065,
                        41.002362600596015
                    ],

                    [-109.05005693435669,
                        41.0006946252774
                    ]
                ]
            ]
        }
    }]
}
'

This is pretty easy to do with a polygon that only has five points, but this would be a huge pain for a polygon with hundreds or thousands of points.

So how do we solve this in a more efficient manner?

Easy, use SQL Server's ReorientObject() function.

SELECT
    geography::STPolyFromText(
        'POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))'
        ,4326).ReorientObject() AS StateBoundary
FROM
    (
    SELECT 
        Long,
        Lat
    FROM
        OPENJSON(@Colorado, '$.features[0].geometry.coordinates[0]')
        WITH
            (
                Long varchar(100) '$[0]',
                Lat varchar(100) '$[1]'
            )
)d

ReorientObject() does what we did manually above - it manipulates the order of our polygon's points so that it changes the direction in which the polygon is drawn.

Note: SQL uses a different order when reversing the points using ReorientObject() than the way we reversed them above.  The end result ends up being the same however.

Regardless of which method you choose to use, the results are the same: our polygon of Colorado is now drawn in the correct direction and the Spatial Results tab visually confirms this for us:

A significant portion of Yellowstone National Park sits on top of a supervolcano.  Although it's not likely to erupt any time soon, the park is constantly monitored for geological events like earthquakes.

This week I want to take a look at how you can import this earthquake data, encoded in GeoJSON format, into SQL Server in order to be able to analyze it using SQL Server's spatial functions.

Watch this week's video on YouTube

GeoJSON

The source for the data we'll be using is the 30-day earthquake feed from the USGS.  This data is encoded in the GeoJSON format, a specification that makes it easy to share spatial data via JSON.  To get an idea of how it looks, here's an extract:

{
    "type": "FeatureCollection",
    "metadata": {
        "generated": 1515603955000,
        "url": "https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/1.0_month.geojson",
        "title": "USGS Magnitude 1.0+ Earthquakes, Past Month",
        ...
    },
    "features": [{
            "type": "Feature",
            "properties": {
                "mag": 1.25,
                "place": "7km NW of The Geysers, CA",
                "time": 1515603166510,
                ...
            },
            "geometry": {
                "type": "Point",
                "coordinates": [-122.8221664, 38.8175011, 2.02]
            },
            "id": "nc72952110"
        },
        ...
        ],
    "bbox": [-179.921, -62.519, -3.35, 179.9551, 85.3951, 608.58]
}

The key thing we'll be examining in this data is the "features" array: it contains one feature object for each earthquake that's been recorded in the past 30 days.  You can see the "geometry" child object contains lat/long coordinates that we'll be importing into SQL Server.

If you want the same 30-day GeoJSON extract we'll be using in all of the following demo code, you can download it here.

Importing GeoJSON into SQL Server

There's no out of the box way to import GeoJSON data into SQL Server.

However, using SQL Server's JSON functions we can build our own solution pretty easily.

First, let's create a table where we can store all of earthquake data:

CREATE TABLE dbo.EarthquakeData
(
    Id int IDENTITY PRIMARY KEY,
    EventDate DATETIME2,
    Magnitude FLOAT,
    Place VARCHAR(300),
    Coordinates GEOGRAPHY,
    Long varchar(100),
    Lat varchar(100)
)

CREATE SPATIAL INDEX IX_Coordinates ON dbo.EarthquakeData (Coordinates)

Then, let's use the OPENJSON() function to parse our JSON and insert it into our table:

DECLARE @GeoJSON nvarchar(max) = N'{...}' -- Put the GeoJSON here

INSERT INTO dbo.EarthquakeData (EventDate,Magnitude,Place,Coordinates,Long,Lat)
SELECT 
    -- Convert unix epoch time to datetime
    -- We also know the source is in UTC, so we specify that using AT TIME ZONE for easier conversions down the road
    DATEADD(second,cast(UnixMillisecondsSinceEpoch/1000 as int),'1970-01-01 00:00:00') AT TIME ZONE 'UTC' AS EventDate,
    Magnitude,
    Place,
    -- Build our geography datatype
    geography::STPointFromText('POINT ('+Long + ' ' + Lat + ')', 4326) AS Geography,
    Long,
    Lat
FROM 
    OPENJSON(@GeoJSON, '$.features')
        WITH (
            UnixMillisecondsSinceEpoch bigint '$.properties.time',
            Magnitude float '$.properties.mag',
            Place varchar(300) '$.properties.place',
            Long varchar(100) '$.geometry.coordinates[0]',
            Lat varchar(100) '$.geometry.coordinates[1]'
        )

We use OPENJSON() to parse our JSON hierarchy and then concatenate together the lat and long values into our well known text format to be able to use it with SQL Server's spatial function STPointFromText:

SELECT geography::STPointFromText('POINT ('+Long + ' ' + Lat + ')', 4326) AS Geography

What results is our earthquake data all nicely parsed out into our dbo.EarthquakeData table:

What about Yellowstone?

The above data includes earthquakes from around world.  Since we only want to examine earthquakes in Yellowstone, we'll need to filter the data out.

There's a handy Place column in the data that we could probably add a LIKE '%yellowstone%' filter to - but this is a post about spatial data in SQL, we can do better!

The Wyoming State Geological Survey website has Shapefiles for the boundary of Yellowstone National Park.  Since we are practicing our GeoJSON import skills, I converted the Shapefiles to GeoJSON using an online converter and the resulting data looks like this:

{
    "type": "FeatureCollection",
    "bbox": [-111.15354785438899, 44.13238494057162, -109.816111731858, 45.102865336505396],
    "features": [{
        "type": "Feature",
        "geometry": {
            "type": "Polygon",
            "coordinates": [
                [
                    [-111.0970801722779, 44.487322588834374],
                    [-111.09707931336956, 44.48929779632266],
                    [-111.09707877845554, 44.49109904485493],
                    [-111.09707888353985, 44.49472122457225],.
                    ...
                ]
            ]
        },
        ...
    }]
}

You can download the full park boundary GeoJSON file here.

Just like before, we'll use SQL Server's OPENJSON() function to parse our GeoJSON data into a well-known text POLYGON.

First we create our table:

DROP TABLE IF EXISTS dbo.ParkBoundaries
CREATE TABLE dbo.ParkBoundaries
(
    Id int IDENTITY PRIMARY KEY,
    ParkName varchar(100),
    ParkBoundary GEOGRAPHY
)
CREATE SPATIAL INDEX IX_Boundary ON dbo.ParkBoundaries (ParkBoundary)

And then populate it, this time using the STPolyFromText spatial function:

INSERT INTO dbo.ParkBoundaries (ParkName, ParkBoundary)
SELECT
    'Yellowstone National Park' AS ParkName,
    geography::STPolyFromText('POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))',4326).ReorientObject() AS ParkBoundary
FROM
    (
    SELECT 
        Long,
        Lat
    FROM
        OPENJSON(@Yellowstone, '$.features[0].geometry.coordinates[0]')
        WITH
            (
                Long varchar(100) '$[0]',
                Lat varchar(100) '$[1]'
            )
)d

Filtering our data

Now we have two tables: dbo.EarthquakeData and dbo.ParkBoundaries.  What we want to do is select only the Earthquake data points that fall within the boundaries of Yellowstone National Park.

This is easy to do using the STIntersects spatial function, which returns a "1" for any rows where one geography instance (our lat/long earthquake coordinate) intersects another geography instance (our park boundary):

SELECT 
    d.Id,
    d.Magnitude,
    d.EventDate,
    d.Place,
    b.ParkName
FROM 
    dbo.EarthQuakeData d
    CROSS JOIN dbo.ParkBoundaries b
WHERE
    Coordinates.STIntersects(ParkBoundary) =1
    AND b.ParkName = 'Yellowstone National Park'
ORDER BY
    Magnitude DESC

The rest is up to you

So all it takes to import GeoJSON data into SQL Server is knowing how to use SQL Server's JSON functions.

Once geographical data is imported into geography data types, SQL Server's spatial functions offer lots of flexibility for how to efficiently slice and dice the data.