Click here to Skip to main content
11,482,402 members (74,798 online)
Click here to Skip to main content

Is Your Data Valuable To You?

, 17 Apr 2006 CPOL 22.6K 47 20
Rate this:
Please Sign up or sign in to vote.
The article discusses threats to application in the absence of proper validation procedure.

Is Your Data Valuable to You?

“June 17, 2005, in what could be the largest data security breach to date, MasterCard International on Friday said information on more than 40 million credit cards may have been stolen. Of those exposed accounts, about 13.9 million are for MasterCard-branded cards, the company said in a statement. Some 20 million Visa-branded cards may have been affected and the remaining accounts were other brands, including American Express and Discover.The breach occurred at CardSystems Solutions in Tucson, Ariz., a third-party processor of payment data, according to a MasterCard statement. An intruder was able to use security vulnerabilities to infiltrate the CardSystems network and access the cardholder data, MasterCard said.”[CNET News.com]

The above example is one of the several security breaches of recent years.

In this article, I am discussing how in the absence of proper data validation and sanitization, through application how a malicious user can easily play with the contents of a database.

How Application is Under Threat?

A hacker enters a malformed SQL statement into the textbox. This entered data is then used in SQL query against the database. If data is not sanitized before directing it to the database in the form of SQL query, it may insert, modify or even delete the database contents.

An SQL–Injection attack can be well explained with the help of an example.

The Windows application consists of two forms, one for user validation, and the second is for displaying employee’s salary.

The database is SQL Server 2000,.There are two tables in the database.

Sample screenshot
Figure 1. tblUsers table which stores Usernames and Password.


Sample screenshot
Figure 2.tblSalary table which stores employee salary details

Good User

Consider a normal user who happens to be an employee of the organization. To check for the salary details, he/she will login through the following form:

Sample screenshot
Figure 3. User login through the form

In this case, the formed SQL query would be:

SELECT COUNT (1) FROM tblusers where UserName='Ujwal' AND password='watgule'

Now as Ujwal is an authorized employee of an organization, he would be directed to the form which will make him able to check his salary details.

Sample screenshot

Bad User

Now a user has entered the following data in the login form as shown below:

Sample screenshot

Now the formed SQL query is:

SELECT COUNT (1) FROM tblusers where UserName='' OR 1=1 --'AND password=''

Instead of comparing the user-supplied data with that present in the tblUser table, the query compares a quotation mark (nothing) to another quotation mark (nothing). This is always return True. In SQL ‘--’ is considered as comment. As bad user has entered ‘--’ in the User Id textbox ,the part of the query which checks password entry get commented : --'AND password=''. Thus the SQL query with the entered data will always return more than one record count and will direct the bad user to the salary details page.

Worst User

Now consider worst user who has more knowledge about SQL language. Our previous user i.e. bad user was only able to view other’s salary details. But this Worst User can Insert, Update, Delete the salary records of the employees.

A worst User can enter ‘ OR 1=1—in the TextBox of Employee No. and thereby can view all Employee’s Salary details as shown below:

Sample screenshot

Insertion of Record

Now consider the following malformed statement entered by the worst user:

Sample screenshot

The SQL query formed is:

SELECT * FROM tblSalary where EmployeeID='' OR 1=1; 
INSERT INTO tblSalary (EmployeeID, EmployeeName, Salary, _
    IncomeTax, ProfessionalTax, HRA) VALUES (5,'xyzBad','$70,000', 0, 0, 0)--'

In SQL we can execute multiple queries separated by semicolon (;).

As shown in the above query, worst user has added a new record in the Salary table as shown below:

Sample screenshot

Updating a Record

Now consider the following statement entered by the worst user:

Sample screenshot

The SQL query formed is:

SELECT * FROM tblSalary where EmployeeID='' OR 1=1; 
    Update tblSalary SET Salary='$ 0 ' WHERE EmployeeID =1--'

