Hey, fellow SQL enthusiasts! Have you ever stumbled upon an SQL injection query that seemed to make sense, but just wouldn’t work? I recently encountered this issue while practicing PortSwigger’s lab on blind SQL injection with conditional responses.
The query in question was `SUBSTRING((SELECT password FROM users WHERE username=’administrator’), 1, 1)`. I thought it was a clever way to extract the administrator’s password, but to my surprise, it didn’t work at all.
The solution provided by PortSwigger, however, was `(SELECT SUBSTRING(password, 1, 1) FROM users WHERE username=’administrator’)`. At first glance, both queries seemed almost identical to me, but only the second one worked.
So, what’s the difference between `SUBSTRING((SELECT …))` and `(SELECT SUBSTRING(…))`? It turns out that the order of operations matters in SQL. When you use `SUBSTRING((SELECT …))`, the database first evaluates the inner `SELECT` statement, which returns a set of values. Then, the `SUBSTRING` function is applied to that set, which doesn’t make sense in this context.
On the other hand, `(SELECT SUBSTRING(…))` applies the `SUBSTRING` function to each individual value returned by the `SELECT` statement. This subtle difference makes all the difference in the world.
I hope this explanation helps clarify things for you. If you’re interested in learning more about SQL injection and how to protect against it, I’d be happy to share some resources.