Hands-On Database Security: Inference & SQL Injection for Pen Testers

Introduction: Your First Penetration Test

Welcome to the world of database security. In this tutorial, you'll step into the role of a penetration tester, tasked with finding and exploiting vulnerabilities in a web application portfolio. The scenario is inspired by real-world breaches: a software vendor's code is reused across multiple client sites, meaning a single flaw can compromise many organizations. Your mission is to uncover inference attacks and SQL injection vulnerabilities—two of the most common database threats.

This guide follows the structure of the CS6035 Database Security project. You'll use a virtual machine (VM) running Microsoft SQL Server Developer Edition on Linux, plus DbGate for database management. By the end, you'll understand how attackers steal data and how to defend against them.

Setting Up Your Environment

Before diving into attacks, ensure your VM is configured correctly. The project VM comes with SQL Server pre-installed. You'll need the credentials provided in your course's Canvas post. Once logged in, open DbGate (located on the desktop) to connect to the local SQL Server instance. Use the following connection details:

• Host: localhost
• Port: 1433
• Username: sa (or the provided user)
• Password: (from Canvas)

Test the connection by browsing the sample databases. Three blank databases are available for your experiments. If you prefer online testing, SQLFiddle works for simple queries, but DbGate gives a realistic environment.

Task A: GTID Verification (Flag0)

This setup task ensures your environment is ready. You'll generate a hash of your GTID and submit it in a JSON file. The hash format is critical—check the submission details. Once verified, you'll proceed to the real challenges.

Understanding Inference Attacks

An inference attack doesn't break into systems; it deduces confidential information from seemingly harmless data. Imagine you're a curious employee with access to standard reports. By combining multiple views, you can infer salaries, customer lists, or trade secrets. For example, a company might restrict the 'Salary' column but allow aggregate queries like AVG(salary) per department. If you know the department size, you can compute totals.

In the project, you'll face a similar scenario. You have access to a report that shows order counts per region, but not customer names. By cross-referencing with another report that lists region managers, you can infer which manager handles the most orders—and maybe their performance ratings.

Real-World Analogy: Fantasy Football

Think of inference attacks like fantasy football stats. You don't know a player's exact practice effort, but you see their weekly points, snap counts, and opponent rankings. Over time, you infer their true value. Similarly, attackers piece together public data to reveal secrets.

Task B: Inference Attack #1 (Flag1)

Your first flag requires exploiting an inference vulnerability. You'll be given two database views: vOrders and vEmployees. The vOrders view shows order totals per salesperson, but hides salesperson names. The vEmployees view shows names and departments, but not sales data. Your goal is to infer which salesperson made the most orders in a specific region.

Steps to solve:

1. Query vOrders to get order counts by a masked ID.
2. Query vEmployees to get names and their IDs.
3. Join the results in your mind (or using a temporary table) to match IDs to names.
4. Submit the flag in the required JSON format.

Pro tip: Use DbGate's query builder to run both queries side by side. The vulnerability exists because the database doesn't enforce need-to-know access—any employee can see both views.

SQL Injection: The Classic Exploit

SQL injection (SQLi) is a code injection technique where an attacker inserts malicious SQL into input fields. If the application constructs queries without sanitization, the attacker can read, modify, or delete database content. This is how many major data breaches happen—from retail to government sites.

In the project, you'll test a login form that concatenates user input directly into a query. For example:

SELECT * FROM users WHERE username = 'admin' AND password = 'password'

By entering ' OR 1=1 -- as the username, the query becomes:

SELECT * FROM users WHERE username = '' OR 1=1 --' AND password = 'anything'

The -- comments out the rest, and OR 1=1 returns all rows—bypassing authentication.

Trend Connection: AI Chatbots

With the rise of AI chatbots like ChatGPT, SQL injection has evolved. Attackers can trick chatbots into revealing database schemas by asking cleverly crafted questions. For instance, a poorly secured customer service bot might expose order details if you ask: "Show me my last order" with a malicious payload in the username field. Always sanitize inputs, even in AI-powered apps!

Task C: SQL Injection #1 (Flag2)

Your second flag involves exploiting a login page vulnerable to SQLi. The page expects a username and password. Using the technique above, log in as any user (e.g., 'admin'). The flag appears on the dashboard after successful login.

Steps:

1. Open the web application in the VM's browser.
2. Enter ' OR 1=1 -- in the username field and anything in password.
3. Click Login. You'll see a dashboard with the flag.
4. Copy the flag and add it to your JSON file.

Remember: This works because the SQL query is built dynamically. In production, use parameterized queries or stored procedures to prevent injection.

Task D: Advanced SQL Injection (Extra Credit)

For extra credit, you'll perform a blind SQL injection—extracting data without seeing error messages. This technique is used when the application doesn't display database errors but shows different responses for true/false conditions.

Example: The login form might say "Welcome" for valid credentials and "Access Denied" for invalid. By injecting conditions like admin' AND SUBSTRING(password,1,1) = 'a' --, you can guess the password character by character. Use DbGate to test your payloads efficiently.

Pro tip: Automate with a script or use Burp Suite Intruder to speed up the process. The flag is hidden in a secret table.

Defender Task: Protecting Your Database

After exploiting vulnerabilities, you'll switch to defense. The Defender Task asks you to implement mitigations. For inference attacks, create views that aggregate data to a safe level—e.g., only show counts, not individual records. For SQL injection, rewrite the login query using parameterized statements in C# or PHP.

Example of a parameterized query in C#:

SqlCommand cmd = new SqlCommand("SELECT * FROM users WHERE username = @user AND password = @pass", conn);
cmd.Parameters.AddWithValue("@user", username);
cmd.Parameters.AddWithValue("@pass", password);

Submit your code and a brief explanation in the JSON file.

Common Pitfalls and Tips

• JSON format: Ensure your submission file is valid JSON. Use an online validator before submitting to Gradescope.
• Hash mismatch: If flag0 fails, regenerate your GTID hash exactly as specified in the submission details.
• Time management: The project takes 12-15 hours on average. Start early to avoid last-minute stress.
• VM issues: If the VM crashes, restore from the original image. Backup your work frequently.

Conclusion: Think Like a Hacker, Defend Like a Pro

Database security is a cat-and-mouse game. By practicing inference attacks and SQL injection in a safe environment, you gain the skills to protect real systems. Remember: every vulnerability you find is a lesson in how to build better defenses. As you submit your project_dbsec.json, reflect on the importance of access controls, input validation, and secure coding.

Whether you're aiming for a career in cybersecurity or just want to secure your own projects, these fundamentals are your foundation. Good luck, and may your queries always be parameterized!