Thus worst user has changed the salary of Employee no. 1 to $ 0.

Deleting a Record

Now consider the following statement entered by the worst user:

Sample screenshot
SELECT * FROM tblSalary where EmployeeID='' OR 1=1; 
    Delete FROM tblSalary WHERE EmployeeID='1'--'

The worst user has deleted a record of Employee No. 1 by inserting a DELETE query in the Employee no. TextBox.

What Can Be Done?

To protect our application (whether it’s Windows application or Web-based application) from SQL Injection attack, we need to be very much selective about client-supplied data. Every character entered by the user needs to be validated and sanitized.

As far as our EmployeeSalary application is concerned, the user is entering data at two places:

  1. One at the time of authorization in the form of user name and password.

    We know that normally a user name consists of lowercase characters, uppercase characters, single quotes or numbers. Apart from these characters, we should not accept other user supplied characters.

    In the application, we have a function ValidateData(). This function checks for malicious characters entered by the user.

    For intCount = 0 To strUserName.Length - 1
    If strUserName.Substring (intCount, 1) = ";" Or _
    strUserName.Substring (intCount, 1) = "=" Or _
    strUserName.Substring (intCount, 1) = "-" Then
    blnFlag = False
    End If
    Next
    
    For intCount = 0 To strPassWord.Length - 1
    If strPassWord.Substring(intCount, 1) = ";" Or _
    strPassWord.Substring(intCount, 1) = "=" Then
    blnFlag = False
    End If
    Next
    If blnFlag = False Then
    Return False
    Else
    Return True
    End If
  2. Second at the time of displaying Salary Details where the user enters Employee number.

    We know that Employee no. consists of only numbers. Apart from numbers, we should not accept other user supplied characters.

    In the application we have a function ValidateData(). This function checks for malicious characters entered by the user.

    For intCount = 0 To strEmpNo.Length - 1
    If strEmpNo.Substring (intCount, 1) = ";" Or _
    strEmpNo.Substring (intCount, 1) = "=" Or _
    strEmpNo.Substring (intCount, 1) = "-" Then
    blnFlag = False
    End If
    Next
    If blnFlag = False Then
    Return False
    Else
    Return True
    End If

How to make Your Application More Secure?

The first and foremost step of protecting your application from any outside threat is to code securely. Immense care should be taken while designing the application. We must adhere to the Software Engineering principles while coding and should never compromise.

Some of the suggested measures against SQL Injection are:

  1. Validate and sanitize all user input.
  2. Never use Dynamic SQL query.
  3. Execute application using a less privilege account (Make sure in App.config file, you are not using a default account type, i.e. sa).
  4. Wherever possible, encrypt data which you think sensitive.
  5. Avoid comments (--) in user supplied data.
  6. Disallow batch execution of queries.
  7. Disallow sp_password function, because if it used in SQL query, the query does not appear in the SQL log.
  8. Replace single quote (‘) with double quotes (“).

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ujwal Watgule
Team Leader
India India
Hello Reader, This is Ujwal working with a consultancy firm. I develope applications in Microsoft .NET Technologies.

Comments and Discussions

 
GeneralExcellent Article Pin
XclusiveGuy10-May-07 0:28
memberXclusiveGuy10-May-07 0:28 
GeneralParameterised queries Pin
Graham N9-Apr-06 7:24
memberGraham N9-Apr-06 7:24 
GeneralRe: Parameterised queries Pin
Ujwal Watgule12-Apr-06 18:52
memberUjwal Watgule12-Apr-06 18:52 
GeneralRe: Parameterised queries Pin
Graham N13-Apr-06 10:12
memberGraham N13-Apr-06 10:12 
GeneralRe: Parameterised queries Pin
computerguru9238223-May-06 15:27
membercomputerguru9238223-May-06 15:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 17 Apr 2006
Article Copyright 2006 by Ujwal Watgule
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid