The module for this lesson is still under development. Please contact us if you have any questions.

 

SQL Injections –CS


1. Read Background
2. Execute Lab Assignment
3. Complete Security Checklist
4. Answer Discussion Questions

top

Summary:

SQL injection is an attack technique that exploits a security vulnerability occurring in the database layer of an application .  Hackers use injections to obtain unauthorized access to the underlying data, structure, and DBMS.  It is one of the most common web application vulnerabilities.


Description:

A Database is the heart of many, if not all, web-applications and is used to store information needed by the application, such as, credit card information, customer demographics, customer orders, client preferences, etc.  Consequently, databases have become attractive and very lucrative targets for hackers to hack into. SQL Injections happen when a developer accepts user input that is directly placed into a SQL Statement and doesn’t properly validate and filter out dangerous characters. This can allow an attacker to alter SQL statements passed to the database as parameters and enable her to not only steal data from your database, but also modify and delete it.

A database is vulnerable to SQL injections when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed.  SQL injection attacks are also known as SQL insertion attacks.

Injection vulnerabilities, such as SQL, LDAP, HTTP header injection and OS command injection, have been ranked number one on the OWASP (Open Web Application Security Project) Top 10 Web application vulnerabilities 2010 and the top 25 Most Dangerous Software Errors 2011.

Risk: How can it happen ?

SQL injection attacks occur when a web application does not validate values received from a web form, cookie, input parameter, etc., before passing them to SQL queries that will be executed on a database server.  This will allow an attacker to manipulate the input so that the data is interpreted as code rather than as data.

SQL injection attack risk is usually very high and the consequences are severe.  A successful attack can bypass authentication and authorization to gain full control of the database, steal sensitive data, change users’ passwords, retrieve users’ credential information, add non-existent accounts, drop tables, make illegal financial transactions, and destroy the existing database, and a lot more.  The following table summarizes SQL injection examples which result in different types of threats.

 

 

Types of Threat SQL Injection Examples
Spoofing
  • Retrieve and use another user’s credentials
  • Modify Author value for messages
Tampering
  • Modify product stock informations
  • Change any other data in the database
Repudiation
  • Delete transaction records
  • Delete database event logs
Information disclosure
  • Obtain saved credit card numbers
  • Gain insight into internal design of app
Denial of service
  • Run resource-intensive SQL queries
  • Kill sqlservr.exe process
Elevation of privilege
  • Retrieve and use administrator credentials
  • Run shell commands


Example of occurrence:

In 2011, the hacker group LulzSec used Security Injection attacks to cause databases to spit out user names  and passwords from Websites, including one associated with F.B.I.’s InfraGard program, SonyPictures and NATO’s online bookstore and deface the PBS site.

http://www.pcworld.com/article/231303/lulzsec_anonymous_hacks_were_avoidable_report_says.html

Examples:

A dynamic SQL statement is constructed during execution time.  Consider the following example written in .NET, where the input is provided by the user.


Query= "SELECT * FROM  users WHERE username  = ‘ " +request.getParameter("input")+ " ' " ;


Below is the statement that this code builds:

SELECT * FROM  users WHERE username  = ‘input’


SQL Manipulation Attacks:

The most common SQL injection is SQL manipulation where the attacker attempts to modify an existing SQL query statement, and insert exploited statement into the database.

SELECT *
FROM Users
WHERE loginName = ‘ $user ‘ - -
AND loginPassword =  ‘ $password ‘


SELECT * FROM Users WHERE  loginName = ‘ $user ‘ and loginPassword = ‘ $password’


What if user enters:

$user = ‘ OR ‘1’ = ‘1 
$password = ‘ OR ‘1’= ‘1

Since 1=1 is always true, the query will succeed and the attacker bypass authentication.  Similar attacks can be conducted for numeric fields for which we don’t include quotes.

Another variation of SQL manipulation attack is to insert two consecutive dashes (–) or # in MySQL which comments out anything after it.

SELECT *
FROM Users
WHERE loginName = ‘ John ‘ - -
AND Password = anything


SELECT *
FROM Customers
WHERE AccNumber = 1 OR 1 = 1 #
AND Pin = anything


