Now suppose the hacker enters the same user name and password as before. Then the SQL statement becomes this:
SELECT COUNT (*) FROM Passwords
WHERE UserName='X'' OR ''a''=''a'
AND Password='Y'' OR ''b''=''b'
This query looks for records where:
UserName: X' OR 'a'='a
Password: Y' OR 'b'='b
It's pretty unlikely that those values are in the database.
(As I mentioned before, I think this spoils the attack. Let me know if you discover another attack that works.)
An alternative is to build the query as a parameterized command and set the user-entered values as parameters. Then the database includes any quotes that the user entered in the field values.
A second alternative is to simply remove any single quotes from the user name and password before using them. That is a bit simpler but prevents the user from using quotes in the user name and password.
The same techniques apply to every field that you will use in an SQL statement. For example, suppose a legitimate user is logged in and wants to find the record for a customer named Sean O'Grady. The single quote will mess up the query giving something like this:
SELECT * FROM Customers WHERE Name='Sean O'Grady'
The database thinks the name is "Sean O" and gets confused by the "Grady'" that comes next.
If you double the quotes, you get this:
SELECT * FROM Customers WHERE Name='Sean O''Grady'
The database knows that two single quotes in a row mean to include a quote in the text value so this query will work.