Voice of the DBA

Tag: security

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
Encryption in Colorado Springs – Encrypting in the Application?

Last night was my annual presentation at the Colorado Springs SQL Server User Group. I try to make sure I get down there at least once a year, and it’s been only once a year for the last few years. Far too busy, and I’m sorry for that, but I am glad I get invited to go down.

I presented The Encryption Primer, and there were a few interesting questions asked. Always good to see people debating and asking questions.

One interesting one from a developer – If I can perform encryption in the application, why would I do use something like TDE or column level encryption?

To me, I prefer to do encryption as close to the source as possible. If I can do the encryption in the application front end, I’d do it there. It reduces the chances of having the data accidentally disclosed. I don’t have to worry about having data read across the wire, or in a backup tape, or anywhere else.

However that takes time and effort. Developers are expensive, and they have to write good, solid, secure code in the application. They also have to write this encryption code in every application that accesses the database (reports, ETL, etc.).

Something like TDE is much easier to setup and use. Column level encryption, while still coding, is centralized.

It’s a balance, and one you need to consider carefully and thoroughly. It also helps to debate and discuss the decisions about what you protect, why, and what it costs.

November 20, 2014
MY Data

Data is merely a set of numbers. However when data is given context, it allows conclusions to be drawn and many of us make a living managing some part of that process every day. Most of us deal with data that is viewed as somewhat public, related to our interactions with businesses and organizations. However we see some of this changing as employers start to gather more data on us through social media and other sources, beginning to use that data to make hiring decisions.

It can get worse. As Shane Battier says in this article says, “big data is scary.”

Most of us don’t aren’t hired for our physical performance. While our health can make a difference in how well we write code over time, the regular, subtle decisions of drinking juice or soda don’t affect our performance much on a daily basis. We could argue about that, but from the employer’s perspective, I’m not sure those things matter.

Or do they? If the cost of employees rises over time, especially with regard to health care or sick time, should employers start to make decisions based on the data? Are we confident in the conclusions from data, which are really probabilities, not actualities? Would you want aspects of your life, perhaps outside of your health (think driving, finance, etc) to be part of the evaluation (or negotiation) process for your employment?

Perhaps a lighter question, would you be comfortable managing and writing applications that work with employee data and try to analyze, perhaps even strongly recommend, changes in peoples’ live? I’m not sure I would, and I certainly hope we don’t get to the point where the data about our personal lives directly impacts the way we are managed.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

November 18, 2014
It’s Not Just Poor Coding

We hear regularly about SQL injection continuing to be a problem (it is) and malware causing data loss for many companies. We’ve got misconfigured firewalls and backup tapes being lost. However even if we could solve all those issues, we’d still have security issues.

A new study shows that many workers share a tremendous number of files with coworkers, and potentially friends on a regular basis through the various cloud services. I don’t worry so much about cloud service providers being hacked themselves, or their employees selling information. It could happen, and probably does, but it’s also likely just isolated incidents that have limited impact. However I’m more worried about the poor security practices of the employees.

Many people tend to use the same passwords on different sites (a bad practice). They also often open attachments from “friends”, unaware of how dangerous it can be to pass along and share documents that can embed malware. Malicious coders are becoming smarter, learning to make their actions more subtle, often only infecting a device to use it to send emails or files to other users who are the real target of the malware. With all of these issues, it’s much more likely that a user will end up creating their own hack for malicious servers than targeted attacks will work. It’s also highly possible that many users will share documents with friends, unaware that they might be exposing corporate data to the general public with confusing settings in some of these services.

Ultimately I’m becoming more cautious about sharing information with others. I won’t open many files sent to me by people I don’t know well, and never look at any “humorous” attachments. I won’t accept a USB key from you and plug it into my laptop. It’s sad because we’ve built some amazing services to allow us to communicate easier and faster than ever before, however we’ve also not built these applications with an eye towards security first, and convenience second.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

November 13, 2014