The UNION operator has also been used to conduct SQL manipulation attack.The UNION operator combines the result of two or more SELECT statements.  The result sets should have the same number of attributes (columns) with similar data types and same order. An attacker can use the UNION operator to obtain the values of fields from tables that don’t have anything to do with the data being queried.

SELECT Title, Author, Publishing
FROM Books
WHERE ISBN = ‘ ‘
UNION ALL SELECT Price, Quantity
FROM Pricing_Table
WHERE	‘ ’ = ‘ ‘

Note: the NULL value can be converted to any other data type.

Code Injection Attack:

Another type of SQL injection attack, called code injection attack, is done by appending SQL statement or executable commands right after vulnerable SQL statement.

SELECT *
FROM Users
WHERE UserName = ‘John’ AND Password = ‘myPassword’; DROP TABLE users;


Or we can create a backdoor to the database by inserting a new record in the user table which would allow a hacker to access the database legally.

SQL injection attacks can be also accomplished via function calls, which are called function call injections. The attacker inserts malicious function calls or dynamic procedures into vulnerable SQL statements.  For example, Microsoft’s SQL Server 7, 2000 and 2005,  supports a stored procedure xp_cmdshell that permits what amounts to arbitrary operating system command execution; access is usually limited to administrative accounts. An attacker can append the following function call to a vulnerable statement to stop the server with no output sent to the client:

 EXEC  master..xp_cmdshell ‘net stop sqlserver’, no_output

Or simply look at the executable files in the admin directory:

 EXEC master..xp_cmdshell ‘dir *.exe’

Error Messages:

We can also mine useful information from error messages generated by the database server and use it to retrieve data.The very first task for a hacker is to identify the database platform in order to design and plan its attacks specific for that platform.For example the following error message can reveal a lot of useful information for a hacker.

  >> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.id' is invalid in the select list because it is not
 contained in an aggregate function and there is no GROUP BY clause.


This is also known as Database foot printing.


Advanced SQL injection:

This type of attack, also known as second order SQL injection, is less direct as compared to injection of malicious code into parameters of a dynamically built SOL statement, where the attacker receives the desired results immediately.  Here an attacker injects malicious code into strings which are stored in a table in the database and not immediately executed.  This type of attack occurs because it is often thought that if the data is stored in the database is clean and no additional validation is necessary. When the stored strings are subsequently passed to dynamically SQL commands, the malicious code is executed.

For example:

An attacker might register as:

Username: admin’–

Password: Password

The system escapes the single code and inserts it as:

INSERT  into USERS values( 123, 'admin' '--', 'password')

Given the user name Admin’ – – , an attacker can become the admin and change the password:

UPDATE USERS set password=‘password’ where username = ‘admin’ - -’


How can I avoid Injection problems ?


  • Limit user access:
    • Follow the principle of least privileges which will in turn limits the scope of an SQL Injection attack
    • The front-end public interface to a website should have a restricted database access.
    • Never ever connect as system administrator, if compromised, potential damage is unlimited
  • Limit user input length and data types:
    • Restrict the length of input from text boxes and forms fields
    • Restrict user input to the length of the corresponding field in the database
    • Verify data types
  • Quoting the input:
    • Replace single quotes with two single quotes in the user input, so the single quote is escaped and makes the SQL statement invalid.
    • Filter out character like slash, back slash, semi colon, extended character like NULL, carry return, new line, etc, in all input from users
  • Implement proper error handling:
    • Error messages often reveal technical details that can enable an attacker to identify vulnerable entry points
    • Implement error handling and output error messages that do not provide technical details to the attacker
  • Use Prepared/Parameterized Queries:
    • Parameterized queries are predefined statement and the intent of the parameters cannot be changed based on the user input.
    • The database using parameterized queries distinguishes between data and code and string inputs are treated as strings no matter what data is entered by the user
  • Use Stored Procedures:
    • The SQL code for the stored procedures are defines and stored in the database itself and input parameters are passed to the procedures.
    • Stored procedures should not include any dynamically generated queries.
    • Disable the stored procedures like master..XP_cmdshell, xp_startmail, etc. if you are not using them.

Parameterized queries are most secure against SQL injection attacks.


top

Laboratory Homework Assignment:

