SQL injection: Developers fight back
Anurag Agarwal
Rating: -3.78- (out of 5)
|
SQL injection is one of the newer attacks aimed at Web application security in the cybercrime world. It is targeted at your database, which stores sensitive information of your employees or customers. This type of attack exploits vulnerabilities in your application to access your database by manipulating the SQL queries in your application via the input boxes of your Web form.
A lot has already been written on how SQL injection attacks are done, so I am not going to go into that. I have some links at the bottom for you to get more information on how this attack works.
What I want to do is take a look at 10 steps you can take to prevent applications from being vulnerable to this type of attack.
- Install the database on a different machine then a Web server or application server. Make sure all the latest patches are applied.
- The database should have all the default accounts and passwords disabled, including the superuser account. Create a new account under a different name with similar privileges if required.
- Create an application user account in your database that has minimum privileges in order for that application to access the data. Remove all the sample tables. Disable or delete any system stored procedure that is not required by that application user.
- Identify the list of SQL statements that will be used by the application and allow only those SQL statements from the application, such as Select, Insert, Update, etc.
- Use read-only views for SQL statements, which do not require any inserts or updates, e.g. Authentication SQL.
- Use stored procedures instead of SQL wherever possible.
- Employ proper error handling and logging within the application so that a database error or any other type of technical information is not revealed to the user. ...
To continue reading for free, register below or login
To read more you must become a member of SearchSoftwareQuality.com
- Choose names for tables and fields that are not easy to guess. For example, fieldname of an e-mail address could be "x_eml" or "olb_mail_addr" and the name of the table could be app_usr_det.
- Use dynamic queries instead of static queries. For example, in Java, use PreparedStatement instead of Statement object.
- Sanitize the input by validating it in your code. I will categorize the input in two categories:
- Authentication input, such as username and password
- Other form field input, such as registration, forgot password, feedback and suggestions
Input validation
Let's explore input validation further. Usually, any Web application has some input boxes to collect data from the user. When a Web form is submitted, the input values are submitted to the application running at the server. The application should validate the input before processing it. Certain steps can be taken to protect the application from malicious input.
Authentication input: Authentication being the entry point of the system is a more sensitive area, and the likelihood of an attacker trying SQL injection here is extremely high. Moreover, if authentication is compromised, then the attacker has unauthorized entry into your system. Some of the techniques we can implement here include the following:
- Validate the username and password fields when they are entered into the system as shown in the example below.
- Use a read-only view in the database to authenticate a user with the username and password stored in the database. That would prevent an attacker from making any modifications in the database to gain entry into the system.
- Encrypt the username and password in the database and validate them using one-way encryption. If an attacker tries to inject a query in the username or password boxes and somehow manages to bypass the input validation techniques, the input values when encrypted and verified against the values stored in the database will not match.
- Log every successful or failed login attempt. This log will help not only in establishing a pattern of attack, but also in identifying any new techniques adopted by the attacker. This later can be used as an input to prepare a security patch for the system, if the system is still vulnerable to that type of attack.
Other form field input: Let's take a look at some of the common form fields that are usually available before authentication and what can you do to prevent SQL injection attacks in those fields.
- Forgotten password:
- Usually applications ask for only the e-mail address. Some of them ask a challenge question and answer for further validation before sending the password to the user via e-mail. This helps attackers guess the e-mail address. If you instead ask for e-mail address, first name and last name, the applications make it difficult for the attacker to guess a valid user.
- Validate the input as shown in the example below.
- If any of the information provided did not match, display a generic error message, such as "Your information did not match" as opposed to "Your e-mail did not match". This will keep the attacker guessing as to what did not match.
- Registration page: This page usually consists of multiple fields. Apply the appropriate validation techniques for different fields based on the sample code below.
- Feedback and suggestions: If this is stored in the database, then it should be stored as a CLOB. Using PreparedStatement and storing it as a CLOB will protect it from most types of attacks.
- Contact Us: If there is no reporting or tracking requirement for this information, then architects should avoid storing this information in the database. Accept the form fields and send them as an e-mail. That gives you one less area to protect from SQL injection.
Input validation techniques
Ideally, architects can define in the design phase what type of input is expected (alphabets, numeric, alphanumeric) and what characters are allowed for that type of input. Let's take a look at an example with sample code in Java.
Username validation -- The following rules will apply:
- It can contain the following alphabets a-z, A-Z, 0-9, _,
- It is a single word and hence no spaces allowed
- Maximum characters allowed -- 30
- No escape characters allowed
Sample code:
For numeric input, convert it to integer as well before appending into your SQL query. If it throws a NumberFormatException, then it is not a valid input.
It might get a little tedious to define rules for all the individual fields. In such scenarios, architects can divide the fields into multiple categories, such as single word, multiple words, multiple words with punctuations, e-mail address, numeric input, etc., and define rules for those categories.
As you can see, by taking certain measures, you can avoid common vulnerabilities and create more secure Web applications. After all, every step, however small, is a step forward in securing your application.
More information:
List of SQL injection attacks by example
List of escape characters
-------------------------------
About the author: Anurag Agarwal, CISSP, works for a leading software solutions provider where he addresses different aspects of application security. You may e-mail him at anurag.agarwal@yahoo.com.
|
|
|
|
|
|
