Database Testing Fundamentals

Database Testing

• Database Testing is a software testing type that reviews schemas, tables, triggers, and more.
• It verifies data integrity and consistency, potentially using complex queries to stress and assess database responsiveness.
• It is important to ensure the accuracy of data values and information stored in the database.
• It prevents data loss, secures aborted transaction data, and guards against unauthorized access.
• Testers require solid SQL knowledge given the database's importance in applications.
• It is crucial to validate database information, despite GUIs getting more attention as they are more visible.
• Application stores transaction information in the database and displays them correctly to the user.
• No information is lost.
• Partially performed or aborted operation information is saved by the application.
• No unauthorized individual is allowed to access the user's information.
• Data validation or data testing should be used to ensure all objectives.

User-Interface vs. Data Testing

• User-Interface testing, also known as Front-end Testing, focuses on testable items visible and interactive to the user like forms, presentations, and menus, often created with tools like VB.net, and VC++.
• Data Testing, or Backend Testing, concentrates on hidden elements such as internal processes and storage, like DBMS (Oracle, SQL Server, MYSQL).
• User-Interface validates text boxes, dropdowns, calendars, page navigation, images, and application appearance.
• Data testing validates schema, database tables, columns, keys/indexes, stored procedures, server validations and data duplication.
• User-Interface testers should know business needs, development tools, automation frameworks and tools.
• Data testers must be proficient in Structured Query Language and database server concepts.

Types of Database Testing

• Structural Testing validates database elements for data storage not manipulated by end-users, focusing on database servers and needing SQL expertise.
• Schema Testing validates database schema formats.
• It also verifies compatibility between table/view/column mapping formats and the user interface.
• This ensures front-end and back-end schema mapping similarity, sometimes called mapping testing.
• Schema testing involves validating schema formats related to databases, due to table mapping format incompatibility issues compared to the user interface level.
• It is important to verify unmapped tables/views/columns.
• Consistency requires validating heterogeneous databases in an environment according to overall application mapping
• DBUnit is integrated with Ant for mapping testing.
• SQL Server allows testers to check and query the database schema via simple queries, not code.

Stored Procedures Testing

• Ensure developmental teams adopt coding standards and exception/error handling for all stored procedures across modules.
• Verify the development team employs the TRIM operation on data fetched from database tables.
• Determine if manual stored procedure execution gives the end-user the needed outcome.
• Confirm manual stored procedure execution updates table fields as needed.
• Check if stored procedure runs enable required triggers implicitly.
• Validate unused stored procedures and "Allow Null" conditions at database level.
• Verify successful stored procedure and function execution with an empty database.
• Validate overall stored procedure module integration according to application needs.
• LINQ and SP Test Tool are used for testing stored procedures.

Trigger Testing

• Verify the required coding standards have been followed during the coding phase of the Triggers
• Confirm triggers for DML transactions meet needed conditions.
• Determine triggers update data correctly after execution.
• Validation of the required Update/Insert/Delete triggers functionality.

Database Table and Column Testing

• Determine if the backend database field and column mapping matches the front-end.
• Validate database field/column length and naming according to needs.
• Validation of any unused/unmapped database tables/columns.
• Validation of the compatibility of the datatype and field lengths of backend database columns against the frontend.
• The database field allows the user to input data as required by business documents.

Database Server Check

• Review database server settings as specified by business needs.
• Check required user authorization to perform actions.
• Verify the database server meets user-transaction needs as specified.

Keys and Indexes Testing

• Check for required primary and foreign keys.
• Check if there are constraints on requested tables.
• Determines if foreign key references are legitimate.
• Determine if the primary key's data type matches corresponding foreign keys in tables.
• Check that required naming conventions have been used.
• Check size and length of required fields.
• Determining if required clustered and non-clustered indexes were made on needed tables, as business specifies.

Functional Database Testing

