← Back to Module

WEB-SQL-01

SQL Injection

CMU540: Cyber Security - Session 4

Birmingham Newman University

Lecturer: James Williams

Understanding SQL injection vulnerabilities and secure database practices

3-hour session • 30 slides • 2 interactive tasks

Session Timeline:

  • 10 min: Registration & waiting
  • 20 min: Opening slides
  • 45 min: Task 1
  • 15 min: Break/Catch up
  • 20 min: Secondary slides
  • 45 min: Task 2
  • Remaining: Self-study

Learning Objectives

  • Understand OWASP A03: SQL Injection
  • Identify common SQL injection attack vectors
  • Learn secure database query practices
  • Practice vulnerability discovery using OS³ Studio
  • Implement secure database solutions
  • Explore career opportunities in database security

OWASP A03: SQL Injection

Definition: SQL injection occurs when malicious SQL code is inserted into application queries, allowing attackers to manipulate databases.

Key Attack Types:

  • Union-based injection
  • Boolean-based blind injection
  • Time-based blind injection
  • Error-based injection

Real-World Impact

Notable Breaches:

  • Heartland Payment Systems (2008): 130 million credit cards compromised
  • TalkTalk (2015): 157,000 customer records exposed
  • Yahoo (2013-2014): 3 billion accounts compromised

Impact: Data theft, financial loss, reputation damage

Common SQL Injection Vulnerabilities

1. String Concatenation

<!-- VULNERABLE: String concatenation -->
String query = "SELECT * FROM users WHERE username = '" + username + "'";

// Input: admin' OR '1'='1
// Result: SELECT * FROM users WHERE username = 'admin' OR '1'='1'

2. Dynamic Query Building

<!-- VULNERABLE: Dynamic queries -->
String query = "SELECT * FROM products WHERE category = " + categoryId;

// Input: 1; DROP TABLE users; --
// Result: SELECT * FROM products WHERE category = 1; DROP TABLE users; --

Union-Based Injection

Attack Technique:

<!-- Original query -->
SELECT name, email FROM users WHERE id = 1

<!-- Injected query -->
SELECT name, email FROM users WHERE id = 1 UNION SELECT username, password FROM admin_users

<!-- Result: Data from both tables -->

Impact: Unauthorized data access, information disclosure

Boolean-Based Blind Injection

Attack Technique:

<!-- Testing for vulnerability -->
SELECT * FROM users WHERE id = 1 AND 1=1 -- Returns data
SELECT * FROM users WHERE id = 1 AND 1=2 -- Returns no data

<!-- Extracting data character by character -->
SELECT * FROM users WHERE id = 1 AND ASCII(SUBSTRING(password,1,1)) > 65
SELECT * FROM users WHERE id = 1 AND ASCII(SUBSTRING(password,1,1)) > 66

Method: Use boolean conditions to extract data without direct output

Time-Based Blind Injection

Attack Technique:

<!-- Testing for vulnerability -->
SELECT * FROM users WHERE id = 1; WAITFOR DELAY '00:00:05' -- 5 second delay

<!-- Extracting data with timing -->
SELECT * FROM users WHERE id = 1 AND IF(ASCII(SUBSTRING(password,1,1)) > 65, SLEEP(5), 0)

<!-- If condition is true, response is delayed -->

Method: Use time delays to infer data without direct output

Error-Based Injection

Attack Technique:

