SQL injection and unsafe HTML

Why is text from visitors dangerous?

Everything a visitor sends is text you didn't write: the query string, form fields, JSON bodies, cookies, even headers. Most of it is harmless. The trouble starts when your server pastes that text into something that another program reads as instructions. SQL is read by the database. HTML is read by the browser. If a visitor's text lands in either one unchanged, the visitor gets to write part of your program.

The two attacks have different names and the same cause. SQL injection happens when input changes the meaning of a database query. Cross-site scripting, usually shortened to XSS, happens when input changes the meaning of a page, typically by adding a script that runs in someone else's browser. Both have been on every list of common web security flaws for over twenty years, and both still turn up in new code, including code written by AI tools.

How does SQL injection actually work?

Look at the starting search route. It builds the query by adding the bird name between two quote marks. For Robin, the database receives ... AND bird = 'Robin', which is fine. Now send the **Try an injection** request. The name is x' OR '1'='1, and the glued-together SQL becomes WHERE hidden = 0 AND bird = 'x' OR '1'='1'.

The visitor's quote mark closed the string early, and the rest of their text became SQL. Because AND binds tighter than OR, the condition now reads as "either a visible sighting of a bird called x, or one equals one", which is true for every row. The goshawk nest marked hidden appears in the results. On a real site the same trick reads other members' emails, skips a password check, or with a slightly longer string deletes a table.

There is a quieter symptom too. Search for Bewick's swan, a real bird, and the server answers 500 with a syntax error in the Log. The apostrophe ends the string just like the attack did. Code that crashes on an apostrophe in a name is code that can be injected.

Why do placeholders fix it?

A placeholder sends the SQL and the values to the database separately. You write ? where the value belongs, and better-sqlite3 hands the text over as data after the query has already been parsed. Whatever the value contains, quotes, semicolons, the word DROP, it can only ever be compared with the bird column. It can't become part of the query's grammar.

This is the same rule from the database lesson, and now you can see why it is a firm rule. Escaping quotes by hand is the old, fragile fix: it is easy to miss a case, and different databases escape differently. Placeholders leave the job to the database driver, which gets it right every time.

// Unsafe: the name becomes part of the SQL text
db.prepare("SELECT * FROM sightings WHERE bird = '" + bird + "'").all();
db.prepare(`SELECT * FROM sightings WHERE bird = '${bird}'`).all();

// Safe: the name is a separate value
db.prepare('SELECT * FROM sightings WHERE bird = ?').all(bird);
db.prepare('SELECT * FROM sightings WHERE bird = @bird').all({ bird });

What can a placeholder not do?

Placeholders stand in for values only. You can't use one for a table name, a column name, or the ASC and DESC in ORDER BY. A sort option like /search?sort=place tempts people back into gluing text into SQL. The safe pattern is an allowlist: compare the input with a short list of names you wrote yourself, and use your own string, never theirs.

The same goes for LIKE searches. Write bird LIKE ? and pass '%' + term + '%' as the value. The percent signs are part of the value, so the database still treats the whole thing as data.

const sortColumns = { bird: 'bird', place: 'place' };
const column = sortColumns[req.query.sort] || 'bird';
const rows = db
  .prepare(`SELECT bird, place FROM sightings WHERE bird LIKE ? ORDER BY ${column}`)
  .all('%' + term + '%');

How does unsafe HTML turn into a script?

Search for Wren. One member typed a note that ends with <img src=x onerror="alert(1)">. The starting code adds the note to the page as it is, so the browser sees a real image tag, fails to load x, and runs the onerror code. Here it only shows an alert. A real attacker would read the page, send requests as the logged-in visitor, or redirect them to a fake login form. Every visitor who views the wren sightings runs it.

That is stored XSS: the attack sits in the database and hits whoever reads it. The "No sightings of …" line has the reflected kind. The searched name goes straight back into the page, so a link like /search?bird=<script>…</script> attacks whoever clicks it. Both come from the same mistake: treating text as HTML.

What does escaping do?