• Functional Database Testing is to validate database functional needs from the end-user's POV.
• Database testing checks if end-user transactions work as planned.
• The field must be mandatory when it has NULL fields, all like fields should possess same names across tables and whether there are computed fields?
• Testers conduct operations at the database and UI levels in turn to confirm adequate field validation.
• Tester should validate at backend and begin operations at the user interface.

Data Integrity and Consistency Checks

• Validate the organization of data
• Table data is accurate and satisfies business needs.
• There is no unnecessary data present.
• The data has been stored as per as the requirement with respect to data which has been updated from the user interface.
• Verify TRIM operations on data before database insertion.
• Verify if transactions follow business needs and give correct results.
• Is data committed upon transaction success?
• The data has been rolled backed successfully if the transaction has not been executed successfully by the end-user?

Login and Security Validations

• The application prevents the user from proceeding further if an invalid username but valid password, a valid username but invalid password, and an invalid username and invalid password.
• Review user access to specific operations as specified.
• Ensure data security against unauthorized entry.
• Are different user roles with permissions created?
• All users should have needed database access as business specifications require.
• Determine if sensitive information is encrypted rather than kept as plain text, using hard-to-guess passwords.

Black Box Testing

• Black Box Testing verifies database integration.
• Simple test cases are employed to verify data coming into and out of the function.
• Techniques like cause-effect graphing, equivalence partitioning, and boundary-value analysis are employed to assess database functionality.
• It is simple and done early in development.
• Test-case development is cheaper than white-box testing.
• This method does not detects a few errors and determine amount of needed programming.

White Box Testing

• White Box Testing addresses internal database design and user-hidden details.
• Module testing is done on functions, triggers, views, and SQL queries.
• It validates database tables, models, and schemas, along with Referential integrity.
• It tests on database consistency, selecting default values.
• Common techniques are condition, decision, and statement coverage.
• Errors are spotted early and so internal concerns are eliminated.
• The limitation is that SQL statements are not covered.

Non-Functional Testing

• Non-functional testing can be load testing, stress Testing, Security Testing, Usability Testing, and Compatibility Testing, and so on.
• Performance Testing encompasses load and stress testing, which has two purposes.
• Risk quantification shows stakeholders a variety of system response under various levels of load.
• It involves the processes of risk identification and risk quantification, presents corrective opportunities and an impetus for remediation that will mitigate risk.
• Minimum system equipment requirements include minimum system configuration that meets stakeholder performance expectations, in order to minimize extraneous hardware, software, and ownership costs.

Load Testing

• Load testing’s purpose should be clear and documented.
• The most frequently used user transactions have the potential to impact the performance of all of the other transactions if they are not efficient.
• Non-editing user transaction should be included in the final test suite, so that performance can be differentiated from other transactions.
• Key transactions should be included, as load failure has a significant impact.
• One editable transaction should be included so that performance of transactions can be differentiated from other transactions.
• Load testing require optimum response time under huge number of virtual users.
• They provide effective times for fetching of various records.
• LoadRunner, win runner, and JMeter are effective load testing tools.

Stress Testing

• Database Stress Testing tests database systems under heavy loads to identify breakdown points.
• Proper planning and execution is needed to avoid overuse.
• Torturous / Fatigue testing is data stress testing.
• LoadRunner and JMeter are crucial stress testing tools.

Cloud Database Configuration Errors

• Barely a week goes by without a new data breach caused by insecurely configured cloud databases or storage services.
• Knowing what data you hold, where it is located and implementing effective infrastructure configuration and change management procedures.
• Implementation of cloud databases should have configuration by service, such as firewall.
• Database configuration need authentication.
• Insecure data services should be monitored.

SQL Injection

• SQL injection vulnerabilities occur when dynamic database queries have user supplied input.
• Avoid the use of dynamic queries and implement user input validation.
• Include data tier monitoring/alerting for dynamic queries to find attackers who query the database directly.
• NoSQL databases are prone to injection attacks that necessitates controls such as input validation.

