Preventing SQL injection is crucial for securing a PHP application that interacts with a database. SQL injection occurs when an attacker manipulates input to execute malicious SQL commands. The best practices to prevent SQL injection involve properly validating and sanitizing user inputs and using secure database interaction methods.

Here are key techniques to prevent SQL injection in PHP:

---

1. Use Prepared Statements with Parameterized Queries

Prepared statements separate SQL code from user inputs, making it impossible for inputs to alter the structure of the query.

Using MySQLi:

// Create a connection
$conn = new mysqli("localhost", "username", "password", "database");

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

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

// Set values and execute
$username = $_POST['username'];
$password = $_POST['password']; // Ideally, hash the password before comparing
$stmt->execute();

// Fetch the result
$result = $stmt->get_result();
if ($result->num_rows > 0) {
    echo "Login successful.";
} else {
    echo "Invalid username or password.";
}

$stmt->close();
$conn->close();

Using PDO:

// Create a connection
$pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare a statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");

// Bind parameters
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);

// Set values and execute
$username = $_POST['username'];
$password = $_POST['password']; // Ideally, hash the password before comparing
$stmt->execute();

// Fetch the result
if ($stmt->rowCount() > 0) {
    echo "Login successful.";
} else {
    echo "Invalid username or password.";
}

---

2. Validate and Sanitize User Input

Validation ensures input meets expected criteria, while sanitization removes harmful content.

Example: Validate Email

$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
if ($email === false) {
    die("Invalid email format");
}

Example: Sanitize String

$name = filter_var($_POST['name'], FILTER_SANITIZE_STRING);

---

3. Escape Input Data

If you must use dynamic queries (not recommended), escape special characters using mysqli_real_escape_string().

$conn = new mysqli("localhost", "username", "password", "database");

$username = $conn->real_escape_string($_POST['username']);
$password = $conn->real_escape_string($_POST['password']);

$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "Login successful.";
} else {
    echo "Invalid username or password.";
}

---

4. Use Stored Procedures

Stored procedures execute on the database server, reducing the risk of SQL injection.

// Create a connection
$conn = new mysqli("localhost", "username", "password", "database");

// Call a stored procedure
$stmt = $conn->prepare("CALL GetUser(?, ?)");
$stmt->bind_param("ss", $username, $password);

// Set values 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 username or password.";
}

$stmt->close();
$conn->close();

---

5. Limit User Permissions

Grant the database user only the minimum permissions necessary for the application.

• Avoid using root or highly privileged users.
• Use separate users for read-only and write operations.

---

6. Disable Multiple Statements

Disable multiple statements in your database connection to prevent executing multiple SQL commands in a single query.

PDO:

$pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password', [
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_MULTI_STATEMENTS => false,
]);

---

7. Use an ORM (Object-Relational Mapping) Library

Using an ORM like Eloquent (Laravel) or Doctrine can abstract and simplify database interactions, automatically protecting against SQL injection.

Example with Eloquent:

$user = User::where('username', $username)->where('password', $password)->first();
if ($user) {
    echo "Login successful.";
} else {
    echo "Invalid username or password.";
}

---

Example of Vulnerable Code and Fixed Code

Vulnerable Code:

$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $conn->query($sql); // Vulnerable to SQL injection

if ($result->num_rows > 0) {
    echo "Login successful.";
}

Fixed Code:

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);

$username = $_POST['username'];
$password = $_POST['password'];
$stmt->execute();

$result = $stmt->get_result();
if ($result->num_rows > 0) {
    echo "Login successful.";
}

---

Conclusion

To prevent SQL injection:

• Always use prepared statements or stored procedures.
• Validate and sanitize user inputs.
• Avoid constructing SQL queries dynamically with user input.
• Grant minimal database permissions.
• Use modern frameworks or ORMs that inherently protect against SQL injection.

By adhering to these best practices, you can ensure your PHP application is secure from SQL injection attacks.