Welcome!

Microsoft Cloud Authors: Elizabeth White, Mihai Corbuleac, Pat Romanski, David Bermingham, Steven Mandel

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
The IoT has the potential to create a renaissance of manufacturing in the US and elsewhere. In his session at 18th Cloud Expo, Florent Solt, CTO and chief architect of Netvibes, will discuss how the expected exponential increase in the amount of data that will be processed, transported, stored, and accessed means there will be a huge demand for smart technologies to deliver it. Florent Solt is the CTO and chief architect of Netvibes. Prior to joining Netvibes in 2007, he co-founded Rift Technol...
Join IBM June 8 at 18th Cloud Expo at the Javits Center in New York City, NY, and learn how to innovate like a startup and scale for the enterprise. You need to deliver quality applications faster and cheaper, attract and retain customers with an engaging experience across devices, and seamlessly integrate your enterprise systems. And you can't take 12 months to do it.
This is not a small hotel event. It is also not a big vendor party where politicians and entertainers are more important than real content. This is Cloud Expo, the world's longest-running conference and exhibition focused on Cloud Computing and all that it entails. If you want serious presentations and valuable insight about Cloud Computing for three straight days, then register now for Cloud Expo.
IoT device adoption is growing at staggering rates, and with it comes opportunity for developers to meet consumer demand for an ever more connected world. Wireless communication is the key part of the encompassing components of any IoT device. Wireless connectivity enhances the device utility at the expense of ease of use and deployment challenges. Since connectivity is fundamental for IoT device development, engineers must understand how to overcome the hurdles inherent in incorporating multipl...
Machine Learning helps make complex systems more efficient. By applying advanced Machine Learning techniques such as Cognitive Fingerprinting, wind project operators can utilize these tools to learn from collected data, detect regular patterns, and optimize their own operations. In his session at 18th Cloud Expo, Stuart Gillen, Director of Business Development at SparkCognition, will discuss how research has demonstrated the value of Machine Learning in delivering next generation analytics to im...
Manufacturers are embracing the Industrial Internet the same way consumers are leveraging Fitbits – to improve overall health and wellness. Both can provide consistent measurement, visibility, and suggest performance improvements customized to help reach goals. Fitbit users can view real-time data and make adjustments to increase their activity. In his session at @ThingsExpo, Mark Bernardo Professional Services Leader, Americas, at GE Digital, will discuss how leveraging the Industrial Interne...
The paradigm has shifted. A Gartner survey shows that 43% of organizations are using or plan to implement the Internet of Things in 2016. However, not just a handful of companies are still using the old-style ad-hoc trial-and-error ways, unaware of the critical barriers, paint points, traps, and hidden roadblocks. How can you become a winner? In his session at @ThingsExpo, Tony Shan will present a methodical approach to guide the holistic adoption and enablement of IoT implementations. This ov...
SYS-CON Events announced today that Stratoscale, the software company developing the next generation data center operating system, 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. Stratoscale is revolutionizing the data center with a zero-to-cloud-in-minutes solution. With Stratoscale’s hardware-agnostic, Software Defined Data Center (SDDC) solution to store everything, run anything and scale everywhere...
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. This immersive one-day workshop at 18th Cloud Expo, led by Yakov Fain, a Java Champion and a co-founder of the IT consultancy Farata Systems and the product company SuranceBay, will provide you with everything you wanted to know about Angular 2.
Digital payments using wearable devices such as smart watches, fitness trackers, and payment wristbands are an increasing area of focus for industry participants, and consumer acceptance from early trials and deployments has encouraged some of the biggest names in technology and banking to continue their push to drive growth in this nascent market. Wearable payment systems may utilize near field communication (NFC), radio frequency identification (RFID), or quick response (QR) codes and barcodes...
SYS-CON Events announced today that Men & Mice, the leading global provider of DNS, DHCP and IP address management overlay solutions, 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. The Men & Mice Suite overlay solution is already known for its powerful application in heterogeneous operating environments, enabling enterprises to scale without fuss. Building on a solid range of diverse platform support,...
You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this webcast to learn various techniques for designing applications that will scale successfully in Bluemix, for the confidence you need to take your apps to the next level and beyond.
The increasing popularity of the Internet of Things necessitates that our physical and cognitive relationship with wearable technology will change rapidly in the near future. This advent means logging has become a thing of the past. Before, it was on us to track our own data, but now that data is automatically available. What does this mean for mHealth and the "connected" body? In her session at @ThingsExpo, Lisa Calkins, CEO and co-founder of Amadeus Consulting, will discuss the impact of wea...
Whether your IoT service is connecting cars, homes, appliances, wearable, cameras or other devices, one question hangs in the balance – how do you actually make money from this service? The ability to turn your IoT service into profit requires the ability to create a monetization strategy that is flexible, scalable and working for you in real-time. It must be a transparent, smoothly implemented strategy that all stakeholders – from customers to the board – will be able to understand and comprehe...
SYS-CON Events announced today that Ericsson has been named “Gold Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Ericsson is a world leader in the rapidly changing environment of communications technology – providing equipment, software and services to enable transformation through mobility. Some 40 percent of global mobile traffic runs through networks we have supplied. More than 1 billion subscribers around the world re...
So, you bought into the current machine learning craze and went on to collect millions/billions of records from this promising new data source. Now, what do you do with them? Too often, the abundance of data quickly turns into an abundance of problems. How do you extract that "magic essence" from your data without falling into the common pitfalls? In her session at @ThingsExpo, Natalia Ponomareva, Software Engineer at Google, will provide tips on how to be successful in large scale machine lear...
SYS-CON Events announced today that Fusion, a leading provider of cloud services, 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. Fusion, a leading provider of integrated cloud solutions to small, medium and large businesses, is the industry's single source for the cloud. Fusion's advanced, proprietary cloud service platform enables the integration of leading edge solutions in the cloud, including cloud...
There is an ever-growing explosion of new devices that are connected to the Internet using “cloud” solutions. This rapid growth is creating a massive new demand for efficient access to data. And it’s not just about connecting to that data anymore. This new demand is bringing new issues and challenges and it is important for companies to scale for the coming growth. And with that scaling comes the need for greater security, gathering and data analysis, storage, connectivity and, of course, the...
The IETF draft standard for M2M certificates is a security solution specifically designed for the demanding needs of IoT/M2M applications. In his session at @ThingsExpo, Brian Romansky, VP of Strategic Technology at TrustPoint Innovation, will explain how M2M certificates can efficiently enable confidentiality, integrity, and authenticity on highly constrained devices.
In his session at @ThingsExpo, Chris Klein, CEO and Co-founder of Rachio, will discuss next generation communities that are using IoT to create more sustainable, intelligent communities. One example is Sterling Ranch, a 10,000 home development that – with the help of Siemens – will integrate IoT technology into the community to provide residents with energy and water savings as well as intelligent security. Everything from stop lights to sprinkler systems to building infrastructures will run ef...