Data Management

Secure SQL Server: Encryption and SQL injection attacks

A total security plan for SQL Server should include encryption and protection against SQL injection attacks. In this last article of the series, we give you a lesson in SQL Server security that could save you from making some expensive mistakes.


By Susan Sales Harkins and Mike Gunderloy

Welcome to the final installment of our series on SQL Server security. So far, we've explained how to set up your SQL Server, how to manage users and groups, how to manage objects and permissions, and how to set up application roles. Today we're going to dig into a final two topics that don't fit anywhere else: encryption and SQL injection.

Builder's series on SQL Server security
Our goal is to give you the tools and the confidence you need to set up SQL Server securely, so that your data is protected from accidental or deliberate theft and corruption. Our series starts with some of the basic decisions and techniques for making sure that you have a secure platform to build upon. Throughout the series, we've concentrated on SQL Server 2000, which introduces some new security features to help protect your data. In case you've missed the previous articles in this series, you can read them here:

Encryption in SQL Server
SQL Server has built-in encryption to protect various types of sensitive data. In some cases, this encryption is completely transparent to you; things are encrypted when they're stored and decrypted automatically when they're used. In other cases, you can choose whether data should be encrypted or not. SQL Server can encrypt the following components:
  • Passwords
  • Definitions of stored procedures, views, triggers, user-defined functions, defaults, and rules
  • Data sent between the server and the client

Password encryption
SQL Server automatically encrypts the passwords that you assign to logins and application roles. Even if you look directly into the system tables in the master database, you won't find actual passwords. You don't need to do anything to enable this feature; in fact, you can't disable it.

Definition encryption
In some cases, the definitions of objects may be information that you don't want to share with everyone. For instance, a stored procedure can contain proprietary business logic that shouldn't be available to all users, even if they can open the system tables and look at the object definition. That's why SQL Server lets you encrypt object definitions when you create an object. To encrypt a stored procedure, use the CREATE PROCEDURE statement in this form:
CREATE PROCEDURE procedurename [;number]
[@parameter datatype
[VARYING][ = defaultvalue][OUTPUT]]
[, …]
[WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION]


We're concerned with only the optional WITH argument. You can specify either RECOMPILE or ENCRYPTION, or you can use them together. The ENCRYPTION keyword prevents SQL Server from publishing the procedure. As a result, the system stored procedure sp_helptext, will be ignored if ENCRYPTION is enabled. That particular stored procedure displays the text used to create the procedure. If you ever want to disable encryption, you can use ALTER PROCEDURE to recreate the stored procedure, omitting the WITH ENCRYPTION clause.

Data encryption
SQL Server also supports encrypting all data sent between the server and a client on the network. This is especially useful when the client is on the public Internet, and you're worried about someone in between the client and server being able to sniff the network traffic and intercept confidential data.

To enable encryption, both client and server should be using the TCP/IP network library for communication. Run the appropriate network utility (go to Start | Program Files | Microsoft SQL Server | Server Network Utility on the server, and Start | Program Files | Microsoft SQL Server | Client Network Utility on each client) and check the Force Protocol Encryption check box as shown in Figure A. Connections between client and server will now be encrypted.

Figure A
Setting up encryption in the Server Network Utility


Encryption isn't completely free. There's some extra setup that goes on when a connection is established, and both the client and server must run the code to encrypt and decrypt packets. So there will be some overhead and perhaps a measurable slow down with encryption turned on. But if the network packets (the actual data traveling over the wire between client and server) are ever out of your control, it's probably a good idea to do so.

What's missing from encryption?
You may have noticed something obviously missing from this list of things that can be encrypted: the data in your tables. SQL Server doesn't offer any built-in support for encrypting your own data before you store it. If you need to protect the data stored in SQL Server, we offer two pieces of advice. First, you can use the GRANT and DENY keywords to control who can read data within SQL Server, as we discussed in an earlier installment of this series. If people can't open the table at all, it shouldn't matter whether the data is encrypted.

