Basic SQL Injection and Mitigation with Example

SQL injection is a type of cyber attack that allows attackers to execute malicious SQL statements on a database. These statements can be used to manipulate data, retrieve sensitive information, or even delete entire databases. It is one of the most common and dangerous types of web vulnerabilities, and it can affect any website or web application that uses a SQL database.

In this article, we will cover the basics of SQL injection, how it works, and how to prevent it. We will also provide an example of a basic SQL injection attack and show how to mitigate it.

What is SQL Injection?

SQL, or Structured Query Language, is a programming language used to manage and manipulate data stored in relational databases. It is the standard language for interacting with databases, and it is used by millions of websites and applications around the world.

SQL injection is a type of cyber attack that exploits vulnerabilities in SQL-based applications. It allows attackers to insert malicious code into an application's SQL statements, which can then be executed by the database. This can allow attackers to gain unauthorized access to sensitive data, modify or delete data, and even gain control of the entire database.

How Does SQL Injection Work?

SQL injection attacks work by taking advantage of poorly-designed or poorly-implemented SQL code. When an application receives user input, it is often incorporated directly into an SQL query without proper validation or sanitization. This can allow attackers to insert malicious code into the query, which can then be executed by the database.

For example, consider a simple login form that asks for a username and password. The application might generate an SQL query like this to verify the user's credentials &miuns;

SELECT * FROM users WHERE username='$username' AND password='$password';

In this case, the $username and $password variables are replaced with the user's input. If a user enters their own username and password, the query will work as intended. However, if an attacker enters malicious input, they can manipulate the query to do things like retrieve sensitive data or even delete entire tables.

For example, an attacker might enter the following as their password −

' OR 1=1; -

SELECT * FROM users WHERE username='$username' AND password='' OR 1=1;
--';

The OR 1=1 statement will always evaluate to true, so the query will return all rows from the users table. The -- at the end is an SQL comment, which tells the database to ignore everything after it. This allows the attacker to bypass the rest of the query and gain access to the entire table.

How to Prevent SQL Injection?

Preventing SQL injection attacks requires a combination of good design practices and proper input validation. Here are a few steps you can take to protect your application −

• Use parameterized queries − One of the easiest and most effective ways to prevent SQL injection is to use parameterized queries. This involves separating the SQL code from the user input and passing the input as a separate parameter. This ensures that the input is treated as a value, rather than part of the SQL code, and makes it much harder for attackers to inject malicious code.

• Validate and sanitize user input − Another important step is to validate and sanitize all user input. This involves checking the input for any characters or patterns that might indicate an attempt to inject malicious code. You should also limit the type and length of input that users can enter.

• Use prepared statements − Prepared statements are a type of parameterized query that can be used to protect against SQL injection. They allow you to create a template for an SQL statement, and then pass in the parameters at a later time. This can help to prevent SQL injection because the parameters are not parsed until they are passed to the prepared statement, which means that any malicious code will be treated as a value, rather than part of the SQL code.

• Use stored procedures − Stored procedures are pre-compiled SQL statements that are stored in the database. They can be used to execute complex queries and perform various tasks, such as inserting, updating, or deleting data. Because stored procedures are compiled in advance, they can be faster and more efficient than regular SQL statements. They can also help to prevent SQL injection, because the input is passed to the stored procedure as a separate parameter, rather than being incorporated directly into the SQL code.

• Enforce strong passwords − One of the most common ways for attackers to gain access to a database is by guessing or cracking weak passwords. To prevent this, you should enforce strong password policies, including using long passwords that are difficult to guess or crack. You should also consider using two-factor authentication or other security measures to protect sensitive accounts.

Example: Basic SQL Injection Attack and Mitigation

To illustrate the basics of SQL injection, let's walk through an example of a simple login form that is vulnerable to injection attacks. We will then show how to mitigate the vulnerability using parameterized queries.

First, let's create a simple table in a MySQL database to hold our users −

CREATE TABLE users (
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(50) NOT NULL,
   password VARCHAR(50) NOT NULL
);

Next, let's create a login form with a simple HTML form −

<form action="login.php" method="post">
   <label for="username">Username:</label>
   <input type="text" name="username" id="username">
   <br>
   <label for="password">Password:</label>
   <input type="password" name="password" id="password">
   <br>
   <input type="submit" value="Log In">
</form>

Example

The form sends a POST request to login.php with the username and password fields. We can then use PHP to handle the request and check the user's credentials against the database −

<?php
   // Connect to the database
   $db = new mysqli("localhost", "username", "password", "database");

   // Check if the form has been submitted
   if (isset($_POST["username"]) && isset($_POST["password"])) {

      // Get the username and password from the form
      $username = $_POST["username"];
      $password = $_POST["password"];
      
      // Create the SQL query
      $query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
     
      // Execute the query
      $result = $db->query($query);
    
      // Check if the query returned any rows
      if ($result->num_rows > 0) {
    
         // The username and password are correct
         echo "Logged in successfully!";
      } else {
         
         // The username and password are incorrect
         echo "Invalid username or password";
      }
   }
?>

This code creates an SQL query using the `username` and `password` fields from the form, and then executes the query using the `query()` method of the 'mysqli' object. If the query returns any rows, it means that the username and password are correct, and the user is logged in.

However, this code is vulnerable to SQL injection attacks. An attacker can enter malicious input into the form, which will be incorporated directly into the SQL query. For example, if an attacker enters the following as their username −

admin' --

The resulting SQL query will look like this −

SELECT * FROM users WHERE username='admin' --' AND password='$password';

The `--` at the end is an SQL comment, which tells the database to ignore everything after it. This allows the attacker to bypass the rest of the query and gain access to the `admin` user, even if they don't know the password.

Example

To mitigate this vulnerability, we can use a parameterized query instead of building the SQL query directly from the user input. Here's how the code would look using a parameterized query −

<?php
   // Connect to the database
   $db = new mysqli("localhost", "username", "password", "database");

   // Check if the form has been submitted
   if (isset($_POST["username"]) && isset($_POST["password"])) {

      // Get the username and password from the form
      $username = $_POST["username"];
      $password = $_POST["password"];

      // Create a prepared statement
      $stmt = $db->prepare("SELECT * FROM users WHERE username=? AND password=?");

      // Bind the parameters
      $stmt->bind_param("ss", $username, $password);

      // Execute the statement
      $stmt->execute();

      // Get the result
      $result = $stmt->get_result();

      // Check if the query returned any rows
      if ($result->num_rows > 0) {

         // The username and password are correct
         echo "Logged in successfully!";
      } else {

         // The username and password are incorrect
         echo "Invalid username or password";
      }
   }
?>

In this version of the code, we use a prepared statement to create a template for the SQL query. We then bind the username and password variables to the prepared statement as parameters, using the bind_param() method. This ensures that the input is treated as a value, rather than part of the SQL code, which makes it much harder for attackers to inject malicious code.

Conclusion

SQL injection is a serious and widespread security vulnerability that can compromise the integrity and confidentiality of your database. To protect your applications and your data, it is important to follow best practices for designing and implementing your SQL code, and to use proper input validation and sanitization techniques. By using parameterized queries and other prevention measures, you can help to prevent SQL injection attacks and keep your applications and data safe.