SQL injection

What is SQL injection?

Most websites keep their data in a database and talk to it in a language called SQL. A line such as SELECT bird, place FROM sightings WHERE bird = 'kingfisher' asks the database for every kingfisher sighting. The site writes the fixed parts, and a visitor supplies the bird's name through a search box.

SQL injection happens when the site builds that line by gluing the visitor's text straight into it. The database receives one finished command and has no idea which characters the club wrote and which ones a stranger typed. If the stranger types characters that mean something in SQL, such as a quote mark, they can change what the command does.

What an attacker can do depends on the query. A search can be made to return every row in a table, including rows the visitor should never see. A sign-in check can be made to approve a visitor with no password. A query that writes data can be turned into one that changes or deletes other people's records. It has been one of the most common serious web bugs for over twenty years, and it is still found in new code every week.

How does the quote trick work?

The bird club's search on line 7 of server.js puts the visitor's text between two single quotes. With an ordinary search, the finished SQL reads WHERE bird = 'heron', which is what the club intended.

The pretend attacker types ' OR '1'='1' -- instead. The first quote in that text closes the club's string early. OR '1'='1' adds a second condition that is true for every row. The two dashes start a SQL comment, which tells the database to ignore whatever comes after, including the club's own closing quote. (MySQL wants a space after the dashes, so real payloads vary a little between databases.) The database runs a perfectly valid command, and that command returns the whole table.

Nothing here is a flaw in the database. It did exactly what the text told it. The flaw is that the club let a visitor write part of the instructions. That is the same root cause as cross-site scripting: data from outside was treated as code. The difference is only which language it ends up in, HTML for scripting and SQL for injection.

-- What the club meant:
SELECT bird, place, seen_on FROM sightings WHERE bird = 'heron'

-- What the database received:
SELECT bird, place, seen_on FROM sightings WHERE bird = '' OR '1'='1' --'

How do placeholders stop it?

The fix is to send the database two things instead of one: a fixed command with a ? where each value goes, and a separate list of values. The database reads and plans the command first, then fills each ? with a value. A value can contain quotes, dashes and the word OR, and it is still only ever a value.

In the task you changed line 7 to pass [req.query.bird] as a second argument. When the attack ran again, the database searched for a bird whose name was literally ' OR '1'='1' --. No such bird has been seen on the river, so the list came back empty. A member called O'Neill searching for their own sightings also works now, which the glued version would have broken with a syntax error.

This style is called a parameterised query, or a prepared statement. The ? is the placeholder style used by SQLite and MySQL drivers. PostgreSQL's pg package uses $1, $2 and so on. Some libraries accept named placeholders such as :bird. The idea is identical in every case.

const rows = db.all(
  'SELECT bird, place, seen_on FROM sightings WHERE bird = ?',
  [req.query.bird]
);

Where can injected text come from?

Any value that arrives from a browser can carry it. Search boxes are the obvious place, and the lab's attack uses one, but attackers try every input they can reach.

• Form fields, including hidden ones and drop-down menus, since the visitor can change their values before sending.
• The address: query strings such as ?bird=heron and route parts such as /walks/12.
• Cookies and request headers, which a visitor's own tools can set to anything.
• JSON sent by your own front-end code, which an attacker can replay with different contents.
• Data already stored in your database that a visitor wrote earlier, if you later glue it into another query.

Why does a number field still need a placeholder?

A common thought is that a value like a walk's id is always a number, so gluing it in is harmless. The browser does not enforce that. A visitor can change /walks/12 to /walks/12 OR 1=1 in the address bar, and if the code builds '... WHERE id = ' + req.params.id, the database receives exactly that. Numbers do not even need quotes to break out of, which makes this version easier to abuse.

Checking that the id is a whole number before using it is a sensible extra step, and it gives members a friendlier error message. Use the placeholder as well. Placeholders make injection impossible for that value, while validation only makes it unlikely, and the two together cost one line.

How do you spot it in code?

Look at every call that sends SQL to the database, usually named something like db.query, db.all, db.get, db.run or pool.query. Read the first argument. If it is one fixed string and the values come afterwards in a list, that query is safe from injection. If you see + joining strings, or ${...} inside backticks, visitor text may be flowing into the command.

Watch for SQL built in a variable on an earlier line, as in the Break it exercise. const sql = "..." + req.query.place followed by db.all(sql) is exactly as unsafe as doing it all in one call. Follow the variable back to where it was made.

When you test by hand, try a single quote in each search or form field on your own site. If the page shows a database error or behaves oddly, that field reaches the SQL without a placeholder. Only do this on sites you own or have written permission to test.

How does the lab simplify this?

The lab reads server.js with pattern rules and never runs a real database. It marks a query as glued when it sees + or ${} in the SQL, and as safe when it sees a fixed string followed by a list of values. Code written in an unusual shape can fool those rules in either direction. A real code review, or a scanner such as GitHub's code scanning, follows the data more carefully.

The lab also tries one payload, and a real attacker tries hundreds, often with automated tools that test every field on a site in minutes. Those tools can also pull data out slowly, a yes or no at a time, from pages that show no results at all. A query that uses placeholders is safe against those variants too, since the value never becomes part of the command.

What do AI tools get wrong about SQL injection?

Ask an assistant for a quick search route and it will often write SQL with a template string, such as `db.query(SELECT * FROM walks WHERE place = '${place}'). It reads naturally and works in a demo, which is why it keeps appearing. Template strings feel modern, and they are just as unsafe as +`.

Assistants sometimes suggest escaping quotes by hand with something like place.replace(/'/g, "''"). Hand-made escaping misses cases, differs between databases and is easy to forget on one field. When you see it, replace it with a placeholder. If an assistant tells you to sanitise input without naming a method, ask it to rewrite the query with placeholders and check the result yourself.

Field notes

• SQL injection happens when visitor text becomes part of a database command.
• A quote in the input ends the string early, so ' OR '1'='1' -- changes the query.
• Placeholders send values apart from the query, so they can never change its meaning.
• Ids from the address need placeholders too, since 12 OR 1=1 is valid SQL.

Easy to get wrong

Gluing lets a quote in the visitor's text end the string and add new SQL. A placeholder keeps the value apart from the command.

A template string builds one finished command, exactly like +. The tidier syntax changes nothing about the risk.

The visitor controls the address and can put any text where the number should be. Numeric injection needs no quote at all.

Questions people ask

Does using an ORM protect me?

Mostly. Libraries such as Prisma, Sequelize and Knex use placeholders for normal calls. Each one also has a raw SQL option, and gluing text into that brings the problem straight back.

Is escaping quotes enough?

It is fragile. Rules differ between databases and character sets, and it is easy to forget one field. Placeholders are simpler and work for every value.

Can SQL injection happen without a search box?

Yes. Any value from the browser can carry it, including the address, cookies, headers and JSON from your own scripts.

Is it safe to try the quote trick on other sites?

No. Testing a site you do not own without permission is illegal in most countries. Use this lab or your own local copy.