Database Record Manipulation Practice

A Quick Review of DML Commands

Before we begin the lab, let's briefly refresh our memory on the three core DML commands. If you'd like a more detailed visual recap, the following video provides excellent explanations for each.

The Practice Lab: User Account Management

For this lab, we will use an online, interactive SQL interpreter. This provides a safe "sandbox" where you can execute commands without any risk.

Now, let's begin the lab. For each task, first clear any existing text in the interpreter. Then, type or paste the provided SQL commands and run them to see the result.

Task 1: Create the users Table

First, we need a table to work with. In a real-world scenario, this table would already exist, but creating it ourselves is good practice.

Execute the following CREATE TABLE statement. This defines a simple users table with fields that are highly relevant in a security context.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE,
    is_active INTEGER DEFAULT 1,
    created_at TEXT
);

After running this, the table is created but empty. You can verify this by running SELECT * FROM users;.

Task 2: Onboard New Users (INSERT)

Your first administrative duty is to add new employees to the system.

Scenario A: Add a Single User
A new analyst, 'alice', has joined the team. Her email is 'alice@secure.corp'. Add her to the users table.

INSERT INTO users (user_id, username, email, created_at) 
VALUES (101, 'alice', 'alice@secure.corp', '2023-10-27');

Run SELECT * FROM users; to confirm Alice's record was added.

Scenario B: Add Multiple Users
Next, you need to onboard a group of new developers: 'bob' and 'charlie'. We can do this more efficiently in a single statement.

Your turn: Based on the syntax for inserting multiple rows, write and execute a query to add the following two users:

• user_id: 102, username: 'bob', email: 'bob@secure.corp', created_at: '2023-10-27'
• user_id: 103, username: 'charlie', email: 'charlie@secure.corp', created_at: '2023-10-27'

INSERT INTO users (user_id, username, email, created_at) VALUES 
(102, 'bob', 'bob@secure.corp', '2023-10-27'),
(103, 'charlie', 'charlie@secure.corp', '2023-10-27');

Task 3: Modify User Records (UPDATE)

Administrative tasks frequently involve modifying existing data.

Scenario A: Deactivate an Account
User 'charlie' is going on extended leave. As a security measure, his account must be temporarily deactivated. We do this by changing the is_active flag from 1 (active) to 0 (inactive).

UPDATE users
SET is_active = 0
WHERE username = 'charlie';

Verify with SELECT * FROM users; that Charlie's is_active status is now 0.

Scenario B: Change a User's Email
User 'bob' has changed his email address to 'robert.p@secure.corp'.

Your turn: Write and execute an UPDATE statement to change the email for the user with user_id 102.

UPDATE users
SET email = 'robert.p@secure.corp'
WHERE user_id = 102;

Task 4: Offboard a User (DELETE)

When an employee leaves the company, their data must be handled appropriately.

Scenario: Remove a Contractor's Account
A contractor with the username 'temp_worker' had temporary access. Their contract is over, and their account must be removed. First, let's add this user so we have something to delete.

INSERT INTO users (user_id, username, created_at) 
VALUES (901, 'temp_worker', '2023-08-01');

Now, write and execute the DELETE statement to permanently remove the user 'temp_worker' from the table. Remember the defensive programming habit we discussed: always use a WHERE clause.

DELETE FROM users
WHERE username = 'temp_worker';

After running the command, use SELECT * FROM users; one last time to confirm the record is gone.

A Note on Deactivating vs. Deleting:
From a security and auditing standpoint, deactivating a user (UPDATE is_active = 0) is often preferred over deleting them (DELETE). Deactivation revokes access but preserves the user's record and activity history for future investigation. A DELETE is permanent and erases that history. The correct choice depends on your organization's data retention and security policies.

From Practice to Payloads

You have just successfully managed the lifecycle of user data. Now, consider this from an attacker's perspective. The DELETE command you just used to remove a single, specific user can be repurposed for malicious intent.

An attacker who finds an injection vulnerability might not politely delete a single user. Instead, they might inject a command like '; DELETE FROM users; --. If the application is vulnerable, the database could execute your DELETE command without the WHERE clause, wiping out the entire user table. Your hands-on practice today should make the mechanics and severity of such an attack crystal clear.

Conclusion

In this lab, you have applied the core DML commands—INSERT, UPDATE, and DELETE—in a practical, security-focused context. You have moved beyond simple syntax to perform tasks that are fundamental to managing and securing user data.

Key Takeaways:

• You practiced adding single and multiple records to a table using INSERT.
• You modified specific records safely using UPDATE with a WHERE clause to deactivate an account and change an email.
• You removed a specific record using DELETE with a WHERE clause.
• You've critically considered the difference between deactivating (reversible, preserves audit trail) and deleting (permanent) from a security policy perspective.
• Most importantly, you've connected these legitimate administrative actions to their potential for malicious abuse in an SQL injection attack.

This lesson concludes our module on modifying data. You are now prepared to move into the next phase of our cybersecurity focus: reconnaissance. In the next lesson, you will learn how attackers (and penetration testers) probe a database's structure by querying its metadata tables, like INFORMATION_SCHEMA, to discover tables and columns to target.