SQL injection is a common and potentially devastating security vulnerability that can affect web applications. It occurs when an attacker is able to manipulate the SQL queries executed by a database, often by injecting malicious code through input fields. The expression "%'OR'1'='1%" is a classic example of an SQL injection attack, where the injected code always evaluates to true, potentially allowing unauthorised access to the database.

In a Linux environment, securing your web applications against SQL injection is crucial. This article will provide practical examples and techniques to prevent SQL injection attacks, focusing on secure coding practices, using prepared statements, and employing web application firewalls.

Examples:

1. Using Prepared Statements with MySQL in PHP:

   Prepared statements are a robust way to prevent SQL injection. They separate SQL logic from data, ensuring that user input is treated as data and not executable code.

   <?php
   $servername = "localhost";
   $username = "username";
   $password = "password";
   $dbname = "database";
   
   // Create connection
   $conn = new mysqli($servername, $username, $password, $dbname);
   
   // Check connection
   if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
   }
   
   // Prepare and bind
   $stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
   $stmt->bind_param("ss", $username, $password);
   
   // Set parameters and execute
   $username = $_POST['username'];
   $password = $_POST['password'];
   $stmt->execute();
   
   $result = $stmt->get_result();
   if ($result->num_rows > 0) {
      echo "Login successful";
   } else {
      echo "Invalid credentials";
   }
   
   $stmt->close();
   $conn->close();
   ?>

2. Using ORM (Object-Relational Mapping) with Python and SQLAlchemy:

   ORMs like SQLAlchemy in Python also help mitigate SQL injection risks by abstracting SQL queries and allowing developers to interact with the database using Python objects.

   from sqlalchemy import create_engine, Column, Integer, String
   from sqlalchemy.ext.declarative import declarative_base
   from sqlalchemy.orm import sessionmaker
   
   Base = declarative_base()
   
   class User(Base):
      __tablename__ = 'users'
      id = Column(Integer, primary_key=True)
      username = Column(String)
      password = Column(String)
   
   engine = create_engine('mysql+pymysql://username:password@localhost/database')
   Session = sessionmaker(bind=engine)
   session = Session()
   
   def authenticate(username, password):
      user = session.query(User).filter_by(username=username, password=password).first()
      if user:
          return "Login successful"
      else:
          return "Invalid credentials"
   
   # Example usage
   username = input("Enter username: ")
   password = input("Enter password: ")
   print(authenticate(username, password))

3. Implementing a Web Application Firewall (WAF):

   A WAF can help protect your web applications by filtering and monitoring HTTP requests. ModSecurity is a popular open-source WAF for Linux environments.

   • Install ModSecurity:

     sudo apt-get update
     sudo apt-get install libapache2-mod-security2

   • Enable ModSecurity:

     sudo a2enmod security2

   • Configure ModSecurity:

     Edit the main configuration file /etc/modsecurity/modsecurity.conf to enable detection-only mode for initial testing:

     sudo nano /etc/modsecurity/modsecurity.conf

     Change the following line:

     SecRuleEngine DetectionOnly

     Restart Apache to apply changes:

     sudo systemctl restart apache2

   • Test ModSecurity:

     You can test ModSecurity by attempting a known SQL injection attack and checking the logs to see if it was detected.