Voice of the DBA

Category: Blog

SQL in the City 2014 – Washington DC

I’m off today, traveling to SQL in the City 2014 – Washington DC to meet Grant and deliver another database delivery seminar. We’ll be talking about Version Control, testing, continuous integration, and database delivery, and showing off some of the Red Gate tools that can make the process easier.

I really think that you will reap benefits if you start to build a software delivery pipeline and incorporate some of the agile/ALM/DLM processes into your software build system. It’s some work up front, and maintenance ongoing, but it does allow you to regression test, rapidly review changes, and consistently deploy software to your production systems.

Not easy, but it does build solid engineering habits and should help you deliver software reliably, and quickly.

We’re looking at doing more seminars in 2015 and are planning things now. If you’re interested in having us some to your city, perhaps around a SQL Saturday, or maybe just because you don’t have any events, send a request to Red Gate. Use sqlinthecity@red-gate.com and let them know you want Grant and myself to swing by.

December 4, 2014
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