One of the most effective methods of preventing SQL injection from being used is to thoroughly validate every input from the user, by identifying all possible meta-characters which could be utilized by the database system and filtering them out. Filters should be in place to remove everything but known good data. An account lockout policy should also be in place to prevent the brute force guessing of passwords.
All validation for security purposes must be carried out within the server side script and not thorough client side authentication - such as JavaScript - as it can easily be bypassed by the user disabling JavaScript in their browser.
When dealing with a numeric input, such as age, telephone number or credit/debit card number the value of the variable should be processed through a specially constructed function to ensure that the data only comprises of numeric characters (and possibly spaces). Similar functions can be constructed to handle other data types such as Dates, Integers and Floats. Alternatively, for some numeric fields such as integers or dates the input method could be through the use of a drop-down selection box. If the input is selected from a dropdown box it would be generated by the source code and no validation will be necessary.
When dealing with string inputs it may be necessary on some occasions to allow the use of specific meta-characters. As an example, the tick should be allowed to be used in the surname filed so names such as O'Conner are accepted. In this case it would be advisable to accept the name and replace the apostrophe with two apostrophes before running it through the query or entering it in the database.
When dealing with all user inputs through text boxes, it is important to restrict the length of the input. All textbox fields should be as short as possible and must be an appropriate length for the data to be entered. By keeping each field as short as possible, the number of characters that an attacker could use to launch a SQL injection is restricted.
One line of defense include the Restriction of Error Messages. Error messages are normally generated in HTML which an attacker will be able to view. The details of all error messages should be logged in database or file on the server and displayed through a dynamically produced error page.
For each query executed within the code of the application, the most limited access rights possible should be attributed to the query itself. As an example, the data from a username and password text box on a login page should be only used in a query configured with code that ensures 'read only' permissions are given. This will prevent the attacker from inserting data into the database from the text box.
Stored procedures are an advanced feature provided by various SQL servers. In addition to providing some protection from SQL injection the use of stored procedures also increases the performance of the website by allowing the web application to compile and run SQL statements in the server itself. When stored procedures are used a number of conditions must be met by the injected code to be effective; the malicious SQL must be in specified structured format, with the correct number of parameters to be successful. The structure and number of parameters can vary greatly upon depending upon programming decisions made by the web developer.
In order to test a web site against an attack it is not necessary to be an expert at SQL injection as there are several software based automated tools available - such as the Web Vulnerability Scanner by Acunetix and SOAtest by Parasoft - which can be used to systematically carry out a range of attacks against including SQL Injection. Automated testing should be carried out on a regular basis and after any major changes to the web-site or server.
SQL Injection attacks present a serious threat to the security of dynamic web-sites and it is essential that adequate countermeasures are taken to prevent such an attack from being successful. In theory, if meta-characters were handled 100 percent effectively the risk of this type of attack through web-browser forms would be eliminated. In reality - if this was the only line of defence - it would be extremely easy for a programming mistake to be made leaving the system vulnerable.
The best approach is to take as many precautions as possible, this is known as the 'defense in depth' principle. A combination of security measures such as; validation, neutralizing or meta-characters, restricting error messages and limiting access rights to the web server can be used to comprehensively protect a web base application against a SQL injection attack. This approach in conjunction with thorough testing as one of the final stages of web development, together with regular testing and security reviews should be sufficient to protect against this SQL injection.
