Chapter 26: Integration with Applications

Applications talk to databases through drivers and connection libraries. This chapter shows practical patterns for using SQL from Python, Java, and JavaScript. You will learn how to connect, execute parameterized queries, handle transactions, and avoid security mistakes that lead to injection and data leaks.

SQL in Python (sqlite3/mysql-connector-python)

Python ships with sqlite3 and supports other engines through third party packages. The essential steps are: create a connection, get a cursor, execute statements with parameters, then commit or roll back.

Connecting and querying with sqlite3

# Python 3
import sqlite3

conn = sqlite3.connect("app.db", isolation_level=None)  # autocommit off when None varies across versions …
cur = conn.cursor()

cur.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)")
cur.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Robin", "r@example.com"))

cur.execute("SELECT id, name, email FROM users WHERE email = ?", ("r@example.com",))
row = cur.fetchone()
print(row)

conn.commit()
conn.close()

Use ? placeholders in sqlite3. The driver binds values safely and handles quoting.

Connecting to MySQL with mysql-connector-python

# pip install mysql-connector-python
import mysql.connector as mysql

conn = mysql.connect(
    host="localhost",
    user="app_user",
    password="…",
    database="appdb",
    autocommit=False
)

with conn.cursor() as cur:
    cur.execute("INSERT INTO accounts (email, plan) VALUES (%s, %s)", ("a@example.com", "pro"))
    cur.execute("SELECT id, plan FROM accounts WHERE email = %s", ("a@example.com",))
    print(cur.fetchone())
    conn.commit()

conn.close()

MySQL drivers use %s placeholders. Do not interpolate strings directly. Let the driver bind values.

Transactions and context managers

from contextlib import contextmanager
import sqlite3

@contextmanager
def tx(db_path):
    conn = sqlite3.connect(db_path)
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

with tx("app.db") as conn:
    conn.execute("UPDATE balances SET cents = cents - ? WHERE user_id = ?", (500, 1))
    conn.execute("UPDATE balances SET cents = cents + ? WHERE user_id = ?", (500, 2))

SQL in Java (JDBC)

JDBC provides a uniform interface to many databases. Use a connection pool (HikariCP or similar), prepare statements with placeholders, and manage transactions with try with resources.

Basic JDBC pattern with prepared statements

// Requires driver on classpath, for example MySQL: com.mysql:mysql-connector-j …
import java.sql.*;

public class Repo {
  private final DataSource ds;
  public Repo(DataSource ds) { this.ds = ds; }

  public User findByEmail(String email) throws SQLException {
    String sql = "SELECT id, name, email FROM users WHERE email = ?";
    try (Connection c = ds.getConnection();
         PreparedStatement ps = c.prepareStatement(sql)) {
      ps.setString(1, email);
      try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
          return new User(rs.getLong("id"), rs.getString("name"), rs.getString("email"));
        }
        return null;
      }
    }
  }

  public void transfer(long fromId, long toId, long cents) throws SQLException {
    String debit = "UPDATE balances SET cents = cents - ? WHERE user_id = ?";
    String credit = "UPDATE balances SET cents = cents + ? WHERE user_id = ?";
    try (Connection c = ds.getConnection()) {
      c.setAutoCommit(false);
      try (PreparedStatement d = c.prepareStatement(debit);
           PreparedStatement cr = c.prepareStatement(credit)) {
        d.setLong(1, cents); d.setLong(2, fromId); d.executeUpdate();
        cr.setLong(1, cents); cr.setLong(2, toId);  cr.executeUpdate();
        c.commit();
      } catch (SQLException ex) {
        c.rollback();
        throw ex;
      }
    }
  }
}

Placeholder styles across drivers

SQL in JavaScript (mysql2/pg/better-sqlite3)

In Node, use battle tested drivers. Prefer parameter arrays or named parameters. For pooling, create a singleton pool for the process and reuse it.

MySQL with mysql2 and a pool

