Basic

2. How do you sanitize user input in PHP to prevent SQL injection?

Overview

Sanitizing user input in PHP is crucial for preventing SQL injection, a common security vulnerability that allows attackers to execute malicious SQL code on a database. This technique is essential for developers to protect their applications from unauthorized data access or manipulation.

Key Concepts

  1. Prepared Statements
  2. Input Validation
  3. Escaping Strings

Common Interview Questions

Basic Level

  1. What is SQL Injection and why is it dangerous?
  2. How does PHP prevent SQL injection?

Intermediate Level

  1. What is the difference between escaping strings and using prepared statements in PHP?

Advanced Level

  1. How would you implement a secure input handling function in PHP?

Detailed Answers

1. What is SQL Injection and why is it dangerous?

Answer: SQL Injection is a code injection technique that might allow an attacker to execute malicious SQL statements to control a web application's database server, thereby accessing, modifying, or deleting unauthorized data. This can lead to data breaches, loss of data integrity, and unauthorized access to sensitive information.

Key Points:
- Allows attackers to manipulate SQL queries.
- Can lead to unauthorized access and data breaches.
- Prevention is essential for application security.

Example:

// An example of SQL injection vulnerability
$userInput = "'; DROP TABLE users; --";
$query = "SELECT * FROM users WHERE username = '$userInput'";
// This would result in the execution of a malicious query.

2. How does PHP prevent SQL injection?

Answer: PHP can prevent SQL injection primarily through the use of prepared statements with parameterized queries. Prepared statements ensure that an attacker cannot inject malicious SQL code because the input data is treated as a parameter rather than part of the SQL command.

Key Points:
- Prepared statements separate SQL logic from data.
- Parameterized queries are not executed directly, preventing injection.
- PHP's PDO (PHP Data Objects) extension provides support for prepared statements.

Example:

// Using PDO and prepared statements to prevent SQL injection
$pdo = new PDO('mysql:host=example.com;dbname=database', 'username', 'password');
$statement = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$statement->execute(['username' => $userInput]);
$results = $statement->fetchAll();

3. What is the difference between escaping strings and using prepared statements in PHP?

Answer: Escaping strings involves adding escape characters to potentially hazardous characters in a string, whereas prepared statements use parameterized queries that treat user input as parameters, not part of the SQL query. Prepared statements are generally considered more secure and efficient than escaping strings because they separate the data from the code, making it harder for attackers to inject malicious SQL.

Key Points:
- Escaping strings is less secure than prepared statements.
- Prepared statements provide a clear separation of SQL code and data.
- Parameterized queries are easier to read and maintain.

Example:

// Escaping string example
$userInput = mysqli_real_escape_string($connection, $userInput);
$query = "SELECT * FROM users WHERE username = '$userInput'";

// Prepared statement example
$statement = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$statement->execute(['username' => $userInput]);

4. How would you implement a secure input handling function in PHP?

Answer: Implementing a secure input handling function in PHP involves validating and sanitizing user inputs to ensure they meet the expected format and are free from potentially harmful data. This can be achieved by using a combination of prepared statements for database queries, regular expressions for input validation, and built-in PHP functions for sanitizing data.

Key Points:
- Validate inputs to ensure they meet the required format.
- Sanitize inputs to remove or encode potentially dangerous characters.
- Use prepared statements for database interactions.

Example:

function secureInput($input, $pdo) {
    // Sanitize the input
    $sanitizedInput = filter_var($input, FILTER_SANITIZE_STRING);

    // Prepare a query using the sanitized input
    $statement = $pdo->prepare("SELECT * FROM users WHERE email = :email");
    $statement->execute(['email' => $sanitizedInput]);

    return $statement->fetchAll();
}

This function sanitizes the input to remove potentially harmful characters, then uses a prepared statement to safely query the database, effectively preventing SQL injection.

logo