SQL, PHP Integration and Web Security Best Practices

Posted on Feb 12, 2026 in Computers

SIDE A: Database, SQL & PHP Integration

1. SQL Fundamentals (Chapters 8 & 9)

• Data Definition Language (DDL) – Structure

  • Create DB: CREATE DATABASE publications; USE publications;

Create Table:

CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(128),
    password CHAR(255) -- Fixed length best for hashes
) ENGINE=MyISAM;

• Alter Table:

  • ALTER TABLE users ADD email VARCHAR(255);
  • ALTER TABLE users DROP email;
  • ALTER TABLE users MODIFY year SMALLINT;
  • ALTER TABLE classics RENAME TO pre1900;
  • SOC Note: DROP and ALTER in logs usually indicate an attack or insider threat, not normal user activity.

• Indexes:

  • ALTER TABLE classics ADD INDEX(author(20));
  • Purpose: Speeds up SELECT queries (searches).

• Data Types: VARCHAR (variable string), CHAR (fixed string), BLOB (binary/images), INT, DATE.

• Data Manipulation Language (DML) – Content

  • Insert: INSERT INTO classics(author, title) VALUES('Mark Twain', 'Tom Sawyer');

  • Select:

    • SELECT author, title FROM classics;
    • SELECT DISTINCT author ... (removes duplicates).
    • SELECT COUNT(*) ... (returns integer count of rows).
    • SELECT * FROM classics WHERE author = 'Mark Twain';

  • Update: UPDATE classics SET author = 'Samuel Clemens' WHERE author = 'Mark Twain';

    • Warning: Forgetting WHERE updates the entire table.

  • Delete: DELETE FROM classics WHERE title = 'Little Dorrit';

• Joins (Combining Tables)

  • Natural Join: SELECT * FROM customers NATURAL JOIN classics; (joins on columns with identical names).

  • Inner Join: SELECT * FROM customers JOIN classics ON customers.isbn = classics.isbn;

  • Aliases: SELECT * FROM customers AS c, classics AS cl WHERE c.isbn = cl.isbn;

• Database Design (ERD)

  • Normalization: Separating data into distinct tables to reduce redundancy.

  • Relationships:

    • 1:1 (Faculty ↔ Office)
    • 1:M (Team ↔ Players) – Most common.
    • M:N (Student ↔ Course) – Requires a “join table” (e.g., enrollments).

2. PHP & MySQL Integration (Chapter 10)

The Connection Object (mysqli)

// PHP
require_once 'login.php'; // Contains $hn, $un, $pw, $db

$conn = new mysqli($hn, $un, $pw, $db);
if ($conn->connect_error) die($conn->connect_error);

Query Execution

// PHP
$query = "SELECT * FROM classics";
$result = $conn->query($query);
if (!$result) die($conn->error); // Fatal error if query fails

• Fetching Data (The Loop)

  • fetch_assoc(): Returns array with string keys ($row['isbn']).

  • fetch_array(): Returns array with both numeric ($row[0]) and string keys.

  • num_rows: Property getting the count of results.

// PHP
$rows = $result->num_rows;
for ($j = 0; $j < $rows; ++$j) {
    $result->data_seek($j); // Move pointer to specific row
    $row = $result->fetch_assoc(); // Fetch row
    echo 'Author: ' . $row['author'] . '
';
}

$result->close(); // Free memory
$conn->close();   // Close connection

• Handling Form Input (POST)

  • Sanitization (basic): htmlentities() or $conn->real_escape_string().
  • Logic:

// PHP
if (isset($_POST['isbn'])) { // Check if form was submitted
    $isbn = $conn->real_escape_string($_POST['isbn']); // Prevent SQLi (basic)
    $title = $conn->real_escape_string($_POST['title']);
    $query = "INSERT INTO classics VALUES('$isbn', '$title')";
    $result = $conn->query($query);
}

