PHP Database Interaction & SQL Injection Prevention

Core Topics Covered

1. Database Basics in PHP

   • Connecting to MySQL/SQLite
   • Executing SQL queries (SELECT, INSERT, UPDATE, DELETE)
   • Fetching and displaying results

2. PHP Arrays for Database Work

   • Associative arrays for structured data
   • Multi-dimensional arrays for nested queries

3. SQL Injection: Attack & Prevention

   • How SQL injection exploits user input
   • Prepared statements vs. direct queries
   • Escaping user input

4. File Handling for Data Storage

   • Storing user data in .txt files (alternative to databases)
   • Reading/writing files in PHP

5. Real-World Example: Daraz Order Processing

   • How PHP + MySQL handles product inventory and orders

1. Connecting PHP to Databases

PHP interacts with databases using MySQLi (MySQL Improved) or PDO (PHP Data Objects). Below is a step-by-step connection and query example.

Step 1: Database Connection

sequenceDiagram
    participant PHP
    participant MySQL
    PHP->>MySQL: mysqli_connect('localhost', 'user', 'password', 'database')
    MySQL-->>PHP: Connection object

<?php
$conn = mysqli_connect("localhost", "root", "", "webapp_db");
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Step 2: Executing Queries

// Insert data
$sql = "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')";
mysqli_query($conn, $sql);

// Fetch data
$result = mysqli_query($conn, "SELECT * FROM users");
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}

Visual: Database Table Structure

Key Takeaways:

• Use mysqli_connect() for MySQL connections.
• Always check for errors (mysqli_error()).
• Fetch results with mysqli_fetch_assoc() for associative arrays.

2. PHP Arrays for Database Work

Arrays store data in a structured way, useful for handling database records.

Associative Arrays for Database Rows

$user = [
    "id" => 1,
    "name" => "John",
    "email" => "john@example.com"
];

Multi-Dimensional Arrays for Multiple Rows

$users = [
    ["id" => 1, "name" => "John"],
    ["id" => 2, "name" => "Alice"]
];

Visual: Associative Array vs. Indexed Array

Example: Displaying Users in a Table

<table border="1">
    <tr><th>ID</th><th>Name</th></tr>
    <?php foreach ($users as $user): ?>
        <tr>
            <td><?php echo $user['id']; ?></td>
            <td><?php echo $user['name']; ?></td>
        </tr>
    <?php endforeach; ?>
</table>

3. SQL Injection: Attack & Prevention

SQL injection occurs when malicious SQL code is inserted via user input.

Example of SQL Injection Vulnerability

// UNSAFE: Directly inserting user input
$user_id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $user_id";
$result = mysqli_query($conn, $query);

Attack Scenario

If user_id = 1; DROP TABLE users; --, the query becomes:

SELECT * FROM users WHERE id = 1; DROP TABLE users; --

This deletes the entire users table!

Solution: Prepared Statements

$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();

Visual: SQL Injection Attack Flow

sequenceDiagram
    participant Hacker
    participant WebApp
    participant Database
    Hacker->>WebApp: Input: 1; DROP TABLE users; --
    WebApp->>Database: SELECT * FROM users WHERE id = 1; DROP TABLE users; --
    Database-->>WebApp: Error (table deleted)

Key Takeaways:

• Always use prepared statements (mysqli_prepare() or PDO).
• Never concatenate user input directly into SQL.
• Escape input with mysqli_real_escape_string() (less secure than prepared statements).

4. File Handling for Data Storage

When databases aren’t available, PHP can store data in .txt files.

Writing to a File

$file = fopen("Person.txt", "a"); // "a" = append mode
fwrite($file, "John,25,M\n");
fclose($file);

Reading from a File

$file = fopen("Person.txt", "r");
while (!feof($file)) {
    $line = fgets($file);
    echo $line . "<br>";
}
fclose($file);

Visual: File Handling Workflow

sequenceDiagram
    participant PHP
    participant File
    PHP->>File: fopen("Person.txt", "a")
    PHP->>File: fwrite("John,25,M\n")
    PHP->>File: fclose()

Example: Storing User Data

$name = $_POST['name'];
$age = $_POST['age'];
$gender = $_POST['gender'];
$file = fopen("Person.txt", "a");
fwrite($file, "$name,$age,$gender\n");
fclose($file);

5. Real-World Example: Daraz Order Processing

Daraz uses PHP + MySQL to manage:

• Product inventory (stored in products table).
• User orders (stored in orders table).
• Payment processing (linked to payments table).

Sample Query: Fetching Orders for a User

$user_id = 1001;
$query = "SELECT * FROM orders WHERE user_id = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "Order ID: " . $row['order_id'] . "<br>";
}

Visual: Daraz Database Schema

In the Real World

1. eSewa Transaction Processing

   • Uses PHP + MySQL to store transactions securely.
   • Idea: Prepared statements prevent fraudulent SQL queries altering transaction records.

2. Pathao Ride Booking

   • PHP fetches driver availability from a database.
   • Idea: Multi-dimensional arrays store driver schedules and passenger requests.

3. NEPSE Stock Market

   • PHP scripts fetch real-time stock data from MySQL.
   • Idea: SQL injection prevention ensures no malicious queries manipulate stock prices.

Worked Example: Daraz Order Queue

Scenario: Daraz needs to process orders in FIFO (First-In-First-Out) order.

Solution: Queue Using PHP Arrays

$orderQueue = []; // Initialize empty queue

// Enqueue (add) orders
function enqueue(&$queue, $order) {
    $queue[] = $order;
}

// Dequeue (remove) orders
function dequeue(&$queue) {
    if (empty($queue)) return null;
    return array_shift($queue);
}

// Example usage
enqueue($orderQueue, ["id" => 1001, "user" => "Alice"]);
enqueue($orderQueue, ["id" => 1002, "user" => "Bob"]);
echo "Next order: " . dequeue($orderQueue)['user']; // Output: Alice

Visual: Queue Operations

Comparison Table: Database vs. File Storage

Exam Tip

1. For PHP Database Queries:

   • Always use prepared statements to prevent SQL injection.
   • Practice fetching and displaying results with mysqli_fetch_assoc().

2. For File Handling:

   • Know fopen(), fwrite(), fread(), and fclose().
   • Example: Store form data in a .txt file and display it.

3. For SQL Injection:

   • Explain how it works (malicious input altering queries).
   • Compare prepared statements vs. escaping (prepared is better).

4. For Arrays:

   • Know the difference between indexed and associative arrays.
   • Example: Display a table from an associative array.

5. Real-World Tie-In:

   • Link PHP database work to eSewa transactions or Daraz orders.
   • Example: "How would you fetch a user’s order history from a database?"

Final Note: Focus on practical coding (e.g., connecting to MySQL, writing to files) and security (SQL injection prevention). Use prepared statements in every database query!