What injection really is

Injection flaws arise whenever data and code share the same channel. If your program builds a SQL statement, a shell command, or an LDAP filter by gluing strings together, an attacker who controls part of that string can change its meaning. The database does not see 'a search term' — it sees SQL, and it will faithfully execute whatever the attacker wrote.

Anatomy of a SQL injection

Consider a search that runs select * from products where name like '%<input>%'. If the input is ' UNION SELECT password FROM users-- the quote closes the intended string, UNION SELECT appends a second result set from another table, and -- comments out the rest. The attacker has rewritten your query. The same trick in a login query (' OR 1=1--) makes the WHERE clause always true and bypasses authentication entirely.

The fix: parameterised queries

The cure is to separate code from data. Parameterised queries (prepared statements) send the SQL template and the values over different channels, so the database treats user input strictly as a value — never as syntax. No amount of quotes or SQL keywords in the input can escape a bound parameter. In Effect, the @effect/sql tagged-template `sql` helper parameterises automatically; never fall back to `sql.raw` with interpolated input.

Defence in depth

Parameterisation is the primary control, but layer more on top: use least-privilege database accounts (the web user should not be able to read the password table or DROP tables), validate input against an allow-list where the shape is known, and use an ORM or query builder that parameterises by default. Escaping by hand is error-prone — prefer APIs that make the safe path the default path.

Insecure vs secure

const q = url.searchParams.get("q") ?? "";
const rows = await sql.raw(
  `select * from products where name like '%${q}%'`
);

const q = url.searchParams.get("q") ?? "";
const rows = await sql`
  select * from products where name like ${'%' + q + '%'}
`;

Key takeaways

• Injection happens when data and code travel through the same channel.
• String concatenation into a query is the root cause — not the input itself.
• Parameterised queries bind input as values, never as executable syntax.
• Add least-privilege DB accounts and allow-list validation as extra layers.

Knowledge check

Practice

Apply this lesson hands-on in the matching find-it / fix-it challenge.