Welcome!

Microsoft Cloud Authors: Liz McMillan, Elizabeth White, Mihai Corbuleac, Pat Romanski, David Bermingham

Related Topics: Microsoft Cloud

Microsoft Cloud: Article

SQL Injection

Defeat one of the most common attacks

Many applications (Web-based applications and forms-based ["smart client"] applications) typically use data stored in a database. While you may have firewalls and other protections established when running your application, your application can still be open to an attacker gaining direct (or indirect) access to information in your database. The most common and dangerous attack technique is to use SQL injection.

SQL injection occurs when an attacker is able to insert a series of SQL statements into a "query" by manipulating data input into an application. This can happen because data input is not checked or "sanitized" before being entered into the database. All it takes is one input point through your application that can allow an attacker to retrieve sensitive and private information, change data, drop tables, and possibly shut down your database.

In this article, I will talk mostly about how SQL injection can be performed against SQL Server 2000 (using Transact-SQL code), but these techniques also apply to other databases such as Oracle, DB2, and MySQL, which vary only slightly in SQL syntax. I will also talk about best practices in writing correct code to counter SQL injection, as well as some ways to help audit your code for these problems.

How It's Done
SQL injection is primarily caused by developers who use "string-building" techniques for SQL statements that are executed in a database. An attacker can take advantage of code developed this way by passing commands directly to a database and then take advantage of a poorly secured system to leverage access privileges.

As an example of the simplest form of SQL injection, let's talk about a common entry point into most applications: a login or authentication form. The form may look something like Figure 1.

In order to identify the user's account, an SQL query may be written to look up the username and password in a users table in the database. For example, you may have C# code that builds the SQL statement this way:


string sql = "SELECT userid, first
name, lastname FROM users WHERE
username = '" + txtUsername.Text +
"' and password = '" + txtPassword.
Text + "'";

The user authenticates (i.e., "logs in") to the application by supplying their credentials, in this case, username and password. After this, if those credentials match what is stored in the database, the user is considered authenticated and the user's information is returned. For example, if the user name is "JohnSmith" and the password is "hsl33s7%," then the aforementioned SQL command that is sent into the database would look like this:


SELECT userid, firstname, last
name FROM users WHERE username =
'JohnSmith' and password = 'hsl33s7%'

Notice how the SQL query is formed. As it is written, I am only returning one user's information. However, what if I don't know the password or the username? This is the strategy of an attacker - figure out a way to exploit any vulnerability. In this case, the vulnerability is that I can send in valid SQL statements that will significantly change the final SQL query sent to the database. If you know a little SQL, you should know what happens when I send in this value in the Username field:


' OR 1=1 --

You get this result SQL statement:


SELECT userid, firstname, lastname
FROM users WHERE username = '' OR
1=1 --

What's happening here is the quote (') is used to end the open quote in the first username check and this is combined with a logical statement that will always evaluate to true ("OR 1=1"). Finally, SQL comments (--) are used to make SQL Server ignore the rest of the SQL query. By using this logical SQL query result, you can get a list of all users without knowing any username or password!

Advanced SQL Injection
At this point, if an attacker has found an entry point with problem code similar to the code shown above, then many valid SQL statements can be sent into the database. Taking the original SQL query above, I could send in this statement in the Username field:


' UNION SELECT null, name, null from
dbo.sysobjects where xtype = 'U' --

As you may know, when you form a UNION statement, you must have the same number of fields as the other SQL statement you are forming the UNION with. In this case, we knew there were three columns, but an attacker can use this method to determine how many fields there are to get the correct results. This particular statement gives you all the names of the tables contained within the database! Again, this is SQL Server-specific, but a similar construct could be used for Oracle, DB2, etc. An attacker can continue sending in SQL statements including INSERTs, UPDATEs, DELETEs, and even the Transact SQL command "SHUTDOWN" (yes, that will shutdown the database!).

Unfortunately, it doesn't end there. Many databases run with a high privileged account, or applications connect to the database with a high privileged account (i.e., "sa" - system administrator account). An attacker can use this fact to further make calls to extended stored procedures such as xp_cmdshell to drop down into a command shell on the database server to then call applications, import files, download password files, and ultimately use the database server as a starting point to attack other computers within the internal network. The possibilities are almost endless.

Protecting Against SQL Injection
How do you guard against SQL injection? One key technique that security experts talk about often and loud is Don't Trust User Input. In our example above, we trust the user to enter the correct credential information in the correct format (no SQL statements imbedded in the input). We are not validating the format of that input, and are therefore blindly letting that data go through the database. Don't do this!

When validating any data input, you should always check for what is expected and correct, and throw away the rest. What does that mean? One obvious method that many applications try to foil this type of attack is to set up what are called signature checks. For example, if I look for "' OR 1=1 -- " in the input data, I will certainly catch that SQL injection attempt. However, these are all equivalent expressions:


