Application Integration: Python/PHP with PostgreSQL and SQL Injection Prevention

Connecting a Python or PHP Application to PostgreSQL

Web and software applications rarely store data inside the program itself. Instead, they connect to a database server, send queries, and receive results. PostgreSQL is a widely used open-source relational database. To connect an application to it you need a database driver — a library that handles the network connection, authentication, and translation of Python or PHP code into messages the database server understands.

• Python uses psycopg2, the most established PostgreSQL adapter. Install it with `pip install psycopg2-binary`.
• PHP uses PDO (PHP Data Objects), a built-in database abstraction layer that supports PostgreSQL through the `pdo_pgsql` extension.

A typical connection in psycopg2 looks like this:

```python

import psycopg2

conn = psycopg2.connect(

host="localhost",

dbname="library",

user="student",

password="secret"

)

cur = conn.cursor()

```

In PHP with PDO:

```php

$dsn = "pgsql:host=localhost;dbname=library";

$pdo = new PDO($dsn, 'student', 'secret');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

```

What Is SQL Injection and Why Is It Dangerous?

SQL injection is one of the most critical and long-standing web application security vulnerabilities. It appears on the OWASP Top 10 list of security risks. It happens when user-supplied input is concatenated directly into a SQL string before being sent to the database. An attacker can craft input that changes the meaning of the query, allowing them to read unauthorized data, modify or delete records, or even execute administrative commands.

Vulnerable example (Python):

```python

term = input("Search author: ")

query = "SELECT * FROM books WHERE author = '" + term + "'"

cur.execute(query)

```

If the user types `' OR '1'='1`, the query becomes:

```sql

SELECT * FROM books WHERE author = '' OR '1'='1'

```

This condition is always true, so every row in the table is returned — a data breach. More destructive payloads can drop tables or bypass login checks entirely.

The Fix: Parameterized Queries

A parameterized query (also called a prepared statement) separates the SQL structure from the user data. The driver sends the query and the parameters to the database in separate steps. The database engine treats the parameter as a literal value, never as executable SQL, no matter what it contains.

Safe example (psycopg2):

```python

term = input("Search author: ")

cur.execute("SELECT * FROM books WHERE author = %s", (term,))

rows = cur.fetchall()

for row in rows:

print(row)

```

Note the `%s` placeholder and the tuple `(term,)` passed as the second argument. psycopg2 handles escaping automatically.

Safe example (PDO):

```php

$stmt = $pdo->prepare("SELECT * FROM books WHERE author = :author");

$stmt->execute([':author' => $term]);

$rows = $stmt->fetchAll();

```

Parameterized queries are always the correct approach when incorporating user input into a query. There is no safe way to manually escape strings that matches the protection level of parameterized queries.

Connection Management and Error Handling

Database connections are limited resources. Leaving connections open wastes server memory and can exhaust the connection pool. Use a context manager in Python to ensure the connection closes even if an error occurs:

```python

import psycopg2

from contextlib import closing

with psycopg2.connect(host="localhost", dbname="library",

user="student", password="secret") as conn:

with closing(conn.cursor()) as cur:

cur.execute("SELECT title FROM books WHERE id = %s", (1,))

print(cur.fetchone())

```

Wrap database calls in try/except (Python) or try/catch (PHP) blocks to catch driver exceptions, log them, and handle failures gracefully without leaking error details to end users.

```python

try:

cur.execute("INSERT INTO books (title, author) VALUES (%s, %s)",

(title, author))

conn.commit()

except psycopg2.DatabaseError as e:

conn.rollback()

print("Database error. Transaction rolled back.")

```

Always call `conn.commit()` after INSERT, UPDATE, or DELETE. If an error occurs, call `conn.rollback()` to undo partial changes.

Building a CRUD Command-Line Application

A complete application needs four operations:

| Operation | SQL Statement | Notes |

|-----------|--------------|-------|

| Create | `INSERT INTO books ...` | Use parameterized values; commit after |

| Read | `SELECT * FROM books ...` | Use parameterized WHERE clause |

| Update | `UPDATE books SET ... WHERE id = ...` | Always filter by primary key |

| Delete | `DELETE FROM books WHERE id = ...` | Confirm before deleting; commit after |

Structure your application with one function per operation. Each function opens a cursor, executes a parameterized query, and handles its own exception. Keep the main loop simple: prompt the user, call the appropriate function, and display the result.

Security Best Practices Summary

• Always use parameterized queries — never concatenate user input into SQL strings.
• Limit database user privileges: the application's database account should only have the permissions it needs (SELECT, INSERT, UPDATE, DELETE on specific tables). Avoid connecting as a superuser.
• Never display raw database error messages to end users; log them server-side instead.
• Validate input on the application side as an additional layer, but do not rely on validation alone to prevent injection.
• Review the OWASP SQL Injection Prevention Cheat Sheet before writing any production database code.

Following these practices protects user data, satisfies employers' security requirements, and demonstrates the professional standards expected in the trade.