• HTML Form Elements & PHP Handling

  • Text: <input type="text" name="user"> → $_POST['user']
  • Radio: <input type="radio" name="genre" value="SciFi"> → $_POST['genre']
  • Checkbox: <input type="checkbox" name="agree" value="yes"> → $_POST['agree']
  • Select: <select name="color"><option value="red">Red</option></select> → $_POST['color']
  • Hidden: <input type="hidden" name="id" value="123"> → $_POST['id']
    • SOC Note: Hidden fields are not secure. Users can edit them via “Inspect Element”.

SIDE B: Security, Authentication & JavaScript

3. Authentication & Sessions (Chapter 12)

• Concepts

  • Authentication (AuthN): Verifying identity (login). “Who are you?”
  • Authorization (AuthZ): Verifying access (roles). “What can you see?”
  • HTTP Auth: Basic browser popup. Insecure (Base64 encoded).
  • PHP Auth: Custom forms + sessions.

• Password Security

  • Salting: Adding random string to password before hashing. Defeats rainbow tables.
  • Hashing: One-way encryption.
  • Implementation:

// PHP
// Hashing (Registration)
$hash = password_hash($password, PASSWORD_DEFAULT);

// Verifying (Login)
if (password_verify($input_password, $hash)) {
    // ...
}

• Session Management

  • Cookie: Client-side (browser). Vulnerable to manipulation/theft.
  • Session: Server-side. Client only holds PHPSESSID.
  • Lifecycle Code:

// PHP
session_start(); // TOP OF FILE

// Login
$_SESSION['username'] = $user_id;
$_SESSION['ip'] = $_SERVER['REMOTE_ADDR']; // IP check (security)

// Check (Auth Gate)
if (!isset($_SESSION['username'])) die("Please Login");

// Logout
$_SESSION = array(); // Empty variables
session_destroy();   // Kill session on server

• Attacks:

  • Session Fixation: Attacker forces user to use known SessionID. Fix: session_regenerate_id().
  • Hijacking: Stealing the cookie (via XSS or sniffing). Fix: HTTPS + HttpOnly cookies.

4. Validation & Security (Chapter 16 + Security Lecture)

• Validation: Checking data meets criteria (e.g., is_numeric).
• Sanitization: Cleaning data (removing illegal characters).

The Defensive Coding Matrix (SOC Knowledge)

Prepared Statement Syntax (PDO/MySQLi):

// PHP
$stmt = $conn->prepare("INSERT INTO users (user, pass) VALUES (?, ?)");
$stmt->bind_param("ss", $user, $pass); // "ss" = two strings
$stmt->execute();

• Ternary Operator: $val = (Condition) ? TrueVal : FalseVal;

5. JavaScript & DOM (Chapters 13-15, 21)

• Basics

  • Client-side: Runs in browser. Can be bypassed/disabled by attacker.
  • Include: <script src="code.js"></script> in <head>.
  • Output: document.write(), alert(), console.log().

• DOM (Document Object Model)

  • Accessing elements: document.getElementById('demo').innerHTML = 'Hello';
  • DOM Hierarchy: window → document → html → body.

• Syntax

  • Variables: var (function scope), let (block scope). No $ prefix.
  • Functions: function name(arg) { return arg; }

Arrays:

// JavaScript
var arr = ['a', 'b'];
arr.push('c'); // Add to end
arr.pop();     // Remove last
var len = arr.length;

Loops:

// JavaScript
for (var i = 0; i < 10; i++) { ... }
while (condition) { ... }

Validation (Client-Side)

// JavaScript
function validate(form) {
    if (form.user.value == "") {
        alert("Missing User");
        return false; // Stops submission
    }
    return true;
}

// HTML:
// <form onsubmit="return validate(this)">

• jQuery (Chapter 21)

  • Selectors: $('#id'), $('.class'), $('element').
  • Events: $('#btn').click(function() { ... }).
  • Methods: .hide(), .show(), .val(), .text(), .css('color','red').

6. General Coding & Syntax Cheat Sheet