Weak Authentication

• Controls such as account lockout should be implemented.
• Use password blacklisting to prevent users choosing common passwords.
• Multi-factor authentication that an attacker needs more than knowledge of a username and password to illegally access data.
• Use a strong password hashing algorithm such as bcrypt and salt each password with a long, random, unique, string.
• Protect the application database credentials and make sure they are unguessable.

Privilege Abuse

• Users may abuse data access privileges for unauthorized purposes.
• Limit the number accessible to a reasonable maximum to mitigate this.
• The application and its databases should not expose interfaces which allow arbitrary queries and bulk export of data.
• Access to should be logged, regularly audited and limited to as few people as possible.

Excessive Privileges

• Privileges may be abused by the individual or an attacker who compromises their account.
• The problem is addressed by a combination of technical and procedural means like role based access controls.
• When staff change roles, their permissions are updated to reflect this.
• Regular, reviews confirm everyone's role, so, someone who left already still has an active account.

Denial of Service

• Network level Denial of Service (DoS) attacks overload your system regardless of capacity.
• Cloud based DoS protection services have become the usual defense here.
• Resource consumption-based attacks require a different approach, such as request rate limiting.
• Data theft by database backup and hard disks has also long been a concern.

Inadequate Backup

• Backups should be encrypted to protect data.
• Keys must not fall into the wrong hands but must be available when needed.
• Resilience within cloud services is not the same as backup. It's possible is that an attacker to delete so much cloud infrastructure and customer data that an organisation can't survive.

Database Testing

• Testing validates software application heart.
• It confirms database order via backend or data testing.
• Reviews schema, tables, triggers.
• Reveals non-user-facing software aspects, displaying data flow from app to database, as well as data integrity.
• Validates ACID properties: atomicity, consistency, isolation, durability.
• Ensures business rules are exact, as databases record the stored data.
• It is important to test DBs through all areas, information storage, and queries.

Database Testing Objectives

• Testing follows Transaction's ACID Properties.
• There is also Data Mapping.
• This will ensure Accuracy of business rule and also Data Integrity

ACID Properties

• It ensures that four properties such as Atomicity, Consistency, Isolation ad Durability.
• Atomicity signifies data should remain atomic.
• Consistency suggests values persist post-transaction.
• Isolation: This refers to completing all transactions at once, to prevent another change.
• Durability means keeping modifications without failure.

Data Mapping

• It is to check the data that passes though the application to the backend database.
• Front-end methods should constantly map to the equivalent back-end fields.
• Mapping details reside in the application's requirements.
• Each front-end action equates to a backend CRUD function.

Database Testing Processes

• Test Environment consists of configured software, hardware, and network.
• Environment configuration must mirror production.
• Test Environment Setup includes hardware setups, OS settings, software, and test terminals.
• QA needs a fully functional environment to begin testing.
• It can be started with test case development.
• Can be initially done by developers and then by Customers/Business Analysts.
• Smoke testing readies the environment and can undergo validation by the QA team.
• This phase has a criteria, preparation of test data and readiness.

Test Environment Setup Activities

• Design Test Environment includes archiving test environment, network verification, identify required databases and systems, and identify the number of licenses required.
• Environment setup involves reviewal to prepare a software and hardware list.
• Configuration should occur test environment.
• Team should have access to quickly perform testing on the test built.

Test Scenario Generation

• Scenarios include Structured, Function and Non-Functional Database Testing.
• Common Structured Database Testing is verifying database and device dump, log device, enough space and option settings.
• Common Structured Database Testing is verifying the table, column naming, key, value, or no value.
• Functional Database Tests are test scenarios as per schema, responsible triggers or stored procedures.
• It also includes to check and trace data flow.
• Writing function checks, test scripts, testing again a certain period of time, verify log files, writing queries from a frontend, and change values.
• Inserting new menu items, deleting items and repeating if all with invalid data.