Database Testing

Types of  Database Testing

1.  Structural Database Testing : It deals with table and column testing, schema testing, stored procedures and views testing, checking triggers, etc.

2. Functional Testing  : Involves Black box and White Box testing
3. Non-Functional  : Testing: Involves Load and stress testing

The process to perform database testing is similar to testing of other applications. DB testing can be described with key processes given below.

• Set up the environment
• Run a test
• Check the test result
• Validate according to the expected results
• Report the findings to the respective stakeholders

Various SQL statements are used to develop the Test cases. The most common SQL statement, which is used to perform DB testing, is the Select statement. Apart from this, various DDL, DML, DCL statements can also be used.

Example − Create, Insert, Select, Update, etc.

Database Testing Stages

DB testing is not a tedious process and includes various stages in database testing lifecycle in accordance with the test processes.

The key stages in database testing are −

• Checking the initial state
• Test run
• Outcome validation as per expected result
• Generating the results

First Stage:
-Cleaning up the database 
-Set up Fixture
-Perform test, verify results and generate results 

This chapter explains the most common techniques that are used to perform Database Testing.

What to Test in Database Test:
 1. Ensure Data mapping.
 2. Ensure ACID properties of transaction.
 3. Ensure Data Integrity
 4. Ensures accuracy of implemented business rules. 

Database Schema Testing

As mentioned earlier, it involves testing each object in the Schema.

Verifying Databases and devices

• Verifying the name of database
• Verifying the data device, log device and dump device
• Verifying if enough space allocated for each database
• Verifying database option setting

Tables, columns, column types rules check

Verify the items given below to find out the differences between actual and applied setting.

• Name of all the tables in database
• Column names for each table
• Column types for each table
• NULL value checked or not
• Whether a default is bound to correct table columns
• Rule definitions to correct table names and access privileges

Key and Indexes

Verify the Key and indexes in each table −

• Primary key for each table
• Foreign keys for each table
• Data types between a foreign key column and a column in other table Indices, clustered or non-clustered unique or not unique

Stored Procedure Tests

It involves checking whether a stored procedure is defined and the output results are compared. In a Stored Procedure test, the following points are checked −

• Stored procedure name
• Parameter names, parameter types, etc.
• Output − Whether the output contains many records. Zero rows are effected or only a few records are extracted.
• What is the function of Stored Procedure and what a stored procedure is not supposed to do?
• Passing sample input queries to check if a stored procedure extracts correct data.
• Stored Procedure Parameters − Call stored procedure with boundary data and with valid data. Make each parameter invalid once and run a procedure.
• Return values − Check the values that are returned by stored procedure. In case of a failure, nonzero must be returned.
• Error messages check − Make changes in such a way that the stored procedure fails and generate every error message at least once. Check any exception scenarios when there is no predefined error message.

Trigger Tests

In a Trigger test, the tester must perform the following tasks −

• Make sure the trigger name is correct.
• Validate the trigger if it is generated for a specific table column.
• Trigger’s update validation.
• Update a record with a valid data.
• Update a record with invalid data and cover every trigger error.
• Update a record when it is still referenced by a row in other table.
• Ensure rolling back transactions when a failure occurs.
• Find out any cases in which a trigger is not supposed to roll back transactions.

Server Setup Scripts

Two types of tests should be performed −

• Setting up the database from scratch, and
• To set up an existing database.

Integration Tests of SQL Server

Integration tests should be performed after you are through with component testing.

• Stored procedures should be called intensively to select, insert, update, and delete records in different tables to find any conflicts and incompatibility.
• Any conflicts between schema and triggers.
• Any conflicts between stored procedures and schema.
• Any conflicts between stored procedures and triggers.

Functional Testing Method

Functional testing can be performed by dividing the database into modules as per functionality. The functionalities are of the following two types −

• Type 1 − In Type 1 testing, find out the features of the project. For each major feature, find out the schema, triggers, and stored procedures responsible to implement that function and put them into a functional group. Then test each group together.
• Type 2 − In Type 2 testing, the border of functional groups in a back-end is not obvious. You can check the data flow and see where you can check the data. Start from the front-end.

The following process takes place −

• When a service has a request or saves data, some stored procedures will get called.
• The procedures will update some tables.
• Those stored procedures will be the place to start testing and those tables will be the place to check the test results.

Stress Testing

Stress Testing involves getting a list of major database functions and corresponding stored procedures. Follow the steps given below for Stress Testing −

