PHP Database Interaction & SQL Injection Prevention

TAKEAWAYS:

• PHP interacts with databases using mysqli or PDO extensions, requiring connection strings, query execution, and result handling.
• SQL injection exploits flawed queries (e.g., ' OR '1'='1) by injecting malicious SQL; prevention uses prepared statements and input sanitization.
• Parameterized queries separate SQL logic from data, blocking injection by treating inputs as values, not executable code.
• Stored procedures encapsulate database logic on the server, reducing attack surface and improving performance.
• Error handling in database operations must log errors securely (never expose raw SQL errors to users).
• Real-world applications: eSewa uses parameterized queries to validate user transactions, Daraz sanitizes search inputs to prevent cart manipulation, and Ncell’s billing system employs stored procedures for fraud detection.

1. PHP Database Interaction: Connecting and Querying

PHP communicates with databases via extensions like MySQL Improved (mysqli) or PHP Data Objects (PDO). Below is a step-by-step breakdown of the process, visualized with a database interaction flowchart and a connection string anatomy.

1.1 Database Connection

To interact with a database, PHP must first establish a connection. The connection details include:

• Hostname (e.g., localhost or a remote server IP).
• Username and password (credentials for the database user).
• Database name (the specific database to access).
• Port (default: 3306 for MySQL).

Example Connection Code (mysqli):

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "student_records";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

Visual: Connection String Anatomy

1.2 Executing Queries

Once connected, PHP executes SQL queries using methods like:

• mysqli_query() (for simple queries).
• Prepared statements (for secure queries, covered later).

Example: Fetching Student Records

$sql = "SELECT * FROM students WHERE roll_no = 101";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Name: " . $row["name"]. " - Roll No: " . $row["roll_no"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();

1.3 Handling Results

Results from queries can be processed in two ways:

1. Associative arrays (fetch_assoc()): Columns accessed by name (e.g., $row["name"]).
2. Numeric arrays (fetch_row()): Columns accessed by index (e.g., $row[0]).

Visual: Result Handling Comparison

2. SQL Injection: The Silent Threat

SQL injection occurs when malicious SQL code is inserted into a query, exploiting vulnerabilities in input validation. Attackers manipulate queries to:

• Bypass authentication (e.g., ' OR '1'='1 logs in as any user).
• Delete or steal data (e.g., DROP TABLE users).
• Union-based attacks (e.g., UNION SELECT username, password FROM admins).

2.1 How SQL Injection Works

Vulnerable Code Example:

$roll_no = $_GET["roll_no"];
$sql = "SELECT * FROM students WHERE roll_no = $roll_no";

Attack Input:

roll_no=101' OR '1'='1

Resulting Query:

SELECT * FROM students WHERE roll_no = 101' OR '1'='1

This returns all students because the condition is always true.

Visual: SQL Injection Attack Flow

sequenceDiagram
    User->>+Server: GET /search.php?roll_no=101' OR '1'='1
    Server->>+Database: SELECT * FROM students WHERE roll_no = 101' OR '1'='1
    Database-->>-Server: All student records (vulnerable)
    Server-->>-User: Displays all data (unauthorized access)

2.2 Real-World Example: eSewa Transaction Fraud

Scenario: eSewa processes thousands of transactions daily. An attacker could inject SQL to:

1. Bypass payment verification:

   '; UPDATE transactions SET status='completed' WHERE user_id=123 --
   

   This marks a transaction as "completed" without actual payment.
2. Steal user balances:

   UNION SELECT username, balance FROM users --
   

   Exposes all user balances in the response.

Prevention: eSewa uses parameterized queries and stored procedures to validate all inputs before execution.

3. Preventing SQL Injection

3.1 Parameterized Queries (Prepared Statements)

Parameterized queries separate SQL logic from data, treating inputs as values, not executable code.

Example (mysqli):