1.OWASP WebGoat is a great web security teaching tool and a deliberately insecure web application. For this assignment, you need to install WebGoat 5.2,

http://www.owasp.org/index.php/Category:OWASP_WebGoat_Project OR http://code.google.com/p/webgoat/downloads/detail?name=readme-5.2.txt&can=2&q=

Consult the WebGoat user and Install Guide, available from the first WebGoat link above, for installation instructions. You’re free to install it on the operating system of your choice. After you unpack WebGoat, the readme.txt file will explain how to start it and access WebGoat through your web browser.  Make sure that you take your computer off the Internet before using WebGoat.


WebGoat consists of a variety of lessons. For this assignment please complete the following lessons:

1.Numeric SQL Injection
2.String SQL Injection
3.LAB: SQL Injection

You also need to install WebScarab which is basically an intercepting proxy.

http://www.owasp.org/index.php/Category:OWASP_WebScarab_Project


top

Security Checklist:

Below we discuss several methods for reviewing source code for SQL injection.

Security Checklist

Vulnerability: SQL Injections Course: Database
Task – Check each line of code displaying on the web page Completed

1. Identify tainted data:  Data received by an application from an untrusted source is referred to as tainted data.  Tainted data can originate from a web from, cookie, input parameter, etc.  For example, in the following line of PHP code, which accepts its input dynamically, is vulnerable to SQL injection.

$result = mysql_query(“SELECT * FROM table WHERE column = ‘$_GET[“param”]’”);

Even the following code is potentially vulnerable as we don’t know whether param has been verified.

$result = mysql_query(“SELECT * FROM table WHERE column =  ‘$param”);

In these cases identify, the entry point in the application where param has been assigned a value.


2. Identify stored procedures and check the parameters to ensure tainted data is not passed directly to the procedure. Stored procedures are also prone to SQL injection vulnerability, if they are incorrectly constructed and accept user-controlled tainted data as parameters without validating.
3. Use whitelist approach to validate your input, i.e. reject input that doesn’t match your list of safe characters to accept. Filter out character like ‘/’, ‘\’, ‘;’ ‘- – ‘ , extended character like NULL , carriage return, new line. We still need to deal with single quotes when requires, such as in names.
4. Perform an effective source code review and identify security-sensitive functions for potential vulnerabilities. Examples of dangerous function include execute( ), which executes the given SQL statement;mssql_query( ) , which sends a query to the currently active database; and prepareStatement( ), which creates a precompiled SQL statement and stores it in an object.
Highlighted areas in the application may be vulnerable to  SQL injection vulnerability !


top

Exercise:

1.The following stored procedure (in MS SQL) is used to get detailed information about a product.  The product name passed by the user is stored in @productname variable.  Using the checklist above identify whether the code is vulnerable to SQL injection.

CREATE PROCEDURE SP_ProductSearch @prodname varchar(400) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT ProductID, ProductName, Category, Price ' + ' FROM Product Where ProductName LIKE ''' +
 @prodname + ''''
EXEC (@sql)

2.Using the checklist determine whether  the following stored procedure written in MySQL is more secure than the stored procedure in Exercise 1.  Explain your answer.

CREATE PROCEDURE SP_ProductSearch (input varchar(400))
BEGIN
		SET @param = input;
SET @sql =  concat (' SELECT ProductID, ProductName, Category, Price FROM Product Where  ProductName = ', @param);
 		PREPARE stmt FROM @sql;
		EXECUTE stmt;
END

3.Cookie is a data file that a web site puts on the client’s machine maintaining specific information about the user, such as preferences in the web site and possibly username and password.   Cookies are sent to the user’s web browser and they are automatically sent back to the server in each request.  Using the checklist above determine whether cookies can be used by hackers to conduct SQL injection attacks?

Discussion Questions:

  1. What are some examples of web applications that connect to a database server to access data?
  2. What is the impact of SQL injection vulnerabilities?
  3. What is the main reason for the existence of SQL injection vulnerability?
  4. Is any web programming language immune to SQL injection?
  5. Can SQL injection be prevented?
  6. Is it easy to detect that a database which is vulnerable to SQL injection has been hacked?
  7. If we implement source code analysis into the development life cycle of our application, will our software be secure?


 
Copyright © Towson University