// npm i mysql2
const mysql = require("mysql2/promise");

const pool = mysql.createPool({
  uri: "mysql://app_user:…@localhost:3306/appdb",
  connectionLimit: 10
});

async function getUser(email) {
  const [rows] = await pool.query(
    "SELECT id, name FROM users WHERE email = ?",
    [email]
  );
  return rows[0] || null;
}

PostgreSQL with pg

// npm i pg
const { Pool } = require("pg");
const pool = new Pool({ connectionString: "postgres://app_user:…@localhost/appdb" });

async function transfer(client, fromId, toId, cents) {
  await client.query("UPDATE balances SET cents = cents - $1 WHERE user_id = $2", [cents, fromId]);
  await client.query("UPDATE balances SET cents = cents + $1 WHERE user_id = $2", [cents, toId]);
}

async function run() {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await transfer(client, 1, 2, 500);
    await client.query("COMMIT");
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();
  }
}

SQLite with better-sqlite3 (sync API)

// npm i better-sqlite3
const Database = require("better-sqlite3");
const db = new Database("app.db");

db.prepare("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)").run();
db.prepare("INSERT INTO users (name, email) VALUES (?, ?)").run("Robin", "r@example.com");
const row = db.prepare("SELECT id, name FROM users WHERE email = ?").get("r@example.com");
console.log(row);

Avoiding SQL injection

SQL injection happens when user input becomes part of the SQL syntax. The primary defense is parameterized statements. Additional defenses include least privilege accounts, input validation for shape and length, and escaping only as a last resort when a driver lacks parameters for special cases.

Good vs bad patterns

-- Bad (string concatenation)
sql = "SELECT * FROM users WHERE email = '" + email + "'";  -- vulnerable

-- Good (parameters)
sql = "SELECT * FROM users WHERE email = ?";
cur.execute(sql, (email,))

// Bad in Node
const sql = `SELECT * FROM users WHERE id = ${req.query.id}`; // vulnerable

// Good
const [rows] = await pool.query("SELECT * FROM users WHERE id = ?", [req.query.id]);

Dynamic filters without concatenation

# Python example that builds WHERE safely
base = "SELECT id, name FROM products WHERE 1=1"
conds = []
vals = []

if q := params.get("q"):
    conds.append("AND name ILIKE ?")
    vals.append("%" + q + "%")

if brand := params.get("brand"):
    conds.append("AND brand = ?")
    vals.append(brand)

sql = " ".join([base] + conds)
cur.execute(sql, tuple(vals))

Handling LIKE wildcards and escaping

Bind the pattern as a parameter and escape driver specific wildcard characters in the value when necessary. Do not inline user input inside quotes.

Stored procedures and ORMs are not automatic protection

Procedures and ORMs can still be vulnerable if they concatenate strings. Verify parameter binding and review generated SQL. Inspect logs for the final SQL text with placeholders rather than values.

Security pitfalls

Security depends on layers. The following items cover frequent mistakes and the safer alternative.

Secrets in code and connection strings

Network and transport

Require TLS for connections, validate server certificates, and restrict inbound access to the database from application subnets only. Disable direct public access. Use firewall rules or security groups that allow only known application nodes.

Authorization inside the database

Create dedicated roles for application tasks, then GRANT only the permissions required. Avoid using a superuser or root in production. Separate read and write roles. For admin jobs, use a separate connection and role with elevated privileges.

Migration safety

Schema changes can cause outages when long locks block queries. Prefer additive changes, backfill in batches, and create needed indexes before setting columns to NOT NULL. Run heavy operations during maintenance windows.

Exposing raw errors

Never return database error messages directly to users. Map them to generic messages. Log full details to a secure sink for operators to review.

Resource exhaustion

Use connection pools with sensible limits. Set query timeouts and statement timeouts. Monitor for slow queries and add indexes where appropriate. Guard endpoints with rate limits so expensive queries cannot be spammed.