🔴 SQL Injection

SQL Injection: Think of SQL Injection as dictating an instruction to a receptionist over the phone.

Think of SQL Injection as dictating an instruction to a receptionist over the phone. You say: "send the file to this person — name: Ahmet." If the caller pronounces their name as "Ahmet, and by the way also fax over the entire archive," the receptionist has no way to tell where the name ended and a new order began — both arrived through the same channel, inside the same sentence. A query built by string concatenation is exactly that deaf: input like `' OR '1'='1` stops being data and becomes a **command**. So why isn't this solved by "sanitizing dangerous characters" — isn't filtering quotes enough? The industry learned the hard way that it isn't: a blacklist is always incomplete (encoding variants, Unicode look-alikes, multi-byte charsets, comment markers). The real fix is not chasing characters but **separating the channels**: with a parameterized query the skeleton of the statement reaches the database first, alone, and the user's data arrives afterwards in its own box. Whatever characters that data contains, it can no longer become a command — the moment for reading commands has already passed. Java's `PreparedStatement` exists for precisely this, and the difference is one line: `"... WHERE user='" + name + "'"` is defenceless, while `"... WHERE user=?"` plus `setString(1, name)` is structurally immune. Python's `cursor.execute(sql, (name,))` is the same idea. For QA there is a clear ownership claim here: this is not something to hand off as "the security team's job" — it is a negative test case that belongs on every login, search and filter field you own. If `' OR '1'='1' --` logs you in, that is not a bug, it is an incident; and caught in test rather than after release, it costs a thousandth as much. One hard rule though: injection tests are run against an isolated test database, never against live data.

SQL Injection & Parameterized Queries

Micro Lab: SQL — GROUP BY / CTE / Window

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

Step by Step: SQL — GROUP BY / CTE / Window

Aggregation or Window?

Determine: aggregation (summarizing) or Window (row-by-row computing)?

For aggregation, select the summary column with GROUP BY

Apply condition on groups with HAVING — like WHERE but after aggregation

Make modular with CTE

Modularize query with WITH cte_name AS (...) SELECT ... FROM cte_name

Add OVER(PARTITION BY)

For Window, use SUM(col) OVER (PARTITION BY partition ORDER BY sort)

What is the SQL GROUP BY and Window Function query writing order?