• Write test scripts to try those functions and every function must be checked at least once in a full cycle.
• Perform the test scripts again and again for a specific time period.
• Verifying the log files to check any deadlocks, failure out of memory, data corruption, etc.

Benchmark Testing

If your database does not have any data problems or bugs, system performance can be checked. A poor system performance can be found in benchmark testing by checking the parameters given below −

• System level performance
• Identify most-likely-used functions/features
• Timing – maximum time, minimum time and average time to perform functions
• Access volume

Testing a Database via Front-end

Back-end bugs can also be found sometimes by doing front-end testing. You can follow the simple steps given below to detect bugs by front-end testing.

• Write queries from the front-end and issue the searches.
• Pick up an existing record, change the values in some fields, and save the record. (It involves the UPDATE statement or update stored procedures and update triggers.)
• Insert a new menu item in the front-end window. Fill in the information and save the record. (It involves the INSERT statements or insertion stored procedures and deletion triggers.)
• Pick up an existing record, click on the DELETE or REMOVE button, and confirm the deletion. (It involves the DELETE statement or deletion stored procedures and deletion triggers.)
• Repeat these test-cases with invalid data and see how the database responds.

In this chapter, we will see some common database test scenarios with respect to various testing methods.

Structured Database Testing

Common database scenarios with respect to Structured Database Testing are given below −

• Verifying the name of database, verifying the data device, log device and dump device, verifying if enough space allocated for each database and verifying database option setting.
• Names of all the tables in database, column names for each table, column types for each table, null value check or not. Verify the Key and indexes in each table: Primary key for each table, foreign keys for each table.
• Data types between a foreign key column and a column in other table Indices, clustered or non-clustered unique or not unique.

Functional Database Testing

Common Database Test scenarios with respect to Functional Database Testing are −

• Finding out the schema, triggers and stored procedures responsible to implement that function and make them into a functional group and then each group can be tested together.
• Check data flow and see where you can check the data. Start from the front-end.

Non-Functional Database Testing

Common Database Test scenarios with respect to Non-Functional Database Testing are −

• Write test scripts to try major functions and every function must be checked at least once in a full cycle.
• Perform the test scripts again and again for a specific time period.
• Verifying the log files to check any deadlock, failure out of memory, data corruption, etc.
• Write queries from a front end and issue the searches. Pick up an existing record, change values in some fields and save the record. (It involves UPDATE statement or update stored procedures, update triggers.)
• Insert a new menu item in a front-end window. Fill in information and save the record. (It involves INSERT statements or insertion stored procedures, deletion triggers.)
• Pick up an existing record, click on the DELETE or REMOVE button, and confirm the deletion. (It involves DELETE statement or deletion stored procedures, deletion triggers.)
• Repeat these test-cases with invalid data and see how the database responds.

Key Aspects of Data Mapping

Given below are the key aspects of Data Mapping −To check the fields in the UI/Front end forms and mapped consistently with the corresponding DB table. This mapping information is defined in the requirements documents as mentioned above.For any action performed in the front end of an application, a corresponding CRUD ‘Create, Retrieve, Update and delete’ action gets initiated at the back end.A tester will have to check if the right action is invoked and the invoked action in itself is successful or not.

Different Tool for Database Testing:

1. Generating Test data  

2. For Unit Testing: DBUnit , SQLUnit, TSQLUnit

Common Steps in Database Backup and Recovery Testing

In database recovery testing, you need to run the test in the actual environment to check if the system or the data can actually be recovered in case of any disasters and any other unforeseen events in the business environment.

Given below are the common actions performed in Database Recovery Testing −

• Testing of database system
• Testing of the SQL files
• Testing of partial files
• Testing of data backup
• Testing of Backup tool
• Testing log backups

Database Security Testing

Database security testing is done to find the loopholes in security mechanisms and also about finding the vulnerabilities or weaknesses of database system.

Database Security Testing Techniques

Penetration Testing

A penetration test is an attack on a computer system with the intention of finding security loopholes, potentially gaining access to it, its functionality and data.

Risk Finding

Risk Finding is a process of assessing and deciding on the risk involved with the type of loss and the possibility of vulnerability occurrence. This is determined within the organization by various interviews, discussions and analysis.

SQL Injection Test

It involves checking the user inputs in application fields. For example, entering a special character like ‘,’ or ‘;’ in any text box in a user application should not be allowed. When a database error occurs, it means that the user input is inserted in some query, which is then executed by the application. In such a case, the application is vulnerable to SQL injection.

