Understanding Column Constraints in Table Definitions

The Importance of Constraints

Before we dive into identifying constraints, it's important to understand why they exist. Database constraints are rules enforced on data columns to ensure the accuracy and reliability of the data. They prevent actions that would compromise data integrity. For example, a NOT NULL constraint ensures a value must be entered, while a PRIMARY KEY guarantees that every row is uniquely identifiable.

Let's watch a brief segment that explains the problems that arise when constraints are absent.

As you can see, without constraints, a database table can quickly fill with incomplete or inconsistent data, making it unreliable. Now, let's explore how to discover these rules in an existing database.

Method 1: The DESCRIBE Command (MySQL-Specific)

When performing reconnaissance on a MySQL database, one of the quickest ways to inspect a table's structure is with the DESCRIBE command (which can be shortened to DESC). While not part of the SQL standard, it is widely used and provides a concise summary of a table's columns and their properties.

The output of DESCRIBE gives you several key pieces of information at a glance:

• Field: The column name.
• Type: The data type of the column.
• Null: Indicates if the column can store NULL values. NO signifies a NOT NULL constraint.
• Key: This is the most important column for identifying constraints.
  • PRI: The column is a PRIMARY KEY.
  • UNI: The column has a UNIQUE constraint.
  • MUL: The column is the first column of a non-unique index. This often indicates a FOREIGN KEY.
• Default: The default value for the column.
• Extra: Additional information, such as auto_increment.

Let's look at a practical example.

By analyzing this output, a penetration tester can quickly deduce:

• student_id is the PRIMARY KEY (PRI), is NOT NULL, and is auto-incrementing. This is the main identifier for a student.
• first_name and last_name are mandatory fields (NOT NULL).
• email has a UNIQUE constraint (UNI), meaning no two students can have the same email. This makes the email column a reliable alternative identifier for a user.

While DESCRIBE is fast and convenient, it's specific to MySQL. For a more universal and powerful approach, we turn back to the INFORMATION_SCHEMA.

Method 2: The Standard Approach with INFORMATION_SCHEMA

Just as we used information_schema.tables and information_schema.columns in the previous lesson, we can use other views within the INFORMATION_SCHEMA to get detailed information about constraints. This method is compliant with the SQL standard and works across multiple database systems like PostgreSQL, SQL Server, and MySQL.

The primary views for this task are:

• information_schema.table_constraints: Lists the constraints that exist for tables.
• information_schema.key_column_usage: Maps the constraints to the specific columns they apply to.

By querying these views, you can systematically uncover all the rules governing the database tables.

The following video provides an excellent demonstration of how to query these views to find different types of constraints.

To put this into practice, you can use JOINs to link the constraints to the columns they affect. Let's look at some specific queries you can use.

Here are the key query patterns based on that resource:

To Find Primary Keys:
This query joins table_constraints and key_column_usage to find columns that are part of a primary key.

SELECT
    kc.table_schema,
    kc.table_name,
    kc.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kc 
    ON kc.constraint_name = tc.constraint_name AND kc.table_schema = tc.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY';

To Find Unique Constraints:
The structure is nearly identical; you just change the constraint_type.

SELECT
    tc.table_name, 
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'UNIQUE';

This is how an attacker would confirm if a username or email field must be unique, making it a target for user enumeration vulnerabilities.

Using these standard queries allows you to build a scriptable and repeatable process for database reconnaissance that works across different environments, a key skill for any security analyst.

Conclusion

In this lesson, you've moved beyond simply listing database objects to understanding the rules that govern them. You now know how to identify crucial column constraints, which dictate data integrity and reveal the underlying logic of the database schema.

Key Takeaways:

• Constraints like PRIMARY KEY, UNIQUE, and NOT NULL are essential for maintaining data integrity.
• An attacker uses knowledge of constraints to refine their understanding of the database and identify key columns for targeting.
• You can quickly inspect a table in MySQL using the DESCRIBE command, which shows key properties in its Null and Key output columns.
• The SQL-standard method involves querying information_schema.table_constraints and information_schema.key_column_usage to systematically find PRIMARY KEY, UNIQUE, and other constraints.

You have now completed the core of database schema reconnaissance: finding tables, columns, and their constraints. In the next lesson, we will investigate the final piece of the puzzle: permissions. You'll learn how to check which users can perform which actions (SELECT, INSERT, UPDATE, etc.) on these tables, completing your understanding of the database's security posture before we move on to exploiting vulnerabilities.