Iterative Development: Backend, Database, and API Build

Iterative Development: Backend, Database, and API Build

This lab spans three one-week sprints. Each sprint ends with a working, tested feature merged into your `develop` branch through a pull request. By the end of all three sprints you will have a Python (Flask or Django) or PHP 8 backend, a PostgreSQL 15 database, and a documented REST API — all protected against the most common web vulnerabilities.

Sprint Structure and Why It Matters

A sprint is a fixed-length cycle in which a team delivers working software. Using sprints trains you to break a large project into manageable pieces, ship code frequently, and get feedback before problems compound. Each sprint here has the same rhythm: plan the feature, write tests first (or alongside), build, run CI, conduct a peer security audit, then merge.

• Sprint 1 — Data models and CRUD routes. Design your database schema in PostgreSQL, write the ORM models or raw SQL table definitions, and expose Create, Read, Update, and Delete endpoints.
• Sprint 2 — User authentication. Implement registration and login. Passwords must be stored as salted hashes — never plaintext. Use a proven library: `bcrypt` or `werkzeug.security` in Python, `password_hash()`/`password_verify()` in PHP. Issue sessions or JSON Web Tokens (JWTs) to keep users logged in.
• Sprint 3 — Remaining features and input validation. Finish all core routes, enforce input validation on every endpoint, and close any security findings from earlier sprints.

Parameterized Queries: Your Primary Defense Against SQL Injection

SQL injection is consistently listed in the OWASP Top Ten because it allows an attacker to manipulate your database by inserting SQL syntax into user-supplied input. The fix is always the same: use parameterized queries (also called prepared statements). Never concatenate user input directly into a SQL string.

Python / psycopg2 example:

```python

cur.execute(

"SELECT * FROM users WHERE email = %s AND active = %s",

(email, True)

)

```

The database driver sends the SQL template and the data separately. The database engine never interprets the data as code.

PHP / PDO example:

```php

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');

$stmt->execute(['email' => $email]);

```

The `:email` placeholder is filled by the driver, not by string concatenation.

Preventing Cross-Site Scripting (XSS)

XSS occurs when unescaped user data is written into an HTML page and a browser executes it as JavaScript. Flask's Jinja2 and Django's template engine escape variables by default. In PHP, always call `htmlspecialchars()` before echoing user data into HTML. For API responses that return JSON, the browser does not render the JSON as HTML, which reduces XSS risk — but your front end must still escape data before inserting it into the DOM.

Writing Tests and Running CI

A CI (continuous integration) pipeline automatically runs your test suite every time you push a commit or open a pull request. GitHub Actions is configured with a YAML file stored in `.github/workflows/`. A basic Python workflow installs dependencies, runs `pytest`, and reports pass or fail. A failing build blocks the merge — this is the safety net that keeps broken code out of `develop`.

Your test suite must cover at least 70 percent of custom functions and every function that touches the database. For Python, `pytest` with `pytest-cov` measures coverage. For PHP, PHPUnit has built-in coverage reporting. Write tests that check both the happy path (valid input returns expected result) and error paths (invalid input raises the correct exception or returns the correct HTTP status code). These skills map directly to the PCEP Python exam areas of functions, error handling, and program logic.

REST API Design and Documentation

A REST API uses standard HTTP methods to express intent: `GET` retrieves data, `POST` creates a resource, `PUT` or `PATCH` updates it, and `DELETE` removes it. Each endpoint should return a consistent JSON structure and an appropriate HTTP status code (200, 201, 400, 401, 403, 404, 500). Use Postman or `curl` to test each endpoint manually during development, and keep a simple API reference document (a Markdown table or an OpenAPI YAML file) updated as you build.

The Peer Security Audit

Before each sprint closes, your partner attempts SQL injection against your login and search endpoints and inspects your HTML output for unescaped data, using the OWASP Top Ten as a checklist. Every finding becomes a GitHub Issue labeled `security`. You resolve it and close the issue before the sprint ends. This mirrors real-world penetration testing workflows and reinforces defense-in-depth thinking.

Pull Request Discipline

Each sprint's work is committed to a feature branch, then merged into `develop` through a pull request. The PR must show a green CI build and must pass instructor code review at 70 percent or higher on the sprint rubric (logic correctness, security, code style, test coverage). A PR scoring below 70 percent is revised and re-reviewed within three class days. This gate exists to protect code quality — the same way a building inspector must sign off before a structure is occupied.