9.7 A PHP + MySQL Application with Prepared Statements

By Ravindra Bagale Last updated: 27 September 2026

Aata stack cha khara test – ek chhota app jo database madhun data vachto aani form ne navin data takto. Aadhi database aani application user banvuya (MySQL Part 5 madhe detail madhe shiknar aahot):

sudo mysql <<'SQL'
CREATE DATABASE IF NOT EXISTS appdb;
CREATE USER IF NOT EXISTS 'appuser'@'localhost' IDENTIFIED BY 'App@Pass123';
CREATE USER IF NOT EXISTS 'appuser'@'127.0.0.1' IDENTIFIED BY 'App@Pass123';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'127.0.0.1';
USE appdb;
CREATE TABLE IF NOT EXISTS students (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  city VARCHAR(50)  NOT NULL
);
INSERT INTO students (name, city) VALUES ('Shahrukh','Pune'), ('Zoya','Nagpur'), ('Amir','Nashik');
SQL

Now the application files:

sudo tee /var/www/phpapp/db.php > /dev/null <<'EOF'
<?php
$dsn  = "mysql:host=127.0.0.1;dbname=appdb;charset=utf8mb4";
$user = "appuser";
$pass = "App@Pass123";
try {
    $pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
} catch (PDOException $e) {
    http_response_code(500);
    die("Database connection failed: " . htmlspecialchars($e->getMessage()));
}
EOF

sudo tee /var/www/phpapp/index.php > /dev/null <<'EOF'
<?php
require __DIR__ . "/db.php";
if ($_SERVER["REQUEST_METHOD"] === "POST" && !empty($_POST["name"]) && !empty($_POST["city"])) {
    $stmt = $pdo->prepare("INSERT INTO students (name, city) VALUES (?, ?)");   // prepared = safe from SQL injection
    $stmt->execute([$_POST["name"], $_POST["city"]]);
    header("Location: /");
    exit;
}
$rows = $pdo->query("SELECT id, name, city FROM students ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>PHP + MySQL on EC2</title></head>
<body style="font-family:Arial;max-width:700px;margin:30px auto">
<h1>Students (PHP + MySQL)</h1>
<table border="1" cellpadding="6">
<tr><th>ID</th><th>Name</th><th>City</th></tr>
<?php foreach ($rows as $r): ?>
<tr><td><?= $r["id"] ?></td><td><?= htmlspecialchars($r["name"]) ?></td><td><?= htmlspecialchars($r["city"]) ?></td></tr>
<?php endforeach; ?>
</table>
<h3>Add student</h3>
<form method="post">
  <input name="name" placeholder="Name" required>
  <input name="city" placeholder="City" required>
  <button type="submit">Add</button>
</form>
<p>Served by PHP <?= phpversion() ?> on <?= gethostname() ?></p>
</body></html>
EOF

sudo chmod 640 /var/www/phpapp/db.php
# let the PHP process user read db.php:  apache (AL2023/CentOS php-fpm & httpd) or www-data (Ubuntu)
sudo chown root:apache /var/www/phpapp/db.php      # Ubuntu: sudo chown root:www-data /var/www/phpapp/db.php
sudo restorecon -Rv /var/www/phpapp 2>/dev/null   # CentOS only (harmless elsewhere)
curl -s http://localhost/ -H "Host: $(curl -s https://checkip.amazonaws.com)" | head -20

Quick PHP test page

echo "<?php phpinfo();" | sudo tee /var/www/phpapp/info.php shows full PHP configuration. Delete it after testing — it reveals sensitive details.

PHP file downloads instead of running?

If the browser downloads index.php or shows raw PHP code, the web server is not passing .php files to PHP: PHP-FPM is not running, the location ~ \.php$ block is missing, or the socket path is wrong. Check sudo service php-fpm status (Ubuntu: php8.3-fpm) and the Nginx error log.

Why this matters for security

Look at the two security lines in this app: $pdo->prepare(... ?, ?) keeps user input separate from SQL, which prevents SQL injection, and htmlspecialchars() encodes output, which prevents XSS. In Part 11 you will attack DVWA pages that forgot exactly these two lines.

Ravindra Bagale's Tip

Khup students "SELECT ... WHERE name='" . $_POST['name'] . "'" asa string jodun query lihitat karan te soppa vatta. Hich SQL injection chi janmabhoomi aahe! Nehmi prepared statement (? placeholders) vapra. Aani password App@Pass123 practice sathi aahe – real server var majboot password theva.

Lab

Deploy the app on your LEMP server, add three students from the form (Ravina from Kolhapur, Salman from Solapur, Raja from Sambhaji Nagar), and verify with SELECT * FROM appdb.students;.