Tip

SQL injection: Developers fight back

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

    Requires Free Membership to View

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.

  1. Install the database on a different machine then a Web server or application server. Make sure all the latest patches are applied.
  2. 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.
  3. 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.
  4. 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.
  5. Use read-only views for SQL statements, which do not require any inserts or updates, e.g. Authentication SQL.
  6. Use stored procedures instead of SQL wherever possible.
  7. 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.
  8. 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.
  9. Use dynamic queries instead of static queries. For example, in Java, use PreparedStatement instead of Statement object.
  10. 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:

  1. Validate the username and password fields when they are entered into the system as shown in the example below.
  2. 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.
  3. 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.
  4. 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.

  1. Forgotten password:
    1. 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.

    2. Validate the input as shown in the example below.

    3. 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.

  2. Registration page: This page usually consists of multiple fields. Apply the appropriate validation techniques for different fields based on the sample code below.
  3. 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.
  4. 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:

  1. It can contain the following alphabets a-z, A-Z, 0-9, _,
  2. It is a single word and hence no spaces allowed
  3. Maximum characters allowed -- 30
  4. No escape characters allowed

Sample code:

    public boolean validateSingleWord(String word)   {
        //Rule 1 and 2 as mentioned above.
        String charactersAllowed = "abcdefghijklmnopqrstuvwxyz0123456789_"; 
        
        //Rule 3 as mentioned above.
        if((word.length() <= 0) & (word.length() > 30))
            return false;
        
        //Converting to char array also validates for escape characters. Rule 4
        char[] array = word.toLowerCase().toCharArray();



         //If the word contains any character not mentioned in the Rule 1, then it returns false,    
         //signifying invalid input.
        for(int i = 0; i < array.length; i++)   {
            if(charactersAllowed.indexOf(array[i]) < 0)
                return false;
        }

        //If all the rules are satisfied, then it is a valid input. Accept it.
        return true;
    }

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.

SQL injection resources

Stop SQL injection attacks on applications

Malicious code injection: It's not just for SQL anymore

Injection attacks -- Knowledge and prevention

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.


This was first published in April 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.