Second, if you really do want to encrypt the data, don't try to roll your own encryption. There are a number of commercial products out there with tested encryption algorithms that you can use. You'll find a good list at SQLSecurity FAQ.

SQL injection
SQL injection is the name for a general class of attacks that can allow nefarious users to retrieve data, alter server settings, or even take over your server if you're not careful. SQL injection is not a SQL Server problem, but a problem with improperly written applications. But you're likely to run across such an application some time in your career, so you should understand the problem and the cure.

Spotting the vulnerability
SQL injection vulnerabilities occur when well-meaning application developers build a WHERE clause with user input. For example, a simple ASP application might allow the user to input a customer ID and retrieve the company's contact name. If the customer ID comes back as part of the request string from the ASP page, the developer could write this code to get the data:
strConn = "Provider=SQLOLEDB;Data Source=(local);" & _
 "Database=Northwind;Integrated Security=SSPI"
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open  strConn
strQuery = "SELECT ContactName FROM Customers " & _
 “WHERE CustomerID = '" & Request.Form("CustID") & "'"
Set rstResults = cnn.Execute(strQuery)
Response.Write(rstResults.Fields("ContactName").Value)


So, where's the problem? If users know a customer ID, they can get back the corresponding contact name. Looks safe, right?

Getting extra data
Well, not quite. For starters, the attacker can get back a contact name even if she doesn't know any customer ID at all—and without guessing one. To do so, she would enter this text in the textbox where the application calls for a customer ID:
'UNION ALL SELECT ContactName FROM Customers
 WHERE CustomerID <>'


If you follow the code, you'll see that it turns this into a query:
SELECT ContactName FROM Customers
 WHERE CustomerID = ''
UNION ALL SELECT ContactName FROM Customers
 WHERE CustomerID <>''


By taking the union of blank customer IDs and non-blank customer IDs, this query gives back all contact names in the database. The Response.Write statement would happily print the first of these names to the response page. In fact, this UNION technique can be used to get almost anything out of your database. Take a look at this CustomerID value:
'UNION ALL SELECT FirstName + ' ' + LastName FROM
 Employees WHERE LastName <>'


It turns the SQL statement into:
SELECT ContactName FROM Customers
 WHERE CustomerID = ''
UNION ALL SELECT FirstName + ' ' + LastName FROM
 Employees WHERE LastName <>''


Voila, the attacker now has the first employee name from your database.

But wait, there's more
If SQL injection was just a data exposure vulnerability, it would be bad enough. But in fact a determined attacker can exploit this mistake to do almost anything. Here's one nasty example of a bit of SQL that could be injected:
';DROP TABLE Customers;—

The SQL statement then becomes:
SELECT ContactName FROM Customers
 WHERE CustomerID = ''
;DROP TABLE Customers;— '


The semicolon is a statement separator for SQL Server, so there are actually two statements here. The first selects a nonexistent contact name, and the second drops the entire Customers table! The double dash (—) is a SQL Server comment character, which prevents the trailing quote from causing a syntax error.

Using this variant of the technique, an attacker can run any SQL statement or stored procedure on your server. By using the xp_cmdshell extended stored procedure, an attacker can also run operating system commands. Obviously this is a severe problem.

Protecting yourself
So, how do you prevent SQL injection? The first answer is that you simply can't build WHERE clauses from user input. Instead, you should use stored procedures with parameters. In the case of the original ASP page that we started, this section with the rewrite would look something like what's shown in Listing A.

And even if you think there are no such vulnerabilities in your applications, you should always follow the principle of least privilege. That is, use the other security techniques that we've discussed to allow your users access to only the data that they should be able to use. That way, even if there is a vulnerability you've missed, it won't end up a disaster.

Final wrap up
That's it for our SQL Server security series! You may not be the complete expert now, but you've seen a lot of the territory. The next time that you have data to protect in SQL Server, remember the things that you've learned here, and you'll be well on your way to keeping your data safe from prying eyes.

Editor's Picks