You can easily prevent this from happening. The first guideline is: NEVER use user input directly in your database queries. You can either use a wrapper function that will filter all harmful items from the user input, or you can use the features provided by the development platform (ex: the SqlParameter object as provided by the .NET framework).
How can you secure the user input? As you can see from the example, I used the single quotes to insert additional SQL code in the query. If you want to use a single quote in an SQL text value, you need to double the single quote.
Example:
O’Maley will be O’’Maley
INSERT INTO users (login, password)
VALUES (‘O’’Maley’, ‘pass’)
So, first thing you need to do is replace all single quotes in the user input to 2x the single quote (do not confuse with the double quotes on your keyboard !). When applied to the query above, the result will be:
SELECT COUNT(login)
FROM usertable
WHERE login = ‘’’ OR 1=1 OR login = ‘’’ AND
password = ‘’’ OR 1=1 OR password = ‘‘’
To enhance visibility, I marked the values as entered by the user, but where the single quotes are doubled, in red. The user input will be considered as text, and will not influence the normal execution of the query. You now enhanced the security of your website by around 95%.
Besides the solution as mentioned in the previous paragraph, there are some additional steps that should be taken to further enhance the security of your website.
- Limit the size of the user input. There is no reason the user should be able to insert a 10 digit value in a database field of length 8. Considering the large amount of buffer overflow based security risks in Windows based software, you can prevent this from happening at this point.
- Newer trust clientside scripting. You may validate user input by using clientside scripting, but you always have to implement a double check in the serverside script. Nothing can prevent a user from saving a webpage, removing the clientside checks, and opening it again
- Always run the SQL code in a database account with the least privileges. There is no need for the account that is used to validate the login, to have write access to the database structure. This prevents a malicious user from executing DROP TABLE… instructions.
- Never expose a database error message to the user. The message contains in many cases security sensitive information about the database structure, and may give the malicious user some extra ideas. Encapsulate database error messages and prevent them by being displayed directly
- Protect pattern matching statements. If you need to use input with the LIKE clause, some characters take on a special meaning for pattern matching. In order to prevent misuse, you need to escape these characters. These are substitutions you’ll have to make on SQL Server:
|
- Replace a single quote by 2x single quote (‘ by ‘’)
- Replace [ by [[]
- Replace % by [%]
- Replace _ by [_] |
