Understanding and Mitigating SQL Injection Attacks

{
  "title": "Understanding and Mitigating SQL Injection Attacks",
  "description": "Learn practical techniques to prevent SQL injection attacks and secure databases against malicious queries.",
  "category": "Security",
  "rules": [
    {
      "id": "sql-injection-prevention",
      "description": "Ensure all SQL queries are constructed using parameterized statements or prepared statements to prevent SQL injection vulnerabilities.",
      "severity": "error",
      "patterns": [
        {
          "pattern": "SELECT * FROM users WHERE username = '\" + $username + \"' AND password = '\" + $password + \"'",
          "message": "Avoid constructing SQL queries through string concatenation. Use parameterized queries instead."
        },
        {
          "pattern": "cursor.execute(\"SELECT * FROM users WHERE username = '\" + username + \"' AND password = '\" + password + \"'\")",
          "message": "Avoid constructing SQL queries through string concatenation. Use parameterized queries instead."
        }
      ],
      "fixes": [
        {
          "pattern": "SELECT * FROM users WHERE username = '\" + $username + \"' AND password = '\" + $password + \"'",
          "replacement": "SELECT * FROM users WHERE username = ? AND password = ?",
          "language": "php"
        },
        {
          "pattern": "cursor.execute(\"SELECT * FROM users WHERE username = '\" + username + \"' AND password = '\" + password + \"'\")",
          "replacement": "cursor.execute(\"SELECT * FROM users WHERE username = ? AND password = ?\", (username, password))",
          "language": "python"
        }
      ]
    },
    {
      "id": "input-validation",
      "description": "Validate and sanitize all user inputs to ensure they conform to expected formats and prevent malicious data from being processed.",
      "severity": "warning",
      "patterns": [
        {
          "pattern": "$_POST['username']",
          "message": "Ensure that user input is validated and sanitized before use."
        },
        {
          "pattern": "request.form['username']",
          "message": "Ensure that user input is validated and sanitized before use."
        }
      ],
      "fixes": [
        {
          "pattern": "$_POST['username']",
          "replacement": "filter_var($_POST['username'], FILTER_SANITIZE_STRING)",
          "language": "php"
        },
        {
          "pattern": "request.form['username']",
          "replacement": "re.sub(r'[^a-zA-Z0-9]', '', request.form['username'])",
          "language": "python"
        }
      ]
    },
    {
      "id": "least-privilege",
      "description": "Ensure database connections use accounts with the minimum necessary privileges to perform their tasks, reducing the impact of potential SQL injection attacks.",
      "severity": "warning",
      "patterns": [
        {
          "pattern": "GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'",
          "message": "Avoid granting all privileges to database users. Assign only necessary permissions."
        }
      ],
      "fixes": [
        {
          "pattern": "GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'",
          "replacement": "GRANT SELECT, INSERT, UPDATE ON database.* TO 'user'@'localhost'",
          "language": "sql"
        }
      ]
    }
  ]
}