One More Thing
I always try to impart on people that SQL injection isn’t necessarily about vandalizing or trashing data in some way.
Often it’s about getting data. One great way to figure out how difficult it might be to get that data is to figure out who you’re logged in as.
There’s a somewhat easy way to figure out if you’re logged in as sa.
Wanna see it?
SELECT CONVERT(INT, SUSER_SID());
DECLARE @Top INT = 1000 + (SELECT CONVERT(INT, SUSER_SID()));
SELECT TOP (@Top)
u.Id, u.DisplayName, u.Reputation, u.CreationDate
FROM dbo.Users AS u
ORDER BY u.Reputation DESC;
It doesn’t even require dynamic SQL.
All you need is a user entry field to do something like pass in how many records you want returned.
The results of the first three selects looks like this:
This is always the case for the sa login.
If your app is logged in using it, the results of the TOP will return 1001 rows rather than 1000 rows.
If it’s a different login, the number could end up being positive or negative, and so a little bit more difficult to work with.
But hey! Things.
Be mindful of those input fields.
Lots of times, I’ll see people have what should be integer fields accept string values so users can use shortcut codes.
For example, let’s say we wanted someone to be able to select all available rows without making them memorize the integer maximum.
We might use a text field so someone could say “all” instead of 2147483647.
Then uh, you know.
Out comes ISNUMERIC and all its failings.
Thanks for reading!