There was a comment here asking if it was a good idea to check the user inputs to block certain characters as a way to prevent sql injection. They deleted their comment before I could reply, but...
There was a comment here asking if it was a good idea to check the user inputs to block certain characters as a way to prevent sql injection.
They deleted their comment before I could reply, but someone else that comes through later might have the same question so I'll leave it as a top level comment.
You'd want to check that stuff when creating an account (unless you want users with zalgo text as their name), but for just logging in there's no need to do those checks.
In the case of having a sql injection vulnerability, doing those checks on every query would help, but the issue is still the injection itself.
For example, if you had a commenting system, you can't just disallow special characters unless you want really restricted comments.
Even then, doing those checks for every single input is a lot more effort than just using prepared statements in the first place.
In fact, many (most?) modern database libraries use prepared statements by default and would require you to manually build a raw sql string to bypass it.
I love this. Simple and straight forward. A lot of beginner guides fails at simplicity and sudden leaps in knowledge. N00b question: Is the ’a’ OR just for the example or is it necessary? Couldn't...
I love this. Simple and straight forward. A lot of beginner guides fails at simplicity and sudden leaps in knowledge.
N00b question:
Is the ’a’ OR just for the example or is it necessary? Couldn't you just write 1=1;?
Edit: I love that one comment on the article just completely misses the audience and amplifies the difficulties in teaching these things. Of course there are other factors to consider in this example but that's not relevant here but people just can't help themselves.
If you just did 1=1; the final query would be SELECT * FROM users WHERE username='1=1;' and password='RandomPass';. This would cause it to search for a named 1=1; with a password of RandomPass...
If you just did 1=1; the final query would be SELECT * FROM users WHERE username='1=1;' and password='RandomPass';.
This would cause it to search for a named 1=1; with a password of RandomPass instead of returning all of the results.
Here's how the example of a' OR 1=1;-- from the article breaks down:
a' closes the string
OR 1=1 if the previous comparison fails, it evaluates an always true expression, so it will return all users
; ends the current statement
-- comments out the rest of the line
The actually important parts are:
closing the string
ending the current statement
commenting out the rest of the line
Putting that together you can then run arbitrary sql in between to give the results you want.
In this example the query is for authentication.
If someone entered a username of elcuello';--, the final query would be SELECT * FROM users WHERE username='elcuello';--' and password='RandomPass'; and they would be logged in as you.
They could also do something like elcuello'; update users set password='NewPassword' where username='elcuello';--, the final query would be SELECT * FROM users WHERE username='elcuello'; update users set password='NewPassword' where username='elcuello';--' and password='RandomPass'; and your password would be changed.
Since they didn't bother adding another select statement after the update, the query wouldn't return a user and the login would fail.
That's not an issue though since they can just login normally with the password they set.
There was a comment here asking if it was a good idea to check the user inputs to block certain characters as a way to prevent sql injection.
They deleted their comment before I could reply, but someone else that comes through later might have the same question so I'll leave it as a top level comment.
You'd want to check that stuff when creating an account (unless you want users with zalgo text as their name), but for just logging in there's no need to do those checks.
In the case of having a sql injection vulnerability, doing those checks on every query would help, but the issue is still the injection itself.
For example, if you had a commenting system, you can't just disallow special characters unless you want really restricted comments.
Even then, doing those checks for every single input is a lot more effort than just using prepared statements in the first place.
In fact, many (most?) modern database libraries use prepared statements by default and would require you to manually build a raw sql string to bypass it.
I love this. Simple and straight forward. A lot of beginner guides fails at simplicity and sudden leaps in knowledge.
N00b question:
Is the
’a’ ORjust for the example or is it necessary? Couldn't you just write1=1;?Edit: I love that one comment on the article just completely misses the audience and amplifies the difficulties in teaching these things. Of course there are other factors to consider in this example but that's not relevant here but people just can't help themselves.
If you just did
1=1;the final query would beSELECT * FROM users WHERE username='1=1;' and password='RandomPass';.This would cause it to search for a named
1=1;with a password ofRandomPassinstead of returning all of the results.Here's how the example of
a' OR 1=1;--from the article breaks down:a'closes the stringOR 1=1if the previous comparison fails, it evaluates an always true expression, so it will return all users;ends the current statement--comments out the rest of the lineThe actually important parts are:
Putting that together you can then run arbitrary sql in between to give the results you want.
In this example the query is for authentication.
If someone entered a username of
elcuello';--, the final query would beSELECT * FROM users WHERE username='elcuello';--' and password='RandomPass';and they would be logged in as you.They could also do something like
elcuello'; update users set password='NewPassword' where username='elcuello';--, the final query would beSELECT * FROM users WHERE username='elcuello'; update users set password='NewPassword' where username='elcuello';--' and password='RandomPass';and your password would be changed.Since they didn't bother adding another select statement after the update, the query wouldn't return a user and the login would fail.
That's not an issue though since they can just login normally with the password they set.
Thank you for writing this.