Connecting PostgreSQL with PHP

# CHAPTER 26

Connecting PostgreSQL with PHP #

1. Introduction #

2. Learning Objectives #

• Understand the difference between pg_connect and PDO.

• Establish a secure database connection using PHP PDO.

• Handle connection errors using try...catch blocks.

• Understand the catastrophic danger of SQL Injection.

• Prevent SQL Injection using Prepared Statements.

3. Choosing the Connection Method (PDO vs pg_connect) #

1. 1. pg_connect(): The older, procedural Postgres-specific extension.

1. 2. PDO (PHP Data Objects): The modern, object-oriented approach. PDO is a universal interface; if you write your code in PDO, you can switch your database from PostgreSQL to MySQL in the future without having to rewrite your entire application!

*The Golden Rule: Always use PDO in modern PHP applications.*

4. Establishing a PDO Connection #

<?php
$host = &#039;127.0.0.1';
$port = &#039;5432';
$dbname = &#039;university_db';
$user = &#039;web_app';
$password = &#039;AppPassword99!';

// Construct the DSN string
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname";

try {
    // Attempt the connection
    $pdo = new PDO($dsn, $user, $password);
    
    // Tell PDO to throw severe exceptions if a query fails
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Successfully connected to PostgreSQL!";
} catch (PDOException $e) {
    // If the database is offline, catch the error gracefully instead of crashing
    die("Database Connection Failed: " . $e->getMessage());
}
?>

5. Executing a Simple Query (No Variables) #

// Fetch all department names
$stmt = $pdo->query("SELECT dept_name FROM departments ORDER BY dept_name ASC");

// Loop through the results and print them
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "Department: " . $row[&#039;dept_name'] . "<br>";
}

6. The Danger: SQL Injection #

// DANGEROUS! NEVER DO THIS!
$email = $_POST['user_input'];
$pdo->query("SELECT * FROM users WHERE email = '$email'");

If a hacker types ' OR '1'='1 into the email box, the query becomes: SELECT * FROM users WHERE email = '' OR '1'='1' Because 1 always equals 1, the database ignores the email check and logs the hacker in as the first user (usually the Admin)! This is SQL Injection.

7. The Solution: Prepared Statements #

// 1. Prepare the query template with a placeholder (?)
$stmt = $pdo->prepare("SELECT first_name, email FROM users WHERE email = ?");

// 2. Execute the query, passing the user input as a separate array
$userInput = 'john@example.com';
$stmt->execute([$userInput]);

// 3. Fetch the secure result
$user = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Welcome, " . $user['first_name'];

8. Mini Project: Secure User Insertion #

<?php
// Assume these came from a $_POST form submission
$firstName = &#039;Alice';
$email = &#039;alice@example.com';

// The SQL template with named placeholders
$sql = "INSERT INTO users (first_name, email) VALUES (:fname, :email)";

// Prepare the statement
$stmt = $pdo->prepare($sql);

try {
    // Execute and bind the variables securely
    $stmt->execute([
        &#039;:fname' => $firstName,
        &#039;:email' => $email
    ]);
    echo "User registered successfully!";
} catch (PDOException $e) {
    // Catch errors (e.g., if the UNIQUE email constraint fails)
    echo "Registration failed: " . $e->getMessage();
}
?>

9. Common Mistakes #

• Exposing Credentials in GitHub: Never hardcode $password = 'AppPassword99!' directly in your PHP files if you are pushing code to GitHub. Hackers scan GitHub continuously for exposed database passwords. Always use .env files to store configuration secrets outside of your code repository.

• Using procedural pg_query(): Avoid legacy functions from older PHP tutorials. Stick strictly to the Object-Oriented PDO class.

10. Best Practices #

• Singleton Database Connection: Opening a connection to PostgreSQL takes a fraction of a second. If your PHP app opens a new connection in 5 different files during a single page load, the site will be incredibly slow. Use a "Singleton" class or Dependency Injection to ensure the PDO connection is opened exactly once per request and reused.

11. Exercises #

1. 1. What is the fundamental difference between $pdo->query() and $pdo->prepare()?

1. 2. Why is the catch (PDOException $e) block critical when establishing a database connection?

12. SQL Challenges #

$newSalary = 75000;
$empId = 10;
$stmt = $pdo->prepare("UPDATE employees SET salary = ? WHERE emp_id = ?");
$stmt->execute([$newSalary, $empId]);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain exactly how a SQL Injection attack works. How do Prepared Statements mechanically neutralize the threat?

• Q: Describe the architectural purpose of a Data Source Name (DSN) string when instantiating a new PDO object.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #