Connecting from Application Code

Every lesson so far has run SQL directly against StreamHub's tables. In a real project, that SQL almost always gets sent by application code written in some other language - PHP, Python, or whatever a particular site is built with. This lesson closes that gap with real, working connection code in the two languages this site's own tracks already teach, and ties every example straight back to the parameterized-query fix from Security Best Practices.

1. PHP: Connecting with mysqli

PHP's mysqli extension is the same one this site's own connection.php uses. Opening a connection to StreamHub looks like this:

<?php
  $mysqli = new mysqli('localhost', 'streamhub_app', 'password', 'streamhub');

  if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
  }

Notice the account here is streamhub_app, not root - the same least-privilege account created back in Security Best Practices. If you've worked through the PHP track already, this same pattern of reading server details into variables first shows up in PHP Variables and PHP Superglobals.

2. PHP: Parameterized Queries with mysqli

Here's exactly what "the fix" from the previous lesson looks like in real code. Instead of concatenating a year straight into a query string, a placeholder (?) marks where the value belongs, and bind_param() supplies it separately:

$year = $_GET['year'] ?? '2000';

$stmt = $mysqli->prepare('SELECT * FROM movies WHERE release_year > ?');
$stmt->bind_param('i', $year);
$stmt->execute();

$result = $stmt->get_result();
while ($movie = $result->fetch_assoc()) {
  echo $movie['title'];
}

$stmt->close();
$mysqli->close();

Whatever value $year ends up holding, it is only ever treated as data being compared against release_year - it can never reshape the query itself. That's the entire idea behind the "bad concatenation vs. parameterized query" comparison from the previous lesson, written out in actual PHP.

3. PHP: PDO as an Alternative

mysqli isn't PHP's only option - PDO (PHP Data Objects) is a more general database access layer that works against MySQL and several other database systems through the same interface. It offers roughly the same parameterized-query safety, just with named placeholders instead of ? and bind_param():

$pdo = new PDO(
  'mysql:host=localhost;dbname=streamhub;charset=utf8mb4',
  'streamhub_app',
  'password'
);

$stmt = $pdo->prepare('SELECT * FROM movies WHERE release_year > :year');
$stmt->execute(['year' => $year]);
$movies = $stmt->fetchAll();

Either extension is a genuinely safe choice as long as you always reach for the prepared statement form shown above, and never build the SQL string by hand out of user input.

4. Python: Connecting with mysql-connector-python

On the Python side, mysql-connector-python (and the similar PyMySQL package) follow the exact same shape - connect, then run a parameterized query with %s placeholders instead of building a string with an f-string or +:

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='streamhub_app',
    password='password',
    database='streamhub'
)

cursor = conn.cursor(dictionary=True)
year = 2015

cursor.execute("SELECT * FROM movies WHERE release_year > %s", (year,))
for movie in cursor.fetchall():
    print(movie['title'])

cursor.close()
conn.close()

The (year,) tuple passed as the second argument is what keeps this safe - the driver sends the query text and the value separately, the same guarantee bind_param() and PDO's execute() gave the PHP examples above.

5. ORMs Handle This Automatically

If you've been through this site's Django or Flask tracks, you've likely never written a raw parameterized query like the one above at all - and that's by design. Django's ORM and Flask-SQLAlchemy both generate parameterized SQL for you behind the scenes:

Movie.objects.filter(release_year__gt=2015)

Movie.query.filter(Movie.release_year > 2015).all()

Neither line touches a SQL string at all, yet both compile down to a real, safely parameterized query under the hood. An ORM isn't a different, magical way of avoiding SQL injection - it's a more convenient layer built directly on top of the exact same parameterized-query safety shown in the raw PHP and Python examples above.

6. Connection Pooling

Opening a brand-new connection to MySQL for every single incoming request is genuinely wasteful - each connection involves a real network handshake and authentication step, and doing that fresh for every request adds noticeable, unnecessary latency under real traffic. Connection pooling solves this by keeping a small set of already-open connections ready to be reused, so a request borrows one, uses it briefly, and returns it rather than tearing it down and rebuilding it from scratch. Most real frameworks - including Django's and Flask-SQLAlchemy's database layers - manage a connection pool for you automatically, so this is mostly something to be aware of rather than something you'll hand-roll yourself.

7. Closing Connections Properly

The raw examples above call close() once they're done with a connection, which is good practice when you're managing connections by hand - it frees up the resource promptly instead of leaving it held open until the script ends. In practice, frameworks and ORMs typically manage this entire lifecycle for you: a request-scoped session or connection gets opened, used, and cleaned up automatically as part of the request cycle, without you needing to remember to close anything yourself.