Database Testing

Now a day’s databases are an important part of any software that is being developed. In order to communicate with a database we have to write queries. For complex or large software require certain level of expertise to perform complex tasks like

· Database monitoring

· Database auditing

· Database optimization

· Database models (database schema) etc.

Database Testing is an important aspect that a “Software Tester” should be aware of. We will discuss some important aspects of database testing over here.

Why do we test database?
It's important to test the database that the software applications use. Database holds confidential and valuable information which is not liked to be compromised in any case. Testing the database provides us with a solid feedback essential for identifying defects and fixing them.

What to test in database testing?
We need to consider the threats within the database (White box Testing) as well as at the interface level (Black Box Testing).

Black Box testing
Input data
Output Data (from queries, views, stored procedures)

White Box testing(Clear box testing)
Unit tests for Stored Procedures / functions
Triggers / Views code
Referential Integrity

Database Testing

Database security is the system, processes, and procedures that protect a database from unintended activity. Unintended activity can be categorized as authenticated misuse, malicious attacks or inadvertent mistakes made by authorized individuals or processes. “Database security” is also a specialty within the broader discipline of computer security.

Traditionally databases have been protected from external connections by firewalls or routers on the network perimeter with the database environment existing on the internal network opposed to being located within a demilitarized zone. Additional network security devices that detect and alert on malicious database protocol traffic include network intrusion detectionsystems along with host-based intrusion detection systems.

Database security is more critical as networks have become more open.

Databases provide many layers and types of information security, typically specified in the data dictionary, including:

§ Access Control

§ Auditing

§ Authentication

§ Encryption

§ Integrity controls

“Database security” can begin with the process of creation and publishing of appropriate security standards for the database environment. The standards may include specific controls for the various relevant database platforms; a set of best practices that cross over the platforms; and linkages of the standards to higher level polices and governmental regulations.⁶

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.⁷

Figure 1^[2]

How to test?
When we want to test our database, we would need test databases that are copy (replica) of the original database. These are sometimes called as 'sandboxes' in agile terms. In this test database (sandbox) you will rebuild your system and then run all the tests to ensure you haven't broken anything (if so, then back to the development sandbox). Occasionally, at least once an iteration/cycle, we'll deploy our work to the level (demo and pre-production testing), and rerun our test suite (including database tests) each time that we do so to ensure that our changes integrate with the changes made by other developers in our organization working on the same database.
We need to create database tests based on either rebuilding the existing database or starting afresh with creation of database and related schema. Identifying Test Data is an important task here. Once the tests are ready, we would execute them and check the results. We rate the tests pass or fail according to the standards we have set in.

Some tools available for the Db testing are:

· CompuWare DevPartnerDB

DevPartnerDB simplifies rapid, high-quality application development by helping developers debug stored procedures and tune SQL statements. DevPartnerDB has support for Oracle, Microsoft SQL Server and Sybase, and also supports the development of applications in Visual Studio .NET.

· ScandiaSoft DbValidator

DbValidator, was designed to simplify the creation of automation tests for SQL Server database schemas.

· dbUnit SourceForge

DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs. This is an excellent way to avoid the myriad of problems that can occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage.

Reference links

1. http://en.wikipedia.org/wiki/Database

2. http://www.agiledata.org/essays/databaseTesting.html

3. http://www.dbunit.org/

4. http://scandiasoft.com/products.html

5. http://www.embeddedstar.com/software/content/c/embedded529.html

6. http://en.wikipedia.org/wiki/Database_security

7. http://en.wikipedia.org/wiki/SQL_injection

Shoaib Shafique

Serving as,

Testing QA Engineer

Kualitatem Pvt Ltd.