ראשון 22 דצמבר 2024
דעות - מאמרים
1 Introduction
This paper is about to shed some light onhow hackers can easily exploit one of the most common application security vulnerabilities and gain access to your business most critical information, meaning your Database. This evasion
technique to retrieve information from the database is called SQL Injection.
The paper is aimed both for the layman as well as for the professional. It is obvious that in order to have a deeper understanding, the more technical you are, the more you will understand and be able to act. The paper though treats the reader as if nothing is taken for granted.
1.1 Application Security
In the past few years, web applications have become a very common way for companies to interact with their clients. Consequently, web applications got more complex and on the other hand most accessible and useful. The internet market then came with its own businesses such as e-commerce sites. Huge companies today are based on internet clients/surfers who purchase and sell their commodities via the web. Moreover, today companies has special budget for advertising in the web.
The germination of the internet and popularity of e-commerce among surfers and businesses bring web applications to new climax each and every year:
“Online retailers saw a 24 percent increase in sales in 2004; growth through 2005 is projected to achieve another 22 percent” (Forrester)
The growth of web applications and internet use has also brought with it a side effect - the hackers. Hackers became more aggressive and more sophisticated as the years goes by. They developed new methods and new tools to reach and retrieve internal information, gain unauthorized access and harm web applications. There are hundreds of known vulnerabilities that were discovered after post factum - harm has already been done. Day and night we hear of a new story about a hacker who harmed corporations, companies and even small businesses.
In order to prevent malicious users from doing that, old technologies were replaced by new ones, many web sites have changed their whole web applications architecture and platforms, and furthermore new technologies emerged. But still the hackers are there, and one should be prepared for the next attack.
1.2 SQL Injection what is?
Web application’s architecture is almost always based on databases. The database is where all the application’s information stored in tables. Each table contains information about many instances of single object type i.e. users table will contain information about all the company’s users.
It is well known that credit card numbers, expiration dates and other data also stored in special tables.
The common way of implementing web applications is to use the three tier architecture of web application as in Figure-1.
In this shape the DB is the most protected unit, and usually defended by at least a single firewall. The firewall is configured to block any requests addressed directly to the database, unless the source of the request came from the web server. The web server usually located in the DMZ (demilitarized zone) and the client is in the cloud zone as in Figure-2.
In this case, if a user who is located anywhere at the globe (the cloud) will try to access directly to the web application’s database, will either:
Be unable to access the database since the database has an internal address that can be reached only from the internal local area network. Meaning no one from outside the company can access the database since it is not connected to the internet. (this is the most common way)
Will be blocked by the firewall since the access to the database came from an unauthorized zone and not from the web server. (very rare)
Be able to access it, and will be asked for login information. This is a very bad and unsafe situation. (Only in labs).
According to that the case of unauthorized access to our database seems to be closed and covered from all aspects. No one can access my database without the right permission and the permission to access is given only to the web server. Therefore, unless someone will take control over my web server I am safe.
Well, unfortunately this is partially the case. Since unauthorized access is one issue, but using the authorization of the web server and using it for executing illegal commands is another issue.
Let’s think of our database as an origin for all the answers one can have about the company, its products, customers and other information, the web application will be our mediator for the user to ask questions. These questions though are preconfigured. Each time a user gives an input (either by clicking on the mouse or by supplying information via keyboard) a query is being sent from the application to the database. This query is a question that the application is asking the database to supply information for the user. In this simulation, the general idea of SQL Injection is to use wisecracker input which will manipulate the application to ask unauthorized and nor preconfigured question. This wisecracker input that caused the application to perform malicious functions is called “parameter manipulation”.
For example if I click on an image of product named WebDefend the application will present me with the information about this WebDefend product. Clicking on WebDefend image as user input actually caused to the next application command (HTTP Request) www.store.com?prodcut_code=7 which generated the next query to be sent from the web server to the database server:
select * from products where product_id=7 (in our words bring all [=*] information from the zone where products information is stored [products] about a product which its code is 7)
This is a query of the language used to bring answers from the DB. The language used named SQL - Structured Query Language.
In order to damage that database all I need to do is use the query to run malicious code. I can do that by injecting SQL queries instead or in addition to the regular input. In our case I can send the next application command (HTTP Request)
www.store.com?prodcut_code=7;droptable users
This will generate the next SQL Statements:
select * from products where product_id=7; drop table users
This query will generate two different commands since the semicolon is a command separator in SQL language. Therefore the first query which will be executed is the same as the previous one; however the second SQL command “drop table users” will cause to the deletion of all users table, meaning lose all the company’s users details.
In this simple example, we tried to demonstrate how a preconfigured question of the application such as – bring me information about a product that its code is: X (where x is the user input) became into a malicious command such as delete all application’s user’s details. Using the same method a good hacker can steal/manipulate and change data and cause a serious harm and damage to the company and its clients.
2 SQL Injection In details
In this chapter we’re about to explain how SQL Injection works in the real world. We’ll explain what the main reasons are, how hackers exploit it, how rare the phenomenon is and what new trends emerged from it.
2.1 Good to know for better understanding
In order to execute SQL Injection there are few things needed:
Good Knowledge of SQL, a minimal familiarity with web applications in general and with the attacked application is specific. The examples that will be given will also require a minimal knowledge of HTML and ASP. Therefore, it is recommended for people who are not familiar with these topics to read the next:
http://www.w3schools.com/html/html_intro.asp
http://www.w3schools.com/html/asp_intro.asp
http://www.w3schools.com/sql/sql_intro.asp
http://en.wikipedia.org/wiki/Web_application
2.2 SQL Injection how to?
As understood SQL Injection is a common technique usually aimed to harm or to steal information and it can be easily exploited by hackers. The idea behind it is as simple as its name, and literally means Inject SQL commands to the protocol used to communicate between the web server and the database server. The injection is done by the hacker wherever a user input is expected and therefore can be found in each type of an application such as:
E-commerce – in login pages and user details pages, in product details pages, in payment pages.
Search engines – in the search bar
Web mail – registration pages, login pages, user’s details pages.
2.2.1 Authentication bypass (Michael Jackson & SQL Injection)
Login page is usually the main screen clients face with. It is also one of the most attacked forms used by hackers, since if you passed this screen without revealing your own identity, you actually gained access to information you weren’t supposed to. SQL Injection is one of the most common techniques to achieve such an unauthorized access.
Authentication regular process
Understanding the flow of the application will assist in comprehending the whole idea behind.
A. The login screen – First the user is introduced with the login page.
B. Identification inputs – The user is requested to give his identification details.
C. Identification process – The process performed in the server to verify if the user is familiar with the organization and authorized to perform any kind of transaction.
D. Identification output – success login output usually displays a welcoming message and a menu with optional transactions. In case of failure login an appropriate message will be displayed.
The login screen
A regular common login page will comprise two main parameters: username and password, and a login button.
|
Username: |
|
|
Password: |
|
Figure-3
The html text will be similar to the following:
<html>
<body>
<table>
<tr>
<td>Username:</td><td><input type="text" name="username" size="20"></td>
</tr>
<tr>
<td>Password:</td><td><input type="password" name="password" size="22"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="submit" value=Login/></td>
</tr>
</table>
</body>
</html>
Identification inputs
When the user fills the form with the relevant details and presses the submit button the parameters of username and password are sent from the client browser to the web server. In our example the username will be: Michael and password: imbad
Identification process
According to the input given the server should decide whether the given user is a registered one, meaning is allowed to continue using the application, or the user who has just tried to login is not allowed to use the application.
In order to do that, the server must approach the database and question it about the user details. The SQL query is actually the protocol and scripting language to perform this kind of question.
Usually the SQL query that will be used in login screens will resemble the next one:
SELECT FamilyName FROM Users WHERE Username = '" & request.QueryString("username") & "' AND Password = '" & request.QueryString("password") & "'"
Inserting the user input into the query will generate the next query:
SELECT FamilyName FROM Users WHERE Username = 'Michael' AND Password = 'imbad'
The query above requests from the database the family name. The family name is needed in order to welcome the user in the successful login as seen in Figure-4. The query though needs the family name of a specific user - the one who tries to login. To ensure the right output, the query looks for a user who’s both username equals to the username submitted by the user and his password equals to the password submitted by the user. Meaning looking for a single record where the username is “Michael” and at the same record the password is “imbad”. The assumption behind this SQL query is that if there is no matching in a single record no family name output will be retrieved from the database, since the identification inputs were wrong.
Identification output
Consequently the person who is trying to login is an unauthorized user and therefore will be directed to the failure login screen. If such a record exists in the database, the right family name will be retrieved and successful screen will be sent as response to the user as seen in figure-4. Unless, few records, with the same username and password exist. Such a case can be avoided by database characteristic, such as primary key.
Figure-4
SQL Injection backstage
A deeper inspection of the query reveals that the username and password are taken directly from the user input and pasted into the query without performing any assurance on the user input.
If for example instead of giving a legitimate input such as regular username and password, a user will give any username i.e. Michael and a’ or ‘1’=’1 will begiven for the password. To understand the consequences of this input will walk through the same steps we went in previous scenario.
The login screen is the same.
The identification inputs the only change are the texts Michael for the username and a’ or ‘1’=’1 forthe password.
Identification process
Here there are much bigger changes; to expose the changes once again we need to insert the inputs into the query:
SELECT FamilyName FROM Users WHERE Username = 'Michael' AND Password = 'a’ or ‘1’=’1'
The inserted input caused to another SQL expression to be added to the end of the query – or ‘1’=’1’. The meaning of this expression is always true since 1 always equals to 1. Hence, the expression is actually turned to something like that:
SELECT FamilyName FROM Users WHERE Username = 'Michael' AND Password = 'a’ or true;
The “or true” expression at the end of the query turns the where clause to be irrelevant since any logical expression ends with an “or true” logical expression returns always true. Therefore our query could be cosidered as
SELECT FamilyName FROM Users
The outcome of such a query will be a list of all family names in the company’s database. Thereupon,the login probably success with the family name and credentials of the first family name in the database. In many cases the first username of the application is the application administrator. The result may be a disastrous since a malicious user could successful login to the application and moreover with an administrator rights.
Remark character & SQL Injection
Another common and very easy SQL Injection attack is to use the SQL remark character. The remark character is changed among database types but usually its either a ‘--‘ (two minus signs in a row) or ‘//’ (two slashes character in a row).
Once again we’ll use our login example. This time instead of the username “Michael” we’ll supply the username Michael’-- (two minuses in a row - the remark character) the password is no longer matter. Since the query will now look:
SELECT FamilyName FROM Users WHERE Username = 'Michael' -- AND Password =''
The “--“sign is a remark and therefore there is no SQL command meaning to what comes after that sign. Hence the actual query is
SELECT FamilyName FROM Users WHERE Username = 'Michael'
If there is a username Michael in the database, once again, I succeeded login to his account withou knowing his password. Guessing an existing username in a database is an easy task, due to the fact that in most cases the username is either the first name or a combination of first name and last name. Therefore, in many cases, all I need in order to get administrator rights is the site administrator’s name.
Union Select
Last example for SQL Injection is today’s most common use of SQL Injection. In order to demonstrate it, we need to describe a different scenario:
There is an e-Commerce web site of pet shop. This pet shop includes lots of products which you could order and pay via the web. After purchasing the merchandise will be delivered directly to your house. During the registration you gave your login details such as username and password and furthermore in previous acquisitions you gave your credit card details. In the next example we’ll explain how a malicious user could collect your information stored in the pet shop database.
It is easy to guess application’s database structure. Database’s structure of e-commerce site usually includes users or customers tables and products or prods table. However, a good hacker doesn’t have to guess the database structure. If your database is vulnerable to SQL Injection, you can use it to find out the database structure. This could be achieved, because each database has its system tables which hold the information of the database structure. In this example we assume that this information is already known to the hacker.
Before going into the examples, once again we’ll walk through the naïve user steps. A user is entering the site and logs in. After that he is searching for a product by category, selects the category and a list of all products appears.
Figure-5
The HTTP request sent is HTTP://www.MyPetShop.com/productsbycategory.asp?CatID=7. The SQL Query sent to the database is:
SELECT ProductID,ProductName,ProductPrice FROM Products,Categories WHERE Products.CategoryID=Categories.CategoryID AND Products.CategoryID =7
The category ID equals 7 because the user have chosen dog food category in the menu (as seen in the HTTP Request and in Figure-5). A hacker could use this combination of HTTP request and SQL to retrieve illegal data. Look at this HTTP request :
HTTP://www.MyPetShop.com/productsbycategory.asp?CatID=7 UNION SELECT UserID,UserName,CountryID,StateID from Users.
This is a snapshot of the main info presented in the response:
Figure-6
This is the concatenated SQL query with the user input:
SELECT ProductID,ProductName,ProductPrice FROM Products, Categories WHERE Products.CategoryID=Categories.CategoryID AND Products.CategoryID = 7 UNION SELECT UserID,UserName,CountryID,StateID from Users
In SQL the Union command causes to join two tables. The only restriction a hacker should pay attention to, is that both select queries will retrieve same information type. Meaning if the SQL query’s (before the union command) first filed is a number and second field is an alphanumeric, the same order of field types should be consistent after the union command.
Therefore in the given examples we got in the result screen (marked in red) instead of product name the username and instead of price the country code. Following the same logic we need to repeat the same HTTP request and instead of requesting the username in the union select ask for the password in the query and we’ll get a list of all passwords.
By doing that we stole all pairs of usernames and passwords of all the pet shop users. We can continue retrieving information and still credit card numbers, social security numbers and any other information stored in the database.
2.3 Evolution
The examples given are the tip of the iceberg of SQL Injection. The SQL Injection vulnerability in login still exists but there are many solution developed to avoid that. Microsoft for instance has published in .NET framework an out of the box login module in order to overcome such problems.
Nevertheless, there are a lot of places in an application where SQL Injection could be used. As aforementioned it could be in registration forms, in search forms or any form you could think of, where user input is needed. The main idea behind SQL Injection though is the response. The response is sent with the information we need, or via the response we could see if we logged in successfully.
SQL Injection though is an old and famous attack used by hackers. Hence, application developers have tried to overcome it, mostly by limiting the responses.
Yet, hackers found new ways to use SQL to manipulate the application in order to retrieve information from the web application database. The technique they are using is called Blind SQL Injection, but this is for another article to be written.
2.4 Main Reasons
As all examples given show the basis of all of them is the same. It is simple input that taken from the user and inserted directly into the SQL Query of the application.
The validation of the user input was taken out of the equation in all cases mentioned, and this is what SQL Injection is all about:
Input validation, input validation and input validation, have I mentioned input validation?
If for instance, we could eliminate a case where two minuses in a row “--“ will be sent to the database the SQL Injection technique with the remark sign wouldn’t be ever accomplished. If we could avoid inserting the single quote sign (‘) into SQL queries the a’ or ‘1’=’1 attack was avoided and so on and so force.
The conclusion therefore must be one it’s all about missing of input validation.
2.5 How common is it?
SQL Injection is a common vulnerability; it does not depend on the database but only upon the application developers. Therefore, it is meaningless if you are using MSSSQL, Oracle, MySQL DB2, Sybase or any other database type. If your developer has done one single mistake in programming your application, it is already vulnerable.
According to SANS (http://www.sans.org/newsletters/advisor/1.2.pdf) cross site scripting and SQL Injection is “two of the most common types of vulnerabilities that a Web site or application may be susceptible to.”
OWASP (open web application security project) - a volunteering organization of Web application security specialists included SQL Injection in one of its Top Ten common vulnerabilities (http://www.owasp.org/documentation/topten.html)
Research by b-sec published in 2003 reveals that 72% of corporate web applications are vulnerable to a straightforward attack known as SQL injection commit identity theft. (http://www.deacons.com.au/news/article.asp?nID=475)
3 Q&A
3.1 I have my web site, is it vulnerable to SQL Injection?
Discovering SQL Injection vulnerability is not an easy task for a layman, but it is one of the most common and intuitive tasks for application security specialists. Nevertheless, if one form is not vulnerable to SQL Injection it still doesn’t mean that your whole application is not vulnerable. For maximum safety and coverage one must scan his web site each and every form and each and every field in the forms. This is a Sisyphean labor once you did one or two forms. Therefore, our first recommendation is to hire a third party company and pay them to penetrate your site with a full and detailed report.
A rapid way to answer such a question is simply buy or even download freeware scanners available at the internet and try to scan your own site. Almost each basic scanner can tell if an application is vulnerable in a conspicuous manner.
Please pay attention such scanners might sometimes harm your website. Therefore it is recommended to be advised on how to do that, what scanner to use and most important, to follow the scanner information pages and instructions.
For a professional, identifying SQL Injection attacks may vary from just trying to insert an single quote sign (‘) in an alphanumeric parameter thorough a bracket sign in a numeric parameter and up to using blind SQL Injection techniques such as inserting a mathematical expression in a numeric field (18+1 for instance in age field). These requests may result in many different HTTP responses and error codes such as ODBC errors like this one
Microsoft OLE DB Provider for ODBC Driverserror '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
/sv/showproducts.asp, line 31
The response indicates if the web site is vulnerable or in other case might give us a clue of another request that will reveal the vulnerability itself. The web is full of articles for penetration testers on SQL Injection step by step.
3.2 I am vulnerable what should I do?
SQL Injection in deed is a scary vulnerability. However it is still a crime to use it so panic is not the way to handle it. There are many ways to defend against SQL Injection the longest one is to fix your site.
Make your developing team work days and night until all vulnerabilities are fixed. The fix method though is pretty simple. You need to develop one server side procedure that validates the user input and encode it (it is always recommended though to develop a client side either – but it is not a must). The procedure should be called each time with different parameter submitted with the form. A very common way to sanitize user input is by using the html encoding methods that exists in almost all developing languages. These methods translate single quote, double quote, <, > and some other special characters to safe characters that will prevent SQL Injection as well as cross site scripting attacks.
However as mentioned this is the longest solution and unfortunately a partial one. The bigger your web application is the more time it’ll take to fix it. Furthermore, there are web sites which are constantly develop and fixing the previously programmed pages is needed but who promise that the new pages won’t be vulnerable too. Should one scan its application each and every single moment?
A better and more comprehensive solution would be fixing the known vulnerable pages and purchase a web application firewall. Such solution would also solve a situation of a new web page which was developed after fixing the site and the programmer mistakenly forgot to use the input validation procedure. Furthermore, the web application firewall will contribute defending against many other application layer attacks.
3.3 Am I safe if I am not vulnerable?
This is a tricky question, if your site is safe and is never modified or broadened then you are safe. On the other hand, in most cases web sites are never stagnated, they are always changed and getting bigger and bigger. These changes are of course in favor of the web application but in the same time might cause a safe application today to be vulnerable tomorrow.
Hence, web application firewall is once again recommendable for such cases. It will give you the safety net needed, in cases forenamed and will supply a full coverage solution. Additionally, you will enjoy of the benefit of comprehensive application layer security which will provide a wider solution for all application layer vulnerabilities.
3.4 I am planning my new web site, what should I do to avoid SQL Injection?
Planning a new web site can benefit from the experience of those who where the market guinea pig. Therefore, when coming to develop a new web application one should consider all mentioned above. First of all and most important plan your security mechanisms:
Consult with security experts/consulting companies about common application security architecture.
Develop and deploy security mechanisms.
Use as many out of the box solutions for security mechanisms such as encryption algorithms, authentication methods and input validations. Make sure these are fully patched and up to date.
Consider deploying application security firewall before going on air.
During the development process execute scans with application layer scanners on finished web pages and look for application layer vulnerabilities.
