1. Introduction

Goal of the Tutorial

This tutorial aims to provide an understanding of SQL injection attacks and how to prevent these attacks in your web application.

Learning Objectives

• Understand what SQL injection attacks are.
• Learn how to prevent SQL injection attacks.
• Write secure code that is resistant to SQL injection attacks.

Prerequisites

• A basic understanding of SQL and web development.
• Familiarity with a programming language that connects to an SQL database. Examples include PHP, Java, Python, etc.

2. Step-by-Step Guide

What is SQL Injection?

SQL Injection is a type of security vulnerability that allows an attacker to manipulate the SQL queries run by your web application. This could lead to unauthorized access to sensitive data, data corruption, or even data loss.

How to Prevent SQL Injection Attacks?

The best way to prevent SQL injection attacks is to avoid using user input directly in SQL queries. Instead, you should use parameterized queries or prepared statements.

Using Parameterized Queries

Parameterized queries force the developers to first define all the SQL code, and then pass in each parameter to the query later. This makes it almost impossible for an attacker to inject malicious SQL code.

Using Prepared Statements

Prepared statements work similarly to parameterized queries. The SQL statement is defined first, and then the parameters are bound to it. The database understands that these are parameters and treats them as such, preventing any attempts at SQL injection.

3. Code Examples

Example 1: Parameterized Query in PHP

$pdo = new PDO('mysql:dbname=db;host=127.0.0.1', 'username', 'password');
$id = $_GET['id']; // user input
$query = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$query->execute([':id' => $id]);

• Here, we're using the PDO extension in PHP to connect to a MySQL database.
• We get the user input from $_GET['id'].
• Instead of including $id directly in our SQL, we're using a placeholder :id.
• We then bind the $id variable to the :id placeholder using execute([':id' => $id]).
• This ensures that $id is treated as a string literal and not part of the SQL command.

Example 2: Prepared Statement in Java

String custname = request.getParameter("customerName"); // user input
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM Customers WHERE CustomerName = ?");
stmt.setString(1, custname);
ResultSet rs = stmt.executeQuery();

• Here, we're getting user input through request.getParameter("customerName").
• We're using a PreparedStatement to define our SQL query, with ? as a placeholder for the user input.
• We then bind the user input to the SQL query using stmt.setString(1, custname).
• Again, this ensures the user input is treated as a string literal and not part of the SQL command.

4. Summary

Key Points Covered

• SQL injection is a serious security vulnerability that can be exploited to manipulate your SQL queries.
• The best way to prevent SQL injection is to never include user input directly in your SQL queries.
• Use parameterized queries or prepared statements to ensure user input is treated as a string literal and not part of the SQL command.

Next Steps for Learning

• Learn about other types of web application security vulnerabilities and how to prevent them.
• Practice writing secure code and testing it for vulnerabilities.

Additional Resources

• OWASP Top 10
• SQL Injection Cheat Sheet

5. Practice Exercises

Exercise 1:

Write a PHP code snippet to get the 'username' and 'password' from a POST request and verify them using a parameterized query.

Exercise 2:

Write a Java code snippet to insert a new 'product' into the 'products' table using a prepared statement.

Exercise 3:

Write a Python code snippet to update the 'email' of a user in the 'users' table using a parameterized query. Assume the user ID is provided as user input.

Solutions and Explanations

Solution for Exercise 1:

$pdo = new PDO('mysql:dbname=db;host=127.0.0.1', 'username', 'password');
$username = $_POST['username']; // user input
$password = $_POST['password']; // user input
$query = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$query->execute([':username' => $username, ':password' => $password]);

This PHP code snippet first connects to the database using PDO. It gets 'username' and 'password' from a POST request and uses a parameterized query to verify them.

Solution for Exercise 2:

String product = request.getParameter("productName"); // user input
PreparedStatement stmt = connection.prepareStatement("INSERT INTO Products (ProductName) VALUES (?)");
stmt.setString(1, product);
stmt.executeUpdate();

This Java code snippet gets 'productName' from a request and uses a prepared statement to insert a new product into the 'products' table.

Solution for Exercise 3:

import psycopg2
conn = psycopg2.connect(database="testdb", user="postgres", password="pass", host="127.0.0.1", port="5432")
cur = conn.cursor()
user_id = input("Enter user id: ") # user input
new_email = input("Enter new email: ") # user input
cur.execute("UPDATE users SET email = %s WHERE id = %s", (new_email, user_id))
conn.commit()

This Python code snippet connects to the database using psycopg2. It gets user id and new email from user input and uses a parameterized query to update the 'email' of a user in the 'users' table.