Voice of the DBA

Category: Blog

SQL Injection Issues–Password Hashing
I’ve got a demo for one of my talks that really highlights some issues we have with SQL Injection. It’s part of my encryption talk, and it goes like this.

NOTE: I am showing a simple example here, not one that I would deploy into production. The concepts are similar, but this specific code is not designed or applicable for cut/paste into a production system.

Imagine I have a simple table of users and passwords.
```
go
create table UserTest
( firstname varchar(50)
, passwordhash varbinary(max)
);
go
-- insert passwords
insert usertest select 'Steve', HASHBYTES('SHA2_512', 'AP@sswordUCan!tGuess');
insert usertest select 'Andy', HASHBYTES('SHA2_512', 'ADiffP@sswordUCan!tGuess');
go
```
I’ve got two users and a fairly strong hash of their passwords. I’m using the SHA2 algorithm, at 512 bits, and complex passwords. I’m showing this in T-SQL, though you could easily hash these passwords in the application layer and just store the values in the database.

I create a simple proc that takes a username and a password as parameters.
```
create procedure CheckPassword
   @user varchar(200)
 , @password varchar(200)
as
if hashbytes('SHA2_512', @password) = (select passwordhash 
                                 from UserTest
                                 where firstname = @user
                                )
  select 'Password Match'
else
  select 'Password Fail'
  ;
return
go
```
NOTE: This is shown at the DB layer for simplicity, but having a user’s password transit the network in plaintext and be passed to a proc is a poor practice. It would be better to hash this and only send the hash to SQL Server.

If I want to verity a user, I can do this:
```
declare @p varchar(200);
select @p = 'AP@sswordUCan!tGuess';
exec CheckPassword 'Steve', @p;
go
```
The result of this call is the password matches.

If I try a different password, say the one for the other user, it will fail.

That’s good. This is very similar to how many applications, including AD and SQL Server, validate users. However, here’s one problem with a simplistic implementation like this.

Imagine that through SQLInjection, someone learns the structure of the table. Not hard to do. Now the data in the table is hashed, and there are lots of hashing algorithms. Certainly it’s a lot of work to try all different combinations of possible passwords, and algorithms to find a match. It’s possible and it’s a brute force attack.

Here’s the data in the table.

However, the hacker, Andy, doesn’t need to decode the password. Imagine that the hacker creates his own account, which is probably a low level account. However the hacker runs code like this, substituting different accounts for “Steve” until a privileged account is found.

Now the attacker does this. They use my (Steve’s) privileged account, with their password:

The hacker (Andy), can now log in as Steve using his password. Any rights that are assigned to Steve are available for Andy.

We have an attack without decryption.

This is one reason that SQL Injection is a big problem in applications, especially those that implement some type of their own security. Solving this is slightly tricky, and I’ll talk about it in another post.

One side note, the only way this is usually detected is if Steve logs in with his password. He’ll see this:

Even then, unless Steve suspects an attack, he might write this off to a mistyped password, try multiple times and eventually reset his password without a second thought.
December 4, 2014
T-SQL Tricks – Customizing SSMS Templates with Parameters
I wrote briefly about templates in Management Studio (SSMS), and showed the default templates that come with SQL Server. I now want to customize some of the templates in a way that makes sense for me.

If I grab a script I use often, like this one, I can make it generic.
```
SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = 'bob@bob.com'
    GROUP BY
        username
    ,   topic;
```
I run this often to check things, but I rarely need Bob’s information. Instead, I’ll often get different users, and sometimes I need dates. I can add these changes:
```
SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = '<email, varchar, bob@bob.com>'
    AND startdate > <startdate, datetime, dateadd(m, -1, getdate())> 
    AND enddate <lessthan, char <> <enddate, datetime, getdate()>
    GROUP BY
        username
    ,   topic;
```
I’ve changed some of my variable items to parameters. I do this by taking an item that I want to make variable, like “bob@bob.com” and changing it to “<email, varchar, bob@bob.com>”.

The format for a template is:
- name
- type
- default
all of which are placed inside angled brackets and separated by commas. Now when I click CTRL+Shift+M, I get this:

I can click OK for the defaults to be placed in the script, or I can enter new ones. Either way, I save time and effort with saved queries, but saved as templates, not queries I need to edit constantly.

UPDATE: Someone pointed out that the less than, the <, was . I got this from Stack Overflow, which had a good solution. I made the < a parameter as well.
December 3, 2014
Creating Placeholder Files

I recently wrote about placeholders for disk space. While you can use any file, like large images, video, etc., I’ve found a really simple, easy way to build these files on Windows.

Contig.exe

There’s a sysinternals utility called Contig. You can download it from Microsoft and then unzip it on your system. It’s a command line utility, so you can use the /? to get the parameters from it, but here’s what you need to do: use the -n parameter, with a filename and a size.

That simple. Here’s a sample call:

And in text:

contig -n Hold1.place 1073741824

That creates a 1GB file (roughly) on my system. I can then copy this file as many times as I want to save space.

I use folder in the root called placeholder. I put one file in there and then copy it a few times to reserve space.

Why that size? 1024 * 1024 * 1024.

December 1, 2014
Placeholders for Emergencies

A long time ago I worked in a large corporation where we managed lots of servers, each of which performed a variety of different functions. Some were Exchange, some SQL, some file servers, etc. and they would come and go at a frightening pace.

We had monitoring in place, but we’d also have plenty of situations where a runaway process would use up a lot of disk space at a rate that exceeded the ability of the monitoring software to alert us before the user encountered a problem. To help us more effectively monitor things, we started adding placeholders do our server build process.

Placeholders

What’s a placeholder? It’s a large file that just takes up space on a disk. For example, I’ve created a few on my system:

These can be text files, movies, images, whatever you want. The idea is that you just save a particular amount of space. In this case, I have 3 1GB files that are taking up space.

That’s it.

If I run out of disk space, I just delete one of these, and voila, instant 1GB space available.

These have become really, really handy for me. I actually have a few on my laptop. When I run low on space, which will often happen at THE WORST possible time, like the morning of a talk, I can remove a file and free up space.

Of course, I’ve just deferred the issue, but at least then I can work at that moment and then clean up old files as I have a moment.

Placeholders are a great way to save yourself in emergencies.

November 24, 2014