These attacks are a big threat to data as the attackers can get access to important information from the server database. To check SQL injection entry points into your web application, find out code from your code base where direct MySQL queries are executed on the database by accepting some user inputs.

SQL Injection Testing can be performed for Brackets, Commas, and Quotation marks.

Password Cracking

This is the most important check while performing database system testing. To access critical information, hackers can use a password-cracking tool or can guess a common username/password. These common passwords are easily available on internet and also password cracking tools exist freely.

Therefore, it is necessary to check at the time of testing if the password policy is maintained in the system. In case of any banking and finance applications, there is a need to set a strict password policy on all the critical information database systems.

Security Audit of Database System

A security audit is a process of evaluating company’s security policies at a regular time interval to determine whether necessary standards are followed or not. Various security standards can be followed as per business requirement to define the security policy and then assessment of set policies against those standards can be done.

Example of most common security standards are ISO 27001, BS15999, etc.

Database Security Testing Tools

There are various system testing tools available in market, which can be used to test OS and application check. Some of the most common tools are discussed below.

Zed Attack Proxy

It is a penetration-testing tool for finding vulnerabilities in web applications. It is designed to be used by people with a wide range of security experience and as such is ideal for developers and functional testers who are new to penetration testing. It is commonly used for Windows, Linux, Mac OS.

Paros

All HTTP and HTTPS data between server and client, including cookies and form fields, can be intercepted and modified using these scanners. It is used for Cross-platform, Java JRE/JDK 1.4.2 or above.

Social Engineer Toolkit

It is an open source tool and human elements are attacked rather than the system element. It enables you to send emails, java applets etc. containing the attack code. It is preferred for Linux, Apple Mac OS X and Microsoft Windows.

Skipfish

This tool is used to scan their sites for vulnerabilities. Reports generated by the tool are meant to serve as a foundation for professional web application security assessments. It is preferred for Linux, FreeBSD, MacOS X, and Windows.

Vega

It is an open source, multiplatform web security tool that is used to find instances of SQL injection, cross-site scripting (XSS), and other vulnerabilities in web applications. It is preferred for Java, Linux, and Windows.

Wapiti

Wapiti is an open source and web-based tool that scans the web pages of the web application and check for scripts and forms where it can inject data. It is built with Python and can detect File handling errors, Database, XSS, LDAP and CRLF injections, Command execution detection.

Web Scarab

It is written in Java and is used for analyzing the applications that communicate through HTTP/HTTPS protocols. This tool is primarily designed for developers who can write code themselves. This tool is not OS dependent.

Database Testing – Challenges

To perform database testing successfully, a tester should collect the requirements from all the sources, like technical and functional requirements. There is a possibility that a few requirements are at a high level, so there is a need to breakdown those requirements into the small parts. Testing database is a complex task and the testers face many challenges while performing this testing. Most common database testing challenges are −

Testing scope is too large

A tester needs to identify the test items in database testing otherwise he may not have a clear understanding of what he would test and what he would not test. Therefore, if you are clear on the requirement, you may waste a lot of time testing uncritical objects in the database.

When you have a list of objects to test, next is to estimate the effort required to design the tests and execute the tests for each test item. Depending on their design and data size, some database tests may take a long time to execute.

As the database size is too large, it becomes a big challenge to find out the objects that have to be tested and those which are to be left out.

Scaled-down test database

Normally testers are provided with a copy of the development database to test. That database only have little data, which is sufficient to run the application. So there is a need to test the development, staging and as well as production database system.

Changes in database structure

This is one of the common challenges in DB testing. Sometimes, it happens that you design or execute a test, and the database structure has been changed at that time. This is necessary that you should be aware of the changes made to the database during testing.

Once the database structure changes, you should analyze the impact of the changes and modify the tests. In addition, if multiple users use the test database, you would not be sure about the test results so you should ensure that the test database is used for testing purpose only.

Another challenge in DB testing is that you run multiple tests at the same time. You should run one test at a time at least for the performance tests. You do not want your database performing multiple tasks and under-reporting performance.

Complex test plans

The database structure is normally complex and it has huge data, so there is a possibility that you are executing incomplete or same tests repeatedly. So there is a need to create a test plan and proceed accordingly and checking the progress regularly.

Good understanding of SQL

To test a database, you should have a good knowledge of SQL queries and the required database management tools.