' OR 'Test'='Test' --
' OR 2=2 --
' OR 2>1 --
' OR 'Test' IN ('Test') --
etc.

You could try to look for a space between UNION and SELECT as above, but sending in the following could thwart this move:


' UNION /* */SELECT null, name, null
from dbo.sysobjects where xtype =
'U' --

In this case, the /* */ is another comment form. It will be ignored by the database, and you will again have a UNION SELECT construct being sent in. The bottom line regarding signature checks is that almost no matter what invalid input you try to check for (i.e., in this case, valid SQL statements), another variation of an SQL statement can be sent in and will be missed by your checks.

The key is to check for the good input and reject the rest. In our case, the best way to do this is to sanitize the input by using parameterized queries. The earlier code example can be rewritten this way:


string sql = "SELECT userid, first
name, lastname FROM users WHERE
username = @username and password =
@password";
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.Add("@username",
SqlDbType.NVarChar).Value = txtUser
name.Text;
cmd.Parameters.Add("@password",
SqlDbType.NVarChar).Value = txtPass
word.Text;

This method will perform type-checking for us, as well as convert the data input (in this case) into a literal string value rather than an SQL statement to be executed. In other words, with this technique, the exploit "' OR 1=1 --" becomes:


SELECT userid, firstname, lastname
FROM users WHERE username = ''' OR
1=1 -- ''' and password = ''

which will return no records because the SQL query will try to do a literal match on "' OR 1=1 --".

Another technique an attacker will use is inspecting error messages to determine what's available through your application. Never display raw error messages to the user when an SQL statement fails. Also, don't display messages that are too helpful to the attacker. For example, when a login fails, rather than saying, "User name 'JohnSmith' has been found, but the password is incorrect. Please try again," say something like, "Login failed. Please try again." This indicates there was a problem, but doesn't tell the attacker any extra information they don't already know about your data.

One other key security technique is to develop your applications following the Principle of Least Privilege. This means not running your database with a high-privileged account and not setting your applications to connect to the database with a high-privileged account. Use a low-privileged SQL user, or, even better; use Windows Security with a domain or local user account to connect to the database. By restricting the privileges of the account with which your application connects to the database as well as what account the database is running under, you can minimize the privileged access if an attacker is able to get through your application using one of the SQL injection techniques above. Using both methods of parameterized queries and secured database access, you are also practicing the security technique of Defense in Depth.

Tools
Though nothing replaces due diligence in applying the secure coding best practices covered above and securing your database, there are some tools you can use to help you pinpoint possible vulnerabilities that currently exist in your code. One nice tool, and it is free, is Microsoft FxCop (at the time of this writing, the latest release is 1.312 for the .NET Framework 1.1 and it can be found here: www.gotdotnet.com/team/fxcop/). This is a great auditing tool to run against your existing .NET code to determine if it conforms to a set of rules that more or less match Microsoft's coding best practices for the .NET Framework. Among the set of rules in the latest version are some Security Rules that watch for SQL injection vulnerabilities.

One of the FxCop Security Rules found the original concatenated strings code above and described the rule used this way:

SQL queries built up from user input are potentially vulnerable to injection attacks. SQL Server, as well as other database servers, supports parameterized SQL queries, a feature that will reduce the risk for injection attacks.

By changing the code to use parameterized queries, this FxCop rule was satisfied.

Conclusion
Every developer should understand how SQL injection works and the best ways to defend against it. Inspect your own code for some of the weaknesses mentioned in this article and be diligent about fixing your code if you find these problems. Don't get caught not knowing these fundamental secure programming techniques. You can find out more about other advanced SQL injection techniques at www.nextgenss.com/papers/advanced_sql_injection.pdf and security-papers.globint.com.ar/oracle_security/sql_injection_in_oracle.php, among other great resources from Application Security, Inc. (www.appsecinc.com/techdocs/whitepapers/research.html) and SPI Dynamics (www.spidynamics.com/support/whitepapers/index.html).

More Stories By Robert Hurlbut

