Welcome!

Microsoft Cloud Authors: Pat Romanski, Jnan Dash, Andreas Grabner, Lori MacVittie, Jim Kaskade

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
Things are changing so quickly in IoT that it would take a wizard to predict which ecosystem will gain the most traction. In order for IoT to reach its potential, smart devices must be able to work together. Today, there are a slew of interoperability standards being promoted by big names to make this happen: HomeKit, Brillo and Alljoyn. In his session at @ThingsExpo, Adam Justice, vice president and general manager of Grid Connect, will review what happens when smart devices don’t work togethe...
"There's a growing demand from users for things to be faster. When you think about all the transactions or interactions users will have with your product and everything that is between those transactions and interactions - what drives us at Catchpoint Systems is the idea to measure that and to analyze it," explained Leo Vasiliou, Director of Web Performance Engineering at Catchpoint Systems, in this SYS-CON.tv interview at 18th Cloud Expo, held June 7-9, 2016, at the Javits Center in New York Ci...
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 ...
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.
Internet of @ThingsExpo, taking place June 6-8, 2017 at the Javits Center in New York City, New York, is co-located with the 20th International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. @ThingsExpo New York Call for Papers is now open.
In the next five to ten years, millions, if not billions of things will become smarter. This smartness goes beyond connected things in our homes like the fridge, thermostat and fancy lighting, and into heavily regulated industries including aerospace, pharmaceutical/medical devices and energy. “Smartness” will embed itself within individual products that are part of our daily lives. We will engage with smart products - learning from them, informing them, and communicating with them. Smart produc...
SYS-CON Events announced today that Catchpoint, a leading digital experience intelligence company, has been named “Silver Sponsor” of 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. Catchpoint Systems is a leading Digital Performance Analytics company that provides unparalleled insight into your customer-critical services to help you consistently deliver an amazing customer experience. Designed for digital business, C...
More and more brands have jumped on the IoT bandwagon. We have an excess of wearables – activity trackers, smartwatches, smart glasses and sneakers, and more that track seemingly endless datapoints. However, most consumers have no idea what “IoT” means. Creating more wearables that track data shouldn't be the aim of brands; delivering meaningful, tangible relevance to their users should be. We're in a period in which the IoT pendulum is still swinging. Initially, it swung toward "smart for smart...
WebRTC sits at the intersection between VoIP and the Web. As such, it poses some interesting challenges for those developing services on top of it, but also for those who need to test and monitor these services. In his session at WebRTC Summit, Tsahi Levent-Levi, co-founder of testRTC, reviewed the various challenges posed by WebRTC when it comes to testing and monitoring and on ways to overcome them.
The emerging Internet of Everything creates tremendous new opportunities for customer engagement and business model innovation. However, enterprises must overcome a number of critical challenges to bring these new solutions to market. In his session at @ThingsExpo, Michael Martin, CTO/CIO at nfrastructure, outlined these key challenges and recommended approaches for overcoming them to achieve speed and agility in the design, development and implementation of Internet of Everything solutions with...
Every successful software product evolves from an idea to an enterprise system. Notably, the same way is passed by the product owner's company. In his session at 20th Cloud Expo, Oleg Lola, CEO of MobiDev, will provide a generalized overview of the evolution of a software product, the product owner, the needs that arise at various stages of this process, and the value brought by a software development partner to the product owner as a response to these needs.
In 2014, Amazon announced a new form of compute called Lambda. We didn't know it at the time, but this represented a fundamental shift in what we expect from cloud computing. Now, all of the major cloud computing vendors want to take part in this disruptive technology. In his session at 20th Cloud Expo, John Jelinek IV, a web developer at Linux Academy, will discuss why major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform are all trying to sidestep VMs and containers...
SYS-CON Events announced today that MobiDev, a client-oriented software development company, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MobiDev is a software company that develops and delivers turn-key mobile apps, websites, web services, and complex softw...
Smart Cities are here to stay, but for their promise to be delivered, the data they produce must not be put in new siloes. In his session at @ThingsExpo, Mathias Herberts, Co-founder and CTO of Cityzen Data, discussed the best practices that will ensure a successful smart city journey.
Technology vendors and analysts are eager to paint a rosy picture of how wonderful IoT is and why your deployment will be great with the use of their products and services. While it is easy to showcase successful IoT solutions, identifying IoT systems that missed the mark or failed can often provide more in the way of key lessons learned. In his session at @ThingsExpo, Peter Vanderminden, Principal Industry Analyst for IoT & Digital Supply Chain to Flatiron Strategies, will focus on how IoT depl...
Big Data, cloud, analytics, contextual information, wearable tech, sensors, mobility, and WebRTC: together, these advances have created a perfect storm of technologies that are disrupting and transforming classic communications models and ecosystems. In his session at @ThingsExpo, Erik Perotti, Senior Manager of New Ventures on Plantronics’ Innovation team, provided an overview of this technological shift, including associated business and consumer communications impacts, and opportunities it m...
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, discussed how leveraging the Industrial Internet and...
There will be new vendors providing applications, middleware, and connected devices to support the thriving IoT ecosystem. This essentially means that electronic device manufacturers will also be in the software business. Many will be new to building embedded software or robust software. This creates an increased importance on software quality, particularly within the Industrial Internet of Things where business-critical applications are becoming dependent on products controlled by software. Qua...
Fact is, enterprises have significant legacy voice infrastructure that’s costly to replace with pure IP solutions. How can we bring this analog infrastructure into our shiny new cloud applications? There are proven methods to bind both legacy voice applications and traditional PSTN audio into cloud-based applications and services at a carrier scale. Some of the most successful implementations leverage WebRTC, WebSockets, SIP and other open source technologies. In his session at @ThingsExpo, Da...
"Tintri was started in 2008 with the express purpose of building a storage appliance that is ideal for virtualized environments. We support a lot of different hypervisor platforms from VMware to OpenStack to Hyper-V," explained Dan Florea, Director of Product Management at Tintri, in this SYS-CON.tv interview at 18th Cloud Expo, held June 7-9, 2016, at the Javits Center in New York City, NY.