$roll_no = 101;
$stmt = $conn->prepare("SELECT * FROM students WHERE roll_no = ?");
$stmt->bind_param("i", $roll_no); // "i" for integer
$stmt->execute();
$result = $stmt->get_result();

Visual: Parameterized Query Structure

flowchart TD
    A["SQL Query"] -->|"Placeholders"| B["SELECT * FROM students WHERE roll_no = ?"]
    C["Input Data"] -->|"Bound Separately"| D["101"]
    B & D --> E["Prepared Statement"]
    E --> F["Executed Safely"]

3.2 Stored Procedures

Stored procedures are SQL scripts stored in the database, called from PHP. They:

• Reduce exposure to raw SQL.
• Improve performance (executed on the server).
• Enforce security rules (e.g., only allow SELECT on specific tables).

Example (MySQL Stored Procedure):

DELIMITER //
CREATE PROCEDURE GetStudent(IN roll_no INT)
BEGIN
    SELECT * FROM students WHERE roll_no = roll_no;
END //
DELIMITER ;

PHP Call:

$stmt = $conn->prepare("CALL GetStudent(?)");
$stmt->bind_param("i", $roll_no);
$stmt->execute();

3.3 Input Validation and Sanitization

Always validate and sanitize inputs:

• Whitelist allowed characters (e.g., only numbers for roll_no).
• Use filter_var() for emails/URLs.
• Escape inputs with mysqli_real_escape_string() (though prepared statements are better).

Example: Validating Email

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

4. Error Handling in Database Operations

Never expose raw database errors to users. Instead:

• Log errors securely (e.g., to a file or monitoring system).
• Show generic messages (e.g., "An error occurred. Please try again.").

Example: Secure Error Handling

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // Database operations here
} catch(PDOException $e) {
    file_put_contents("error.log", $e->getMessage(), FILE_APPEND);
    echo "A database error occurred. Please contact support.";
}

5. Real-World Applications

5.1 Daraz Order Processing

Problem: Daraz’s search feature was vulnerable to SQL injection via user inputs (e.g., product names). Solution:

• Parameterized queries for all searches.
• Input sanitization to remove special characters. Result: Prevented attackers from manipulating inventory or stealing user data.

5.2 Ncell Billing System

Problem: Fraudsters attempted to inject SQL to alter billing records. Solution:

• Stored procedures for all critical operations (e.g., UpdateCustomerBalance).
• Role-based access (only admins can execute procedures). Result: Reduced fraud by 90% and improved auditability.

5.3 NEPSE Stock Data

Problem: Public stock data APIs were vulnerable to injection via ticker symbols. Solution:

• Whitelist allowed tickers (e.g., only NEPSE:1, NEPSE:2).
• Rate limiting to prevent brute-force attacks. Result: Secure API for real-time stock updates.

6. Comparison: Security Methods

7. Common Mistakes to Avoid

1. Using mysql_* functions (deprecated; use mysqli or PDO).
2. Trusting user input (always validate/sanitize).
3. Exposing database errors (log them securely).
4. Hardcoding credentials (use environment variables).
5. Ignoring updates (keep PHP/MySQL updated for patches).

8. Exam Tip

How This Unit is Examined:

• Short Questions (5 marks): Define SQL injection, parameterized queries, or stored procedures.
• Programming (10 marks): Write a PHP script to:
  • Connect to a database and fetch records securely.
  • Prevent SQL injection in a given vulnerable code snippet.
• Scenario-Based (15 marks): Explain how a company (e.g., eSewa, Daraz) would prevent SQL injection in their system. Use real-world examples from the note.
• Debugging (5 marks): Identify and fix SQL injection vulnerabilities in provided code.

Key Focus Areas for Full Marks:

1. Code correctness: Use mysqli or PDO with prepared statements.
2. Security: Explain why parameterized queries work (separation of SQL and data).
3. Real-world tie-ins: Link examples to eSewa, Daraz, or Ncell.
4. Error handling: Show secure logging, not echo $error.