Microsoft Cloud Authors: Stackify Blog, Liz McMillan, David H Deans, Automic Blog, Pat Romanski

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') --

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 =
SqlCommand cmd = new SqlCommand(sql);
SqlDbType.NVarChar).Value = txtUser
SqlDbType.NVarChar).Value = txtPass

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.

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.

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
Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the USA and Europe, we work with a variety of customers from emerging startups to Fortune 1000 companies.
SYS-CON Events announced today that Grape Up will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company specializing in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the U.S. and Europe, Grape Up works with a variety of customers from emergi...
Financial Technology has become a topic of intense interest throughout the cloud developer and enterprise IT communities. Accordingly, attendees at the upcoming 20th Cloud Expo at the Javits Center in New York, June 6-8, 2017, will find fresh new content in a new track called FinTech.
@ThingsExpo has been named the Most Influential ‘Smart Cities - IIoT' Account and @BigDataExpo has been named fourteenth by Right Relevance (RR), which provides curated information and intelligence on approximately 50,000 topics. In addition, Right Relevance provides an Insights offering that combines the above Topics and Influencers information with real time conversations to provide actionable intelligence with visualizations to enable decision making. The Insights service is applicable to eve...
SYS-CON Events announced today that Interoute, owner-operator of one of Europe's largest networks and a global cloud services platform, has been named “Bronze Sponsor” of SYS-CON's 20th Cloud Expo, which will take place on June 6-8, 2017 at the Javits Center in New York, New York. Interoute is the owner-operator of one of Europe's largest networks and a global cloud services platform which encompasses 12 data centers, 14 virtual data centers and 31 colocation centers, with connections to 195 add...
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm.
20th Cloud Expo, taking place June 6-8, 2017, at the Javits Center in New York City, NY, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy.
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place June 6-8, 2017, at the Javits Center in New York City, New York, is co-located with 20th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry p...
Today we can collect lots and lots of performance data. We build beautiful dashboards and even have fancy query languages to access and transform the data. Still performance data is a secret language only a couple of people understand. The more business becomes digital the more stakeholders are interested in this data including how it relates to business. Some of these people have never used a monitoring tool before. They have a question on their mind like “How is my application doing” but no id...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
@GonzalezCarmen has been ranked the Number One Influencer and @ThingsExpo has been named the Number One Brand in the “M2M 2016: Top 100 Influencers and Brands” by Analytic. Onalytica analyzed tweets over the last 6 months mentioning the keywords M2M OR “Machine to Machine.” They then identified the top 100 most influential brands and individuals leading the discussion on Twitter.
Five years ago development was seen as a dead-end career, now it’s anything but – with an explosion in mobile and IoT initiatives increasing the demand for skilled engineers. But apart from having a ready supply of great coders, what constitutes true ‘DevOps Royalty’? It’ll be the ability to craft resilient architectures, supportability, security everywhere across the software lifecycle. In his keynote at @DevOpsSummit at 20th Cloud Expo, Jeffrey Scheaffer, GM and SVP, Continuous Delivery Busine...
Most technology leaders, contemporary and from the hardware era, are reshaping their businesses to do software in the hope of capturing value in IoT. Although IoT is relatively new in the market, it has already gone through many promotional terms such as IoE, IoX, SDX, Edge/Fog, Mist Compute, etc. Ultimately, irrespective of the name, it is about deriving value from independent software assets participating in an ecosystem as one comprehensive solution.
SYS-CON Events announced today that T-Mobile will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. As America's Un-carrier, T-Mobile US, Inc., is redefining the way consumers and businesses buy wireless services through leading product and service innovation. The Company's advanced nationwide 4G LTE network delivers outstanding wireless experiences to 67.4 million customers who are unwilling to compromise on ...
The 20th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held June 6-8, 2017, at the Javits Center in New York City, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Containers, Microservices and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit your speaking proposal ...
SYS-CON Events announced today that CollabNet, a global leader in enterprise software development, release automation and DevOps solutions, will be a Bronze Sponsor of SYS-CON's 20th International Cloud Expo®, taking place from June 6-8, 2017, at the Javits Center in New York City, NY. CollabNet offers a broad range of solutions with the mission of helping modern organizations deliver quality software at speed. The company’s latest innovation, the DevOps Lifecycle Manager (DLM), supports Value S...
Cybersecurity is a critical component of software development in many industries including medical devices. However, code is not always written to be robust or secure from the unknown or the unexpected. This gap can make medical devices susceptible to cybersecurity attacks ranging from compromised personal health information to life-sustaining treatment. In his session at @ThingsExpo, Clark Fortney, Software Engineer at Battelle, will discuss how programming oversight using key methods can incre...
SYS-CON Events announced today that Hitachi, the leading provider the Internet of Things and Digital Transformation, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Hitachi Data Systems, a wholly owned subsidiary of Hitachi, Ltd., offers an integrated portfolio of services and solutions that enable digital transformation through enhanced data management, governance, mobility and analytics. We help globa...
SYS-CON Events announced today that Juniper Networks (NYSE: JNPR), an industry leader in automated, scalable and secure networks, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Juniper Networks challenges the status quo with products, solutions and services that transform the economics of networking. The company co-innovates with customers and partners to deliver automated, scalable and secure network...
NHK, Japan Broadcasting, will feature the upcoming @ThingsExpo Silicon Valley in a special 'Internet of Things' and smart technology documentary that will be filmed on the expo floor between November 3 to 5, 2015, in Santa Clara. NHK is the sole public TV network in Japan equivalent to the BBC in the UK and the largest in Asia with many award-winning science and technology programs. Japanese TV is producing a documentary about IoT and Smart technology and will be covering @ThingsExpo Silicon Val...