lanning for database security means a number of things. First, you need to take care that the code you send to SQL Server is not vulnerable to SQL injection. Second, you need to keep up with and apply the latest Microsoft SQL Server security updates. Third, after you take care of some of the basic SQL Server security measures, you should test your servers using the Microsoft Baseline Security Analyzer, and also pay attention to other third party utilities.
There’s been an intense focus on all aspects of security in the past year, and SQL Server security is no exception. It’s no longer a compartmentalized concern; security is a concern for everyone. As a SQL Server developer you should be aware of the major security vulnerabilities of SQL Server. In this article you’ll learn how to address the major vulnerabilities in your code; how Microsoft is adding security patches to SQL Server, and some best practices and tools you can use to assess your SQL Server’s security.
By far the most important vulnerability in SQL Server code is SQL injection. SQL injection is an attempt to trick the SQL Server query engine into executing additional code not intended by the application. Any SQL-based database that allows dynamic code generation is vulnerable to this technique, not just Microsoft SQL Server. (See Sidebar 1: Detecting Dynamic SQL)
Vulnerability arises when an application dynamically generates commands and sends them to SQL Server for execution. To exploit this vulnerability the intruder simply appends executable code to a text field that provides input for the query. Under the right conditions, an intruder can experimentally find the right combination of quote delimiters and comment marks to modify the current command or append an unwanted command. The results can range from returning otherwise hidden data to the user to letting the user execute commands that they would normally not be able to do from a form.
To understand how SQL injection works consider this simple query. This query selects the lastname and first name from the authors table using the city as search criteria. For the moment, let’s ignore how the query is built.
SELECT au_lname, au_fname FROM authors WHERE city = 'Berkeley'
To inject unwanted code the intruder indirectly modifies the query so that it can actually execute something not originally intended, such as:
SELECT au_lname, au_fname FROM authors WHERE city = 'Berkeley' UNION SELECT name, password FROM syslogins
The way the intruder accomplishes that is by appending a long string (without carriage returns) that contains the UNION clause.
What the intruder needs to make the injection work is to have either your client-side code or the Transact-SQL in SQL Server dynamically generate the code. Then there’s a good chance it might work. Take a look at the following Transact-SQL example:
DECLARE @city NVARCHAR(2000), @cmd NVARCHAR(2000) SET @city = 'Berkeley'' UNION SELECT name, password FROM master.dbo.syslogins --' SET @cmd = 'SELECT au_lname, au_fname FROM authors WHERE city = ''' + @city + ''' ORDER BY city' EXEC (@cmd)
The two single quotes are required right after Berkeley so that SQL Server will create one embedded single-quote mark in the variable. Alternatively, in your input form you might have this code:
Set oRecordset = oConnection.execute( "SELECT au_lname, au_fname FROM authors WHERE city ='" & request.form("txtCity") & "'")
Both are vulnerable. An intruder can change your generated string so that the SQL parser will accept the input as a UNION with a second SELECT.
From the input form, the intruder could enter this string:
Berkeley' UNION SELECT name, password FROM master.dbo.syslogins --
The resulting string will be sent to SQL Server.
SELECT au_id FROM authors WHERE city = 'Berkeley' UNION SELECT name FROM master.dbo.syslogins --' ORDER BY city
How does the hack work? Since the embedded quote mark comes right after the input value, the parser will see it as the end of the string comparison so the WHERE clause is complete. Then the UNION command tells the parser that the initial SELECT statement has ended and a new SELECT statement must follow. After the second SELECT statement the intruder’s two dashes comment out the dynamically appended quote mark that was supposed to terminate the original string.
To make SQL injection work an intruder needs dynamically generated SQL where terminating quotes are added to a string. If the application uses a statically formed SELECT statement and a variable, the string termination is implicit, the entire entered string will become the search argument of the original SELECT statement, and injection will not work. In other words, when the string termination is implicit, the resulting command will cause SQL Server to look for a city named “Berkeley’ UNION SELECT name, password FROM master.dbo.syslogins–“, and the result is harmless.
Preventing SQL Injection
There are numerous ways you can prevent or deter SQL injection when working with SQL Server. Let’s start with the most secure.
Avoid Dynamic SQL
You can easily eliminate the possibility of SQL injection by not dynamically constructing SQL commands in the first place. One of the easiest ways to do that with SQL Server is to call stored procedures that contain the SQL string and pass your input string to the stored procedure as a parameter:
-- Not vulnerable to injection CREATE PROC usp_AuthorGetName @city nvarchar(2000) AS SELECT au_lname, au_fname FROM authors WHERE city = @city RETURN
The advantage is that whatever the user enters is considered the entire comparison value. Instead of being able to inject a SQL command the intruder simply adds characters, most likely meaningless, to the string.
Note that if you use dynamic SQL in a stored procedure you may end up reintroducing injection vulnerability:
-- Vulnerable to injection CREATE PROC usp_AuthorGetName @city nvarchar(2000) AS DECLARE @cmd nvarchar(2000) SET @cmd = 'SELECT au_lname, au_fname FROM authors WHERE city = ''' + @city + '''' EXEC (@cmd) RETURN
Use sp_executesql with Parameters
If you cannot change your code to call stored procedures you may be able to use sp_executesql. Suppose your form creates a query with a dynamically generated list of columns and search arguments. If you used a stored procedure and tried to pass in the column names as parameters, you’d still have to use dynamic SQL because you cannot use a variable in a column or table name. However, you can dynamically construct a column list and then send parameter(s) for the search argument(s) using the system stored procedure sp_executesql. The following example parameterizes the search argument and prevents injection:
-- Not vulnerable to injection DECLARE @OuterVar nvarchar(2000), @cmd nvarchar(2000) SET @cmd = 'SELECT au_lname, au_fname FROM authors WHERE city = @Innervar' SET @OuterVar = ''' UNION Select login, password from master..syslogins --' EXEC sp_executesql @cmd, N'@Innervar nvarchar(2000)', @Innervar = @OuterVar
You’ve got to make sure to send the parameter into sp_executesql. If you send the raw command without any parameters, you reintroduce the possibility of injection:
-- Vulnerable to injection DECLARE @VAR nvarchar(2000), @cmd nvarchar(2000) SET @var = ''' UNION Select name, password from master..syslogins --' SET @cmd = 'SELECT au_lname, au_fname FROM authors WHERE city = ''' + @var + '''' EXEC sp_executesql @cmd
Limit the Size of Input Strings
In the examples above the strings have deliberately been made much longer than necessary to accommodate the SQL injection example. If the city name were limited to, say, 30 characters that would dramatically reduce if not eliminate the number of SQL injection possibilities.
Replace a Single Quote with Two Single Quotes
If you must dynamically construct your string and you cannot use sp_executesql, you can replace a single quote with two single quotes in your input string. This can be a very effective measure for client-side code and can work well in stored procedures. Here’s the previous stored procedure rewritten so that replacing the single quote stops the injection:
-- Replace stops the injection CREATE PROC usp_AuthorGetName @City nvarchar(2000) AS DECLARE @cmd nvarchar(2000) SET @city = REPLACE(@city, '''', '''''') SET @cmd = 'SELECT au_lname, au_fname FROM authors WHERE city = ''' + @city + '''' EXEC (@cmd) RETURN
The generated command behaves the same as when you parameterize the search argument in a variable; the comparison value becomes the entire string, and the intruder’s single quote is taken as an embedded quote mark, not the end of a string.
Scan for Injected Strings
You could scan the entered text looking for SQL keywords such as UNION in a case-insensitive manner, or scan for a single quote mark, or the two dashes that comment the ending string. This may not be possible for all input text because there are streets and cities named ‘union’, there are names with single quotes in them (O’Shaunessy), and so on.
The Microsoft SQL Server group has been busy these past months producing cumulative patches for both SQL Server 7.0 and 2000. Presumably all the patches will be rolled up in future service packs but it’s reasonable to expect that there will be subsequent patches after those service packs are released. Let’s drill down into what the SQL Server 2000 security patches provide, how you can install them, and how you can test your installation. (See Sidebar 2: A New Departure)
Each security patch contains a readme.txt file that instructs you to install the patch on the correct service pack (SQL Server 2000 SP2 has a version level of 8.00.534; SP3 is due out in late 2002.) After you apply the security patch, your SQL Server’s version number will be incremented. This incremented build number implies that in addition to getting the security fixes, you are also receiving an executable containing other miscellaneous patches, the ones you would normally have to contact Microsoft PSS to receive.
At the time this article was written the security patches require you to install them manually, unlike service packs. By the end of 2002, it is likely the SQL Server group will have automated the security patch system so that it’s more like a service pack install. Until the security patch system is automated you’ll have to spend time automating the application of the patch. You may decide that the vulnerabilities fixed in a given patch do not affect your application and choose not to install the patch.
The full instructions for applying a given patch are in the readme.txt. If you have to apply the security patches manually, create a simple command file to apply them. There’s just enough file copying involved from different file folder locations to make one-at-a-time copying error-prone. Make sure you create a command file to back up the current binaries, copy the new ones in, and restore the old from the copy. One helpful suggestion is that you include the security patch number in the command filename so that you can clone them for the next patch after that. Be careful about file locations; it’s common on database servers to place data and log files for production databases on their own volumes. Test each security patch the same way you would a service pack.
One step may be confusing. The command-line executable, servpriv.exe, restricts registry permissions (obliquely described in the Microsoft Knowledge Base article Q322853 “FIX: SQL Server Grants Unnecessary Permissions or an Encryption Function Contains Unchecked Buffers.”) When you run this program you pass the name of the SQL Server instance as a parameter. For the default instance on your machine, it’s just
For a named SQL Server 2000 instance you just use the name of the instance without a machine prefix. Normally on a server called MyServer, with an instance named Intance1, you specify MyServerInstance1 whenever you connect to it or refer to it in Transact-SQL. But for this executable, you would actually type
Since the registry keys affected by servpriv.exe are not documented, it’s not clear how you would back out of its changes.
By the time you read this, Microsoft should be coordinating the Microsoft Baseline Security Analyzer (MBSA) with the security patch files and you can then use this utility to inspect a SQL Server and determine whether the latest patch has been applied. You can download it from Microsoft TechNet. It uses an XML file to periodically update the proper settings. MBSA can inspect SQL Server, Windows, and IIS security on your system, and it reports on SQL Server security issues not addressed by the patches. You’ll learn more about the Microsoft Baseline Security Analyzer later in this article.
Note that there’s a risk to applying the security patches. Because these fixes are coming out fairly rapidly they may not have been as well tested as a full service pack. It’s possible that they might introduce some regression or new bug. Another risk is that manually applying a patch can be error prone. Your best defense is to use command files that you test first.
SQL Server Security Best Practices
What should you do to make your SQL Server as secure as possible? Here’s a strategy that can help.
Do the Basics First
These basic steps are recommended so often now that they approach common sense:
- Use Windows authentication whenever possible for users and applications
- If you are using SQL Server authentication, secure your SA account with a strong password and only let a select few know it
- Assign users minimal permissions
- Deny access to tables and views in the database?have your application execute stored procedures to get data
- Don’t expose your SQL Server to the Internet; if you must, change it from port 1433 to some other port number and filter that port
- Give SQL Server and SQL Agent domain logins that do not require administrative access to the server
- Apply the latest SQL Server service pack and security patch
Test Using the Microsoft Baseline Security Analyzer
You can use a number of utilities to test your SQL Server. You can go to Microsoft’s site and download the Microsoft Baseline Security Analyzer, for example. This utility will scan your system for Windows, IIS, and SQL Server vulnerabilities and will present you a number of recommendations. You can see some sample output from the MBSA in Figure 1.
Share the Post:
The Role of Call Data: How Call Tracking Can Improve Customer Experience
Who would argue that experiences reign supreme? But not just any experiences — the ones that occur when customers interact with your business. It’s these real-life interactions that have the
Data Observability Explained
Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the
Logitech G502 Software: Optimize and Customize Your Gear
One of the most significant surges of the 21st century is gaming. Gaming is more popular than ever before thanks to innovative new consoles, high-tech PC setups, mobile gaming improvements,
Different Types of Data Models Explained with Examples
In the modern world, data is everything and everywhere. With so much access to technology, data has become a valuable resource for any business. Albeit a complex one. Data is
Revolutionizing Search: A Glimpse Into Google’s Generative Experience
Google is revolutionizing the search experience as we know it with its latest generative experience. No longer will you be bound by the limitations of traditional keyword searching. Now, you
10 Productivity Hacks to Supercharge Your Business in 2023
Picture this: your team working seamlessly, completing tasks efficiently, and achieving goals with ease. Sounds like too good to be true? Not at all! With our productivity hacks, you can
GM Creates Open Source uProtocol and Invites Automakers to Adopt It: Revolutionizing Automotive Software Development.
General Motors (GM) recently announced its entry into the Eclipse Foundation. The Eclipse Foundation is a prominent open-source software foundation. In addition, GMC announced its contribution of “uProtocol” to facilitate
What is Metadata?
What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular
What We Should Expect from Cell Phone Tech in the Near Future
The earliest cell phones included boxy designs full of buttons and antennas, and they only made calls. Needless to say, we’ve come a long way from those classic brick phones
The Best Mechanical Keyboards For Programmers: Where To Find Them
When it comes to programming, a good mechanical keyboard can make all the difference. Naturally, you would want one of the best mechanical keyboards for programmers. But with so many
The Digital Panopticon: Is Big Brother Always Watching Us Online?
In the age of digital transformation, the internet has become a ubiquitous part of our lives. From socializing, shopping, and learning to more sensitive activities such as banking and healthcare,
Embracing Change: How AI Is Revolutionizing the Developer’s Role
The world of software development is changing drastically with the introduction of Artificial Intelligence and Machine Learning technologies. In the past, software developers were in charge of the entire development