A product search runs ... WHERE PRODUCT_NAME LIKE '%<input>%'. Why can't an attacker just paste in a valid SELECT, and what must the payload do instead?
Because the input lands in the middle of someone else's statement. The payload has to close the quote it was dropped into, match the original SELECT's column count and types, and comment away the leftovers — only then does the combined string still parse.
* Anatomy of the payload — it must escape the quoting, match the column count and types, and comment away whatever follows. *
The developer's query and the injected value:
SELECT PRODUCT_NAME, PRICE, DESCRIPTION
FROM PRODUCT WHERE PRODUCT_NAME LIKE '%{pname}%'
with pname set to X' UNION SELECT USERNAME, 0, PASSWORD FROM USER -- gives:
SELECT PRODUCT_NAME, PRICE, DESCRIPTION
FROM PRODUCT WHERE PRODUCT_NAME LIKE '%X' UNION SELECT USERNAME, 0, PASSWORD
FROM USER -- %'
Three separate obstacles had to be cleared, and each is visible in the payload:
| Obstacle | Handled by |
|---|---|
| The value sits inside a quoted string | the leading ', which terminates it early |
The value sits inside a LIKE pattern, so a trailing %' is waiting |
the trailing --, which comments the remainder away |
UNION demands matching column count and types |
three columns supplied, with 0 — a number — placed in the slot that lines up against PRICE |
That middle 0 is the detail worth remembering: it isn't data the attacker wants, it's padding of the right type. Swap it for a username and many databases reject the whole statement on a type mismatch, and the attack silently fails.
The generalisable point: exploiting injection is less about knowing SQL than about reconstructing the statement you have been dropped into — its quoting, its shape, what follows your value. It also explains why blocking one payload string achieves nothing: the same attack rewrites itself around whatever the surrounding query happens to look like.