Escaping replaces the characters that have meaning in HTML with entities that the browser shows as plain characters. < becomes <, > becomes >, & becomes &, and the two quote marks become " and '. The browser then displays <img src=x …> as visible text instead of building an element. Replace & first, or you would turn the & inside < into <.

Escape at the last moment, where the value goes into the HTML, and escape every value that came from outside, including ones from your own database. Data that a member typed last year is still visitor text. Escape each value exactly once: escaping twice makes the page show < literally, which the change notes will warn you about.

function escapeHtml(value) {
  return String(value)
    .replaceAll('&', '&')
    .replaceAll('<', '&lt;')
    .replaceAll('>', '&gt;')
    .replaceAll('"', '&quot;')
    .replaceAll("'", '&#39;');
}

• Between tags and inside quoted attributes, escapeHtml() is enough. Always put quotes around attribute values.
• In an href or src, also check the scheme: javascript:alert(1) contains nothing to escape. Allow only https: links, or links that start with /.
• Inside a <script> block or a style attribute, HTML escaping is the wrong tool. Send the data as JSON from an API instead and set it with textContent in the page.

What does Content-Security-Policy add?

A Content-Security-Policy header tells the browser which scripts, styles and images a page may use. default-src 'self' allows files from your own site and blocks inline scripts and inline event handlers such as onerror. If an unescaped value slips through one day, the injected script still doesn't run.

Treat it as a second layer, never as the fix. Escaping stops the HTML from being built wrong; the policy limits the damage when you miss a spot. A strict policy also means moving your own inline <script> code into files under public/. Browsers ignore a header they don't recognise without any warning, so check the exact spelling in the Requests view.

What should you check when an AI writes this code?

Search the file for SQL built with + or with ${ inside a template literal. Backticks look modern but build exactly the same unsafe string. Every value in a prepare() call should arrive through ? or @name.

Then look at each place HTML is assembled: every ${…} inside a template literal of HTML needs an escaping function around it, unless it is a fixed string you wrote. Generated helpers often use replace with a string pattern, which changes only the first match, or forget the quote characters. Finally, test with hostile input yourself: an apostrophe, <b>, and ' OR '1'='1. Read the Requests view and the Log after each one. A server that handles those three calmly has usually closed both holes.

Field notes

• Everything a visitor sends is text you didn't write, so treat it with care.
• Gluing visitor text into SQL lets it rewrite the query; a ? placeholder keeps it as data.
• Text added to a page as raw HTML can run as a script in other people's browsers.
• Escaping turns characters like < into < so the browser shows them as plain text.

Easy to get wrong

A template literal is string gluing with nicer syntax. The email still becomes part of the SQL text, so a quote mark in it changes the query. Placeholders keep the value separate.

With a string pattern, replace changes only the first match. A note with two tags keeps the second one. Use replaceAll, or a regular expression with the g flag.

Escaping on the way in stores HTML entities in the database, which then show up wrongly in JSON, emails and anywhere else the note is used, and get escaped twice in pages. Store the original text and escape for the place where it is shown.

Questions people ask

Does a JSON API need HTML escaping?

Not when it answers with res.json(): the application/json type tells the browser not to treat the body as a page. The escaping moves to whoever turns that JSON into HTML. In a page, set it with textContent, which never parses tags, instead of innerHTML.

Should I strip out tags instead of escaping them?

Usually not. Stripping changes what people wrote, and hand-written filters are easy to get around. Escape so the text shows exactly as typed. If members really need formatting, offer a small set of plain-text conventions and convert them yourself, or use a well-maintained HTML sanitiser.

Do template engines escape for me?

Most do. Engines used with Express escape values by default and offer a separate syntax for raw HTML. That raw syntax is where XSS comes back, so search for it when you review code. This course builds HTML with template literals, which never escape anything on their own.

Is SQL injection still common in 2026?

Yes. Modern drivers make placeholders easy, but string-built queries keep appearing in quick fixes, search and sort features, and generated code. Database errors that mention syntax near a quote mark, like the swan search here, are a common sign.