Project 3 — PHP API, SQL, uploads and signed video access

Files and responsibilities

public/api.php implements the API. schema.sql creates the posts table. config/config.example.php documents settings and must be copied outside the public directory. Composer installs the AWS SDK in vendor/, also outside public. The browser receives only static files and JSON responses.

Endpoint contract

The session expires for admin actions after one hour. Cookies are HttpOnly, SameSite=Strict and Secure by default. Configure HTTPS before login; a Secure cookie will not work over ordinary public HTTP. Session storage is local to the server in this lab, so load-balanced multi-instance deployment needs a shared session strategy or different authentication design.

Database safety

The API uses PDO prepared statements and disables emulated prepares. IDs are validated as positive integers, caption lengths are bounded and output is serialized as JSON. The frontend renders captions using textContent, so a caption containing HTML is displayed as text instead of executed. Network isolation, SQL privileges and input validation all remain necessary.

Upload sequence

Check admin/CSRF, validate upload status/size, identify MIME from file content, assign a random server-generated key, upload to S3, then insert metadata. The lab accepts MP4/WebM up to 20 MiB. MIME checking is not full malware scanning or video decoding; a public upload platform needs deeper validation/transcoding/moderation. S3 and MySQL do not share one ACID transaction. If the database insert fails, the API attempts S3 cleanup and logs an orphan marker if cleanup also fails. A process crash between operations can still leave an orphan; a periodic reconciliation job is an advanced exercise.

Signed URLs

The feed signs GetObject with a ten-minute expiry. The URL is usable by anyone holding it during its validity and may expire sooner if underlying temporary credentials expire. No S3 credentials go to JavaScript. Reload the feed to obtain fresh URLs; this sample does not implement continuous token refresh for long viewing sessions.

Official references

PHP PDO prepared statements S3 signed URLs with PHP

Interview and revision check

Why is S3 cleanup only compensation rather than a transaction rollback?

S3 and MySQL are separate systems. A crash or failed cleanup can leave an orphan, requiring reconciliation.