Robert Hurlbut is an independent software architect/developer, consultant, and owner of Hurlbut Consulting, Inc. He is also a Microsoft MVP for Visual C#. Robert specializes in application security, distributed architectures, and database architecture and development. He speaks at industry conferences, including WIN-DEV,
New England Code Camp, Heartland Developers Conference, and DevTeach. He has a .NET blog at http://weblogs.asp.net/rhurlbut.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
SYS-CON Events announced today the How to Create Angular 2 Clients for the Cloud Workshop, being held June 7, 2016, in conjunction with 18th Cloud Expo | @ThingsExpo, at the Javits Center in New York, NY. Angular 2 is a complete re-write of the popular framework AngularJS. Programming in Angular 2 is greatly simplified. Now it’s a component-based well-performing framework. The immersive one-day workshop led by Yakov Fain, a Java Champion and a co-founder of the IT consultancy Farata Systems and...
What a difference a year makes. Organizations aren’t just talking about IoT possibilities, it is now baked into their core business strategy. With IoT, billions of devices generating data from different companies on different networks around the globe need to interact. From efficiency to better customer insights to completely new business models, IoT will turn traditional business models upside down. In the new customer-centric age, the key to success is delivering critical services and apps wit...
Join us at Cloud Expo | @ThingsExpo 2016 – June 7-9 at the Javits Center in New York City and November 1-3 at the Santa Clara Convention Center in Santa Clara, CA – and deliver your unique message in a way that is striking and unforgettable by taking advantage of SYS-CON's unmatched high-impact, result-driven event / media packages.
In his keynote at 18th Cloud Expo, Andrew Keys, Co-Founder of ConsenSys Enterprise, will provide an overview of the evolution of the Internet and the Database and the future of their combination – the Blockchain. Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life ...
SYS-CON Events announced today that BMC Software has been named "Siver Sponsor" of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. BMC is a global leader in innovative software solutions that help businesses transform into digital enterprises for the ultimate competitive advantage. BMC Digital Enterprise Management is a set of innovative IT solutions designed to make digital business fast, seamless, and optimized from mainframe to mo...
SYS-CON Events announced today that MobiDev will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. MobiDev is a software company that develops and delivers turn-key mobile apps, websites, web services, and complex software systems for startups and enterprises. Since 2009 it has grown from a small group of passionate engineers and business managers to a full-scale mobile software company with over 200 develope...
As cloud and storage projections continue to rise, the number of organizations moving to the cloud is escalating and it is clear cloud storage is here to stay. However, is it secure? Data is the lifeblood for government entities, countries, cloud service providers and enterprises alike and losing or exposing that data can have disastrous results. There are new concepts for data storage on the horizon that will deliver secure solutions for storing and moving sensitive data around the world. ...
SoftLayer operates a global cloud infrastructure platform built for Internet scale. With a global footprint of data centers and network points of presence, SoftLayer provides infrastructure as a service to leading-edge customers ranging from Web startups to global enterprises. SoftLayer's modular architecture, full-featured API, and sophisticated automation provide unparalleled performance and control. Its flexible unified platform seamlessly spans physical and virtual devices linked via a world...
SYS-CON Events announced today that ContentMX, the marketing technology and services company with a singular mission to increase engagement and drive more conversations for enterprise, channel and SMB technology marketers, has been named “Sponsor & Exhibitor Lounge Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York. “CloudExpo is a great opportunity to start a conversation with new prospects, but what happens after the...
Companies can harness IoT and predictive analytics to sustain business continuity; predict and manage site performance during emergencies; minimize expensive reactive maintenance; and forecast equipment and maintenance budgets and expenditures. Providing cost-effective, uninterrupted service is challenging, particularly for organizations with geographically dispersed operations.
The IoTs will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, will demonstrate how to move beyond today's coding paradigm and share the must-have mindsets for removing complexity from the development proc...
SYS-CON Events announced today TechTarget has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. TechTarget is the Web’s leading destination for serious technology buyers researching and making enterprise technology decisions. Its extensive global networ...
SYS-CON Events announced today that Commvault, a global leader in enterprise data protection and information management, has been named “Bronze Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Commvault is a leading provider of data protection and information management...
The essence of data analysis involves setting up data pipelines that consist of several operations that are chained together – starting from data collection, data quality checks, data integration, data analysis and data visualization (including the setting up of interaction paths in that visualization). In our opinion, the challenges stem from the technology diversity at each stage of the data pipeline as well as the lack of process around the analysis.
A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, wh...
Designing IoT applications is complex, but deploying them in a scalable fashion is even more complex. A scalable, API first IaaS cloud is a good start, but in order to understand the various components specific to deploying IoT applications, one needs to understand the architecture of these applications and figure out how to scale these components independently. In his session at @ThingsExpo, Nara Rajagopalan is CEO of Accelerite, will discuss the fundamental architecture of IoT applications, ...
SYS-CON Events announced today that Tintri Inc., a leading producer of VM-aware storage (VAS) for virtualization and cloud environments, will exhibit at the 18th International CloudExpo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
SYS-CON Events announced today that MangoApps will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. MangoApps provides modern company intranets and team collaboration software, allowing workers to stay connected and productive from anywhere in the world and from any device. For more information, please visit https://www.mangoapps.com/.
SYS-CON Events announced today that Alert Logic, Inc., the leading provider of Security-as-a-Service solutions for the cloud, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Alert Logic, Inc., provides Security-as-a-Service for on-premises, cloud, and hybrid infrastructures, delivering deep security insight and continuous protection for customers at a lower cost than traditional security solutions. Ful...
In his session at 18th Cloud Expo, Bruce Swann, Senior Product Marketing Manager at Adobe, will discuss how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects). Bruce Swann has more than 15 years of experience working with digital marketing disciplines like web analytics, social med...