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.
Figure 1. tblUsers table which stores Usernames and Password.
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:
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.
Bad User
Now a user has entered the following data in the login form as shown below:
Now the formed SQL query is:
SELECT COUNT (1) FROM tblusers where UserName='' OR 1=1
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:
Insertion of Record
Now consider the following malformed statement entered by the worst user:
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:
Updating a Record
Now consider the following statement entered by the worst user:
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:
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:
- 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
- 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:
- Validate and sanitize all user input.
- Never use Dynamic SQL query.
- Execute application using a less privilege account (Make sure in App.config file, you are not using a default account type, i.e. sa).
- Wherever possible, encrypt data which you think sensitive.
- Avoid comments (--) in user supplied data.
- Disallow batch execution of queries.
- Disallow
sp_password function, because if it used in SQL query, the query does not appear in the SQL log.
- Replace single quote (‘) with double quotes (“).
