Database Connectivity: PHP and Python with MySQL/MariaDB

Database Connectivity: PHP and Python with MySQL/MariaDB

Web applications almost always need to store and retrieve data. MySQL and MariaDB are open-source relational database management systems (RDBMS) that are widely used in professional web development. MariaDB is a community-maintained fork of MySQL that is largely compatible with MySQL at the application level. In this lesson you will connect both PHP and Python to a MySQL/MariaDB database and perform the four fundamental data operations: SELECT, INSERT, UPDATE, and DELETE — collectively called CRUD (Create, Read, Update, Delete).

Setting Up the Database

Before writing application code, you need a running MySQL or MariaDB server, a database, and a table. Log in to the MySQL shell with a privileged account and run statements similar to these:

```sql

CREATE DATABASE store_db;

USE store_db;

CREATE TABLE products (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

price DECIMAL(10,2) NOT NULL,

quantity INT NOT NULL DEFAULT 0

);

```

Create a dedicated application user with only the permissions it needs — this principle of least privilege limits damage if credentials are ever exposed:

```sql

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, INSERT, UPDATE, DELETE ON store_db.* TO 'app_user'@'localhost';

FLUSH PRIVILEGES;

```

Never connect your application with the root database account.

Connecting from PHP Using PDO

PHP Data Objects (PDO) is a database-abstraction layer built into PHP. It provides a consistent interface regardless of which database you use and — critically — supports prepared statements natively.

A basic PDO connection looks like this:

```php

<?php

$dsn = 'mysql:host=localhost;dbname=store_db;charset=utf8mb4';

$username = 'app_user';

$password = 'StrongPassword!';

$options = [

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

];

try {

$pdo = new PDO($dsn, $username, $password, $options);

} catch (PDOException $e) {

// Log the real error privately; show a generic message to users

error_log($e->getMessage());

die('Database connection failed.');

}

```

Setting `ERRMODE_EXCEPTION` causes PDO to throw a `PDOException` on errors, which you can catch and handle without exposing internal details to the browser.

Connecting from Python Using mysql-connector-python

Install the connector with pip:

```

pip install mysql-connector-python

```

A basic connection:

```python

import mysql.connector

from mysql.connector import Error

try:

conn = mysql.connector.connect(

host='localhost',

database='store_db',

user='app_user',

password='StrongPassword!'

)

except Error as e:

print(f'Connection error: {e}')

conn = None

```

Always check that `conn` is not `None` before proceeding, and close the connection when finished.

Parameterized Queries and SQL Injection

SQL injection is one of the most dangerous and common web vulnerabilities. It occurs when user-supplied input is concatenated directly into a SQL string. An attacker can craft input that alters the query's logic, dumps data, or destroys a database.

The defense is parameterized queries (also called prepared statements). The database driver sends the query structure to the server separately from the data. The server treats the data as plain values — never as executable SQL.

PHP PDO — parameterized INSERT:

```php

$sql = 'INSERT INTO products (name, price, quantity) VALUES (:name, :price, :quantity)';

$stmt = $pdo->prepare($sql);

$stmt->execute([':name' => $name, ':price' => $price, ':quantity' => $qty]);

```

Python — parameterized INSERT:

```python

cursor = conn.cursor()

sql = 'INSERT INTO products (name, price, quantity) VALUES (%s, %s, %s)'

values = (name, price, qty)

cursor.execute(sql, values)

conn.commit()

```

Note the syntax difference: PDO uses named placeholders (`:name`) or positional `?` placeholders; mysql-connector-python uses `%s` positional placeholders regardless of data type.

CRUD Operations

SELECT (Read): Use a prepared statement with any WHERE conditions parameterized. In PHP, call `$stmt->fetchAll()` or `$stmt->fetch()`. In Python, call `cursor.fetchall()` or `cursor.fetchone()`.

UPDATE: Prepare the UPDATE statement with placeholders for both the new values and the WHERE clause. Always include a WHERE clause — an UPDATE without one modifies every row.

DELETE: The same rule applies. Parameterize the identifier in the WHERE clause and double-check the logic before execution.

After INSERT, UPDATE, or DELETE in Python, call `conn.commit()` to persist changes. PDO auto-commits by default unless you start a transaction manually.

Security and Safe Practices

• Never store credentials in version-controlled files. Use environment variables or a configuration file excluded by `.gitignore`.
• Display generic error messages to users. Log detailed errors server-side only.
• Principle of least privilege: the application database user should have only the permissions it actually needs.
• Validate and sanitize input before it reaches any query — parameterization prevents SQL injection, but it does not validate that a price field contains a valid number.
• Close connections and cursors when done to free resources.
• Use TLS for database connections in production environments where the application server and database server are on separate machines.

These practices align with standard secure-coding guidance and are tested in vendor-neutral credentials such as CompTIA ITF+.