Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
The Server Half  ›  Lesson

Talking to PostgreSQL from Code

PostgreSQL 14 minA Node server reaches PostgreSQL through a connection pool created once at startup, and sends every query as fixed SQL text with values passed separately as parameters, which prevents SQL injection.

Talking to PostgreSQL from Code

The handler needs plants, and the plants live in PostgreSQL. You already know the SQL; what is new is sending it from Node, safely. One careless line here, gluing a user's text into a query, is behind some of the worst data breaches on the web.

The rule

The SQL text stays fixed; values travel separately as parameters.

import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

export async function listPlants(ownerId) {
  const { rows } = await pool.query(
    'SELECT id, name, water_every_days, last_watered FROM plants WHERE owner_id = $1 ORDER BY name',
    [ownerId]
  );
  return rows;
}
  • The pool is a set of open database connections that the server reuses. Opening a connection costs a network handshake and a login, so the pool is created once when the server starts, never once per request.
  • DATABASE_URL looks like postgres://sprout:secret@db-host:5432/sprout. It comes from the environment, never from the code, because it contains a password.
  • $1, $2, ... are placeholders. The values go in a separate array, and the database treats them strictly as data, never as SQL.
  • rows is an array of plain objects, one per row, keyed by column name, such as { id: 7, name: 'Fern', ... }. It is ready to hand to res.json.

Here is the line that must never be written:

// Never do this: the value becomes part of the SQL
pool.query("SELECT * FROM plants WHERE name = '" + req.query.name + "'");

If someone sends the name x' OR '1'='1, the database receives ... WHERE name = 'x' OR '1'='1', a condition that is always true, and returns every user's plants. This is SQL injection. With $1, that whole string is just an odd plant name that matches nothing. Parameters also fix honest names: "Grandma's fern" contains a quote that would break the glued version.

The same pool and parameters work outside the web server too. A small command-line script, such as node scripts/seed.js to insert test plants, talks to PostgreSQL in exactly the same way; only the trigger differs.

The shortcut

Scan every query( call: the SQL string contains $1, $2, ... and nothing glued on. A + or a ${ building the SQL text out of outside values is a bug, even if it "works" today.

Adding a plant safelyStep 1 of 4

  1. The input

    req.body is { name: "Basil", water_every_days: 2 }, and the user is 5.

Drills

Answer fast — trust the rule.

quick drill

Why is the pool created once, when the server starts?

quick drill

Which call safely finds the plants with a given name?

quick drill

An attacker sends the name x' OR '1'='1 to a query that uses $1. What happens?

quick drill

Why add RETURNING * to Sprout's INSERT?

Remember

  • The SQL text stays fixed; values travel separately as parameters.
  • Shortcut: every query( has $1, $2, ... and nothing glued onto the SQL.
  • Trap: "the input is harmless" is never a reason to concatenate. Requests can be sent by anyone, with anything in them.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainBut the search box only allows letters

A teammate says: 'Sprout's search box only lets people type letters, so building the SQL by joining strings is fine.' Explain why that is still unsafe, and show what the parameterised version of a name search looks like.