<!-- Triggering database errors -->
SELECT * FROM users WHERE id = 1 AND (SELECT * FROM (SELECT COUNT(*),CONCAT(version(),FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a)

<!-- Error reveals database version -->
Duplicate entry '5.7.29-0ubuntu0.18.04.1' for key 'group_key'

Method: Exploit error messages to extract sensitive information

Second-Order SQL Injection

Attack Scenario:

<!-- Step 1: Store malicious input -->
INSERT INTO comments (text) VALUES ('admin''--');

<!-- Step 2: Later query uses stored data -->
SELECT * FROM users WHERE username = 'admin'--' AND password = 'password'

<!-- Result: Bypasses password check -->

Risk: Input validation doesn't prevent stored malicious data from being executed

NoSQL Injection

MongoDB Example:

<!-- VULNERABLE: Direct object injection -->
db.users.find({username: req.body.username, password: req.body.password})

<!-- Malicious input -->
{"username": {"$ne": null}, "password": {"$ne": null}}

<!-- Result: Matches any user with non-null username/password -->

Risk: NoSQL databases are also vulnerable to injection attacks

Career Opportunities in Database Security

Database Security Roles:

  • Database Security Specialist: £45,000 - £85,000
  • Penetration Tester: £35,000 - £70,000
  • Security Consultant: £50,000 - £100,000
  • Database Administrator: £40,000 - £80,000
  • Application Security Engineer: £45,000 - £90,000

Skills Needed: SQL, Database security, Web application security, Penetration testing

Industry Certifications

Database Security Certifications:

  • OSCP: Offensive Security Certified Professional
  • CEH: Certified Ethical Hacker
  • CISSP: Certified Information Systems Security Professional
  • GWAPT: GIAC Web Application Penetration Tester
  • OSWE: Offensive Security Web Expert

Resources: Offensive Security | EC-Council

OS³ Newman Cyber Security Lab

WEB-SQL-01 Lab Environment

Our OS³ Studio provides hands-on experience with:

  • Vulnerable web applications with SQL injection flaws
  • Real-world attack scenarios and techniques
  • Secure implementation challenges
  • Database security best practices

Access: Available through university portal

Practical Examples

Watch: SQL Injection Explained

Understanding real-world SQL injection vulnerabilities and attack techniques

Web Demos and Tools

SQL Injection Testing Tools:

Case Study: Heartland Payment Systems

2008 Data Breach

  • Impact: 130 million credit card records compromised
  • Cause: SQL injection vulnerability in web application
  • Method: Malicious SQL code injected through web forms
  • Cost: $140 million in fines and settlements

Lesson: Even large organizations can fall victim to basic SQL injection attacks

Summary: Common SQL Injection Vulnerabilities

Key Vulnerabilities to Look For:

  1. String concatenation in SQL queries
  2. Dynamic query building
  3. Lack of input validation
  4. Missing parameterized queries
  5. Insufficient error handling
  6. NoSQL injection vulnerabilities
  7. Second-order injection risks

Task 1: SQL Injection Vulnerability Discovery

Objective:

Use OS³ Studio to identify SQL injection vulnerabilities in the WEB-SQL-01 lab environment.

Instructions:

  1. Access the OS³ Studio vulnerable application
  2. Test for basic SQL injection vulnerabilities
  3. Attempt union-based injection attacks
  4. Test for boolean-based blind injection
  5. Try time-based blind injection techniques
  6. Look for error-based injection opportunities
  7. Document all findings with proof of concept
  8. Prepare a vulnerability assessment report

Time: 45 minutes

Focus on systematic testing and thorough documentation

Break Time

15 Minutes

Take a break, ask questions, or catch up on the previous task.

Next: Secure implementation and Task 2

Secure Database Implementation

1. Parameterized Queries (Prepared Statements)

<!-- SECURE: Parameterized queries -->
String query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

2. Input Validation

<!-- SECURE: Input validation -->
public boolean isValidInput(String input) {
  if (input == null || input.trim().isEmpty()) {
    return false;
  }
  // Check for SQL injection patterns
  String[] dangerousPatterns = {"'", "\"", ";", "--", "/*", "*/", "xp_", "sp_"};
  for (String pattern : dangerousPatterns) {
    if (input.toLowerCase().contains(pattern)) {
      return false;
    }
  }
  return true;
}

Database Security Best Practices

1. Principle of Least Privilege

<!-- SECURE: Limited database user permissions -->
-- Create application-specific user
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON webapp_db.users TO 'webapp'@'localhost';
GRANT SELECT ON webapp_db.products TO 'webapp'@'localhost';

-- No DROP, CREATE, or ALTER permissions

2. Database Connection Security

  • Use encrypted connections (SSL/TLS)
  • Implement connection pooling
  • Use strong authentication
  • Enable database audit logging

ORM Security

1. Safe ORM Usage

<!-- SECURE: Using ORM safely -->
// Good: Parameterized query
User user = session.createQuery("FROM User WHERE username = :username")
  .setParameter("username", username)
  .uniqueResult();

// Bad: String concatenation
String hql = "FROM User WHERE username = '" + username + "'";
User user = session.createQuery(hql).uniqueResult();

2. ORM-Specific Protections

  • Use parameterized queries in ORM
  • Avoid dynamic query building
  • Implement proper input validation
  • Use ORM's built-in security features

Web Application Firewall (WAF)

1. WAF Configuration

<!-- SECURE: WAF rules for SQL injection -->
# ModSecurity rules
SecRule ARGS "@detectSQLi" \
  "id:1001,phase:2,block,msg:'SQL Injection Attack Detected',\
  logdata:'Matched Data: %{MATCHED_VAR} found within %{MATCHED_VAR_NAME}',\
  tag:'application-multi',tag:'language-multi',tag:'platform-multi',\
  tag:'attack-sqli',severity:'CRITICAL'"

2. Additional Protections

  • Rate limiting on database queries
  • Input sanitization middleware
  • Database query monitoring
  • Anomaly detection

Database Monitoring

1. Query Logging

<!-- SECURE: Database query monitoring -->
// Log all database queries
logger.info("Database Query", {
  query: query,
  parameters: parameters,
  executionTime: executionTime,
  userId: userId,
  ip: request.ip,
  timestamp: new Date()
});

2. Anomaly Detection

  • Monitor unusual query patterns
  • Alert on suspicious database access
  • Track failed authentication attempts
  • Implement query rate limiting

Secure Coding Practices

1. Code Review Checklist

  • All database queries use parameterized statements
  • Input validation implemented
  • Error handling doesn't expose sensitive information
  • Database connections use least privilege
  • No dynamic query building

2. Automated Security Testing

<!-- Security testing tools -->
npm install --save-dev eslint-plugin-security
npm install --save-dev sqlmap

// Run security audit
npm audit
sqlmap -u "http://example.com/login" --batch

Compliance and Standards

1. Industry Standards

  • OWASP Top 10: SQL injection prevention
  • PCI DSS: Payment card industry standards
  • ISO 27001: Information security management
  • NIST SP 800-53: Security controls

2. Best Practices

  • Use parameterized queries exclusively
  • Implement defense in depth
  • Regular security assessments
  • Incident response planning

Career Development in Database Security

Next Steps:

  • Advanced Certifications: OSCP, OSWE, GWAPT
  • Specialized Training: Database security, Web app testing
  • Hands-on Practice: CTF competitions, bug bounties
  • Industry Networking: Security conferences, meetups
  • Research: New attack techniques, defense methods

Resources: OWASP | PortSwigger | PentesterLab

Task 2: Secure Database Implementation

Objective:

Use OS³ Studio to implement secure database practices and fix the SQL injection vulnerabilities found in Task 1.

Instructions:

  1. Access the OS³ Studio secure implementation environment
  2. Replace all dynamic queries with parameterized queries
  3. Implement proper input validation
  4. Configure database user with least privilege
  5. Add database query monitoring
  6. Implement error handling that doesn't leak information
  7. Test the secure implementation
  8. Document the security improvements

Time: 45 minutes

Focus on implementing industry-standard security practices

Further Activity: Code Inspection

Advanced Students - Code Analysis:

For students with additional time, explore the source code to understand:

  • How database queries are constructed
  • Input validation mechanisms
  • Error handling and logging
  • Database connection management
  • ORM configuration and security

Deliverable: Code review report with security recommendations

Session Summary

Key Takeaways:

  • SQL injection is a critical security vulnerability
  • Multiple attack techniques exist (union, boolean, time-based, error-based)
  • Parameterized queries are the primary defense
  • OS³ Studio provides hands-on vulnerability testing
  • Secure implementation requires multiple layers of protection
  • Career opportunities in database security are growing

Next Steps

Continue Learning:

  • Complete the OS³ Studio tasks
  • Explore additional SQL injection techniques
  • Practice with security testing tools
  • Consider industry certifications
  • Join cybersecurity communities

Next Session: Advanced Web Security Topics