The advent of the Internet necessitated sharing of documents not only between computers but also between databases, whereby information is exchanged through websites and other means. It also meant that some people would work towards getting means of accessing information fraudulently. As a result, certain criminal activities have emerged, such as hacking and virus creation, in an attempt to get access to the information shared through the Internet. SQL injection is one of the fraudulent techniques used to attack databases through a website. This paper seeks to describe SQL injection, its detection, and ways of protecting a system from it.
According to Cherry (2011), the Internet environment is more than ever under attack from hackers and other malicious threats that aim to access information. Cherry (2011) notes that despite great efforts by security personnel to make the environment more secure, attackers have found a way to the application layer which poses the largest security vulnerability of over 90 percent. In order to protect the Internet environment, security administrators are required to perform detailed software code security analysis while developing or purchasing software to be used on their websites. This requirement has resulted in high expenses in terms of purchasing, deploying and maintaining of a security analysis exercise. Clarke (2009) noted that this equally impairs development of timelines to the extent that service delivery by many organizations is compromised.
According to Ben-Natan (2005), SQL injection represents a subset of unverified user input similar to buffer overflows, which is aimed at convincing an application on a website to execute an SQL code. The code is usually not intended to run on the database that is held on the website. He indicates that the unintended SQL code run causes access to information or placing of irrelevant or disturbing information to the targeted website. SQL injection uses SQL statements in the form of a web entry intended to pass a new rogue SQL command to a database. This activity is aimed at enabling the executor of SQL injection to access the content of a website. In practice, Ben-Natan (2005) observes that the action exploits the vulnerability of security features in software used on a website.
Ben-Natan (2005) further indicates that the vulnerability of the website software during SQL injection is tested when the user input for string literal escape character is incorrectly filtered. It can also happen when the input from the user is not strongly typed and executed in an unexpected manner. In this way, SQL commands are injected from the web form and enter the application of a database. This action changes the content of a database or gives personal database information to the attacker. Carr (2011) observes that SQL injection is widely known as an attack vector and is mostly associated with websites, but can be found in any database raised on SQL command.
Similarly, Nystrom (2007) defines SQL injection as a type of web application security vulnerability, which allows the attacker to submit a database SQL command executed by a web application. This execution exposes the back-end database. He indicates that SQL injection attacks occur when a web application uses data supplied by the user without proper validation and encoding as part of a command or query. In this case, the particularly formed user data makes the application execute unintended commands or entirely changes the data as known by the system. In executing SQL injection, Nystrom (2007) observes that the attacker is allowed to create, read, update, alter, or delete data that is stored in the back-end database. In its common manifestation, Nystrom (2007) indicates that SQL injection gives the attacker the right to access secured information, such as social security numbers, passwords or credit card numbers.
According to Singh, Singh & Joseph (2008), SQL injection involves a number of concepts, which ensures that it is executed successfully by the attacker. For instance, he observes that SQL injection occurs due to to a software vulnerability, which happens whenever the data entered by the user is interpreted as part of an SQL query by an SQL interpreter. In this case, the attacker provides specially crafted input data to trick the interpreter into executing unintended commands. Consequently, Singh, Singh &Joseph (2008) in their findings indicate that this action enables the attacker to take advantage of the vulnerability of the web software by providing false input data in manner that the SQL interpreter is unable to distinguish between the intended commands and the false data from the attacker. The bottom line for execution of SQL injection lies in the security vulnerability at the layer of the database. Singh, Singh &Joseph (2008) note that through this exploitation of an SQL injection flaw, the attacker creates, reads, to changes, or deletes sensitive data.
SQL Injection Attacks
According to Danchev (2010),SQL injection attacks can occur because of the vulnerabilities in the design of queries. In this case, a malicious user exploits input opportunities by inserting codes in the queries so as to modify query-conditions. This action gives them the ability to access databases without authorization of their owners. Danchev (2010) indicates that there are several ways of identifying the occurrence of SQL attacks. One of the ways is based on automatically developing a model for an SQL query statement which allows the capture of dependencies between various sub-queries of the query.
Danchev (2010) notes that SQL injection attacks could be subdivided into three classes. He identifies the in-band class, which involves the extraction of data using similar channel as used in the injection of SQL code. He further observes that this represents the most direct kind of attack, whereby the retrieved data is present in the application layer of the web page directly. Similarly, Danchev (2010) identifies out-of-band class, which involves the retrieval of data under different names. For instance, the attacker can generate an email, whereby the result of the query statement is sent to the tester. Finally, there is the inferential class which does not involve actual transfer of data but enables the tester to reconstruct the information and send particular requests, while observing the behavior of the database server.
Besides the above classes of the SQL injection, Daswani, Kern & Kesavan (2007) observe that a successful SQL injection attack requires the attacker to make a syntactically right SQL query. This is aimed at getting the return message from the application. The message can then be used to reconstruct the logic of the original query, hence the ability to understand how to perform the SQL injection correctly. However, Daswani, Kern & Kesavan (2007) note that in case the application hides the error details, the attacker invokes the tester to reverse the logic of the original query in a case called Blind SQL Injection.
According to Graves (2010), SQL injection occurs when two conditions are prevailing, such as: 1) data is input from an untrustworthy source which enters the program or database 2) entered data is used to dynamically construct an SQL query statement, which fools the actual data. Graves (2010) indicates that SQL injection can be achieved in any common browsers used for accessing the Internet. The attack targets pages displayed by the browser, thus enabling submission of data. Such pages include login pages and search pages among others. Idir & Jochen (2004) suggest that it is common for HTML pages to make use of POST command in sending parameters to a different page. The attacker, therefore, may not see the parameters in Universal Resource Locators. Nevertheless, Idir & Jochen (2004) indicate that in such a situation, the attacker can always check for a source code in HTML, while searching for “FORM” tag contained in the HTML code. He observes that the attacker will probably find something in some HTML codes similar to the following:
<FORM action=Search/search.asp method=post>
<input type=hidden name=A value=C>
In the example above, Idir & Jochen (2004) observe that it is possible to find potential parameters within the <FORM> and </FORM>, which can be targeted for SQL injection. Similarly, Jorden (2007) observes that an attack can be launched by using such web pages as ASP and PHP contained in URL that allow the following entries: http://rush/index.aspp?id=20. The next step in SQL injection is the testing of the vulnerability of the identified page. Jorden (2007) indicates that this can be done by using a single quote, which inputs data that is sent to the identifier. Such input may take a form of hi' or A=A—in URL, password area or login space. For instance, it could take the form of
- Login: HI' or A=A
- Pass: HI' or A=A
- http://rush/index.aspp?id= HI' or A=A
Nielsen & Parui (2011) observe that in some cases, this can be done in a hidden form, hence the attack simply downloads the source HTML code from the targeted website, save it on their hard disks and makes modifications to the code before hiding it according to their targeted information. For instance, Nielsen & Parui (2011) note that a downloaded code can be modified in this form to allow entry without the need for password or login information:
<FORM action=http://rush/Search/search.aspp process=post>
<input type=hidden name=A value=" HI' or A=A --">
Similarly, Oriyano & Gregg (2010) observe that in addition to the above attack process, SQL injection can be achieved by the attacker by listing several passwords from an arbitrary table in any database server. For instance, the attacker could use the code below to achieve a SQL injection;
$query = "SELECT id, name, inserted, size FROM products
WHERE size = '$size'
ORDER BY $order LIMIT $limit, $offset;";
$result = odbc_exec($conn, $query); ?>
In the above input, Oriyano & Gregg (2010) note that the fixed part of the query is combined with another SELECT statement, which enables to reveal all passwords stored in the database. Such SELECT statement can take the form of:
union select '1', concat(uname||'-'||passwd) as name, '1986-08-12', '0' from usertable;--
However, Oriyano & Gregg (2010) note the above SQL injection can be prevented by using strongly encrypted passwords and avoiding using date, month and year of birth in passwords. Oriyano & Gregg (2010) observes that such passwords are highly susceptible to SQL injection, as the probability that someone was born on a particular date and is in the database is very high, even in a randomly selected data.
Another way of effecting a SQL injection is through resetting of passwords. Stuttard & Pinto (2011) indicate that this action gives the attacker more privileges. The attacker poses as the admin of the database and changes the normal query statement; <?php $query = "UPDATE usertable SET pwd='$pwd' WHERE uid='$uid';";
?> to <?php// $uid == ' or uid like'%admin%'; --
$query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like '%admin%'; --";
// $pwd == "hehehe', admin='yes', trusted=100 "
$query = "UPDATE usertable SET pwd='hehehe', admin='yes', trusted=100 WHERE
...;";?> . The attacker easily achieves this by changing the value ' or uidlike'%admin%'; -- to $uid in order to alter the admin's password. The attacker can also set the $pwd to "rerere', admin='yes', trusted=100” leaving a trailing space so as to access more privileges.
Additionally, according to Stuttard & Pinto (2011), SQL injection can be achieved by directly attacking the database hosts operating system, such as the MSSQL Server. This is done by using the following query statements: <?php
$query = "SELECT * FROM products
WHERE id LIKE '%a%'
exec master..xp_cmdshell 'net user test testpass /ADD'--";
$result = mssql_query($query);?>. In this statement the attacker submits the value a%' exec master..xp_cmdshell 'net user test testpass /ADD' -- to $prod, then the $query prompting the generation of a completely different query statement similar to: <?php
$query = "SELECT * FROM products
WHERE id LIKE '%a%'
exec master..xp_cmdshell 'net user test testpass /ADD'--";
$result = mssql_query($query);?>. The logic behind the execution of the above false command by the MSSQL server is that the server executes the SQL statements in the batch together with a command that adds a new user to the local accounts database. To prevent such an execution, Stuttard &Pinto (2011) suggest that MSSQL server should be programmed to run with sufficient privileges to stop the attacker from accessing any account hosted in the database.
Taylor (2011) notes that there various form of SQL injection. For instance he points out a category of SQL injection called the Blind SQL injection, whereby nothing is known about the outcome of the operation. For instance, Taylor (2011) noted that such behavior can occur in a case where the programmer creates a customized error page. This page does not reveal anything about the structure of the query statement or the database. In this case, the page does not return an SQL error but can simply return an HTTP 500 message. In this particular attack, it is possible to use inferential methods so as to avoid a non-return of messages, and, therefore, succeed in accessing values of some desired fields. Sambath & Zhu (2012) observe that this method involves the use of Boolean queries directed to the server, as well as careful observation of the results and deduction of the meaning of such returns.
For instance, Sambath & Zhu (2012) note that it is possible to deduce that a domain like www.example.com will contain a parameter named id which is vulnerable to SQL injection. Therefore, the use of the following request: http://www.example.com/index.php?id=1' will result in revealing a page with a customized message error, which is caused by a syntactic error in the query. Hence it is logical to suppose that the query executed on the server was; SELECT field1, field2, field3 FROM Users WHERE Id='$Id'. This query can be exploited by using various methods with the intention of obtaining values in the username field. The tests executed allow for extraction of values of the username field, extracted in a sequential manner.
Sambath & Zhu (2012) noted that this is possible by using standard functions which are practically presented in every database. For example, Sambath & Zhu (2012) suggest the use of pseudo-functions, such as SUBSTRING (text, start, length), which allows the return of a substring from the position of “start” of text through the “length”. If the “start” is found to be greater than the length of the text, a null value is returned by the function. Another pseudo-function is the ASCII (char), which gives back the value of ASCII in the input character. In this case, a null value is returned if the value of char is 0. Similarly, the pseudo-function LENGTH (text) allows the results of character length to be used in the input text.
Clarke (2009) notes that the above pseudo functions will enable the execution of a test process on the first character at the discovery of the value which is passed on to the second and so forth. This is done until the entire value is finally discovered. The strength of this test for detecting SQL injection lies in the fact that it allows the selection of only one character at a time. This means that a single character selected impose the length of a character to only 1. The ASCII function allows for numerical calculation for comparison. The result of the comparison is done using the values of the ASCII table till the right value is found.
Real-life Cases of SQL Injection
According to Andrews, Litchfield & Grindlay (2003), individual records and information contained in their payment cards are stolen in million numbers on a regular basis. Several released reports point to some main source of breaches, which involve old-fashioned SQL injection together with malware infections. Even as companies apply ever effort to ensure that their networks and employees account are protected against the latest threats, it is notable that more basic threats with simple and average attack by cybercriminal are overlooked.
Andrews, Litchfield & Grindlay (2003) note that according to the investigation report released by the UK Security Breach, 40% of all cyber attack relied on SQL injection. Another 20% used the combination of SQL injection and malware. The report further indicates that the attack did not result entirely from external sources but depended also on the weakness in the application layer which accounted for 86% of the cases reported. It is notable that the majority of affected companies operated in a shared hosting environment. Similarly, Ben-Gan, Sarka, & Wolter (2009) cite Chinese hackers and botnet masters who use massive SQL injection attacks by using public search engines. This is contained in a report entitled Massive SQL Injection Attacks - the Chinese Way; SQL Injection Through Search Engines Reconnaissance.
Danchev (2010) notes that another real-life example of SQL injection is contained in Trustwave’s Global Security Report 2010 which provides insights into the use of SQL injection for obtaining illegal access to information stored on payment cards. According to Danchev (2010), the reports point out an interesting observation, whereby 81% of the reported cases involved the compromising of computers that were managed by a third party, as compared to 13% which were under self-management. Furthermore, Danchev (2010) observes that this report details that most common techniques used to harvest data in transit. The report further indicates that malware attackers relied on memory parsers at 67% cases being reported, keylogging cases at 18%, network sniffing at 9% while 6% of cases involved the use of credential malware commonly known as ATM malware.
Another report by the Poneman Institute titled Cost of Data Breach emphasizes the use of valuable metrics in a cost benefit analysis (Danchev, 2010). This report found out that data breaches due to SQL injection and other forms of malware doubled between 2008 and 2009, with the cost of malicious attack going higher than the cost of breaches due to user negligence or system glitches. Cole (2011) noted that the report had stated that it is important to notify customers or users of a system immediately after an attack occurs.
Detection of SQL Injection
Cole (2011) notes that the initial step in detecting SQL injection is to understand when applications are connecting to a database server during the access of data. For instance, application layer needs to contact the database during the authentication time. In this case, when the authentication is performed using a web form, it is likely that the there is checking of user credentials against the database that contains all sensitive information, such as usernames and passwords. Similarly, Oriyano & Gregg (2010) note that there are possibilities that the submitted string of data by the user could be used to form an SQL query statement which allows for the extraction of all secured records stored in the database. Additionally, online marketing websites such as are likely to contain products and characteristics like price of the products, description, and availability stored in the relational database which could allow detection of SQL injection whenever it occurs.
On the other hand, Clarke (2009) observes that detection of SQL injection heavily depends on the level of suspicion by the organization or enterprise. For instance, if an enterprise wants to detect an SQL injection, it simply needs to be on the lookout for occurrence of SQL meta characters in the form of single quote, semi-colon or double dash similar to a suspicious way of checking for other attacks such as CSS.
Similarly, SQL injection can be detected in a process called Union Query SQL Injection Testing. Sambath & Zhu (2012), note that this detection involves the use of union operator. This operator is used in SQL injection to join a query statement which is intentionally crafted for the tester to appear as the original genuine query. In this case, they note that the result of the crafted query is joined with the result of the original query to allow the tester to compare the values of fields from different tables. The following example illustrates the execution of a query statement from the server; SELECT Name, Phone, Address FROM Users WHERE Id=$id. In this example the following id value is set; $id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCarTable. The query; SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCarTable, is set to join the original query with, for instance, all credit card users. In the above illustration, Cole (2011) observes that the keyword ALL is necessary in order to circumvent all queries that use the word DISTINCT.
Moreover, it is noticeable that besides the credit card number, other two values have been selected. These values are vital because the two queries are supposed to have related the number of a parameter in order to eliminate occurrence of a syntax error.
Preventing SQL Injection
According to Ben-Gan, Sarka, & Wolter (2009), SQL injection attacks can be detected easily by looking for SQL signatures in the incoming HTTP stream. For instance, he notes that looking for SQL commands, such as UNION, SELECT or xp, will speed up the detection of an SQL injection. However, he indicates that this approach presents the problem of very high rate of false positives. This is because most SQL commands are legitimate words that normally appear in the incoming HTTP stream and can be detected as an SQL injection. Ben-Gan, Sarka, & Wolter (2009) suggest that such a high rate of detection will eventually force the user to either disable or ignore any SQL alert reported, which may be detrimental to the security of the database. He adds that the best way of avoiding this problem is to ensure that the software used in detection should be aware where and when to expect SQL signatures to appear. In this case, it is important that the software used is able to distinguish parameter values coming from the entire HTTP request. It should also be able to handle various encoding scenarios within an online environment.
According to Cole (2011), SQL injection can be prevented by the adoption of validated data input techniques which authenticate input against a clear set of defined rules that check length, type, and syntax. Similarly, it can be avoided by having strict business rules within an online environment in which the organization operates. These business rules include giving the users least privileges to allow permission for accessing the database. Additionally, Cole (2011) suggests that system administrators’ accounts, such as “sa”, should be avoided for Web applications. Similarly, the user account should be created for a specific application, and such a user is not given authority to access other applications within the same environment.
Noticeably, Ben-Gan, Sarka & Wolter (2009) noted that it is important to remove all applications which are not in use. Ben-Gan, Sarka & Wolter (2009) suggest the use of strongly typed parameter query APIs which have placeholder substitution markers even in a situation where the user is in the process of performing simple actions, such as calling stored procedures. In this regard, Cole (2011) notes that it is important to be careful when using stored procedures, as they are arguably safe from SQL injection. However, he further notes that stored procedures are not fully fool proof against SQL injection, as they can be injected by using such structures as exec() or lining up arguments within the stored procedures.
Similarly, Cole (2011) observes that SQL injection can be prevented by filtering out character lines, which are presented as single quote, double quote and extended characters, such as NULL, carry return, and new line in all strings from inputs by the users, parameter from URL, and values entered from cookies. He further observes that the administrator should consider converting numeric values from user input to integers before parsing them into SQL statement or simply use the ISNEMERIC function to ascertain that the input is an integer.
Similarly, Sambath & Zhu (2012) indicate that SQL injection can be prevented by using firewalls and similar intrusion detection mechanisms, which provide defense against web attacks. This is because most websites are public platforms where security measures allow public web traffic to interact with database servers through web application. Sambath & Zhu (2012) noted that practices, such as patching servers, databases, programming languages and operating systems, are important for protecting against malicious attacks, yet they cannot prevent SQL injection attacks.
Daswani, Kern & Kesavan (2007) observe that another method of preventing SQL injection is to use a method called escaping. Taylor (2011) noted that this is a straightforward way of preventing SQL injections by escaping the characters with special meaning in SQL. Taylor (2011) observes that special meaning characters are contained in SQ DBMS and the administrator can use this manual by preparing a blacklist for those characters. For instance, he indicates in the manual that at each occurrence of a single quote (
') in a parameter should be replaced with two single quotes (
''). This will necessitate the formation of a SQL string that is valid. For example, Taylor (2011) notes that in PHP, it is always advisable to escape parameters using the function
mysql_real_escape_string(); prior to sending the SQL query: $query = sprintf("SELECT * FROM `Users` WHERE UserName='%s' AND Password='%s'",
Additionally, Taylor (2011) argues that SQL injection can be prevented by limiting the privileges of the user during the logon session in the database in the web application. The user should be given permission only to the extent whereby effectiveness of any SQL injection attacks that exploit the vulnerability of the web application is reduced.
How to Protect the System from SQL Attacks
Andrews, Litchfield, &Grindlay (2003) indicate that it is important for programmers and administrators to understand web applications which are mostly vulnerable to attacks and those that can attract many hackers. This is because most of the web applications used in the Internet environment do not pass rigorous testing and quality assurance procedures, while they are being produced. This translates to the fact that simple procedures, such as scanning customized web applications with signature base scanners, cannot point out the vulnerabilities posed by SQL injection to a system and to extension any other malicious malware.
Similarly, Cole (2011) argues that establishing and testing against a database of signatures to ascertain vulnerability of the system by using known applications cannot prevent SQL injection. This is because such practice amounts to passive auditing which can only cover off-the-shelf applications, hence any vulnerability to emerging hacking techniques cannot be disclosed. Additionally, it is noted that signature matching can only do more if an attacker launches an SQL injection on customized web applications. This is because such attacks are not based on the signature file testing. Taylor (2011) writes that hackers are well versed with common applications, systems and servers, and that servers are updated and secured always by vendors. Therefore, their target focuses on the customized web applications which are not always secured and updated.
It is also important that during the assessment of web security, the programmer ascertains what really appears in the system. Taylor (2011) observes that it is possible for the scanner to find vulnerability without its existence. This requires administrators and programmers to test the pushback that is received from the tester before embarking on finding security breach. This will ensure that administrators and developers of web applications remain ahead of hackers. For instance, Taylor (2011) observes that it is possible for SQL injection search to emerge from exploitable vulnerability of a web application. SQL injection feeds allows for manual entering of the script code directly into the URL string. However, search pop up does not necessarily indicate that the vulnerable page really exists on the web application. This is indicated by the pushback embedded in the system.
This can be an indication that there exists proxying or load balancing that are in place behind the scenes. According to Sambath & Zhu (2012), this indicates the possibility of the occurrence of an error outside database network server setup for the web application. Notably, the database network server prevents the SQL injection from finding a page with the specified vulnerabilities, and, therefore, prevents the resolving of the sub-domain name. Instead the request from the SQL injection will be passed on to catch all entry which redirects it to other websites. Sambath & Zhu (2012) note that lack of URL rewriting creates the confusion for the input from the attacker. In essence, the attacker cannot differentiate between the requests sent to the back end user and the ones directed to other sites. In this case, Sambath & Zhu (2012) suggest that the returning HTTP 404s try to access the suspected vulnerable page by the attacker which prevents them from accessing the actual database.
Andrews, Litchfield, &Grindlay (2003) note that it is important for programmers and administrators to use rigorous and heuristic technologies to identify SQL injection attacks before they occur. Therefore, a system can be prevented from SQL injection by using a well-protected website and the one which its structure and different components and linkages have been ascertained. Similarly, a system can be protected by using powerful scanners which help leverage in an intelligent manner the various methods and techniques of attacking a web application. Notably, Daswani, Kern & Kesavan (2007) argue that in addition to the detection of the vulnerability of known applications, it is significant that heuristics is used in detecting vulnerabilities, because attackers and hackers are highly creative, and they launch new attacks against modified web applications in order to impose greater impact on the system.
Similarly, Andrews, Litchfield, &Grindlay (2003) advise that administrators should avoid accepting inputs from user who use SQL query statement. This will ensure that anything passed on by the user from the outside does not get into the system to sent back messages which can be interpreted by the attacker. This way, the administrator will also be able to ensure that suspicious constructs from users are turned away. Additionally, Andrews, Litchfield, &Grindlay (2003) argue that inputs from the users should be bound in a query but not allow the concatenation of user inputs into the system. Daswani, Kern & Kesavan (2007) observe that concatenation of user inputs gives the user the ability to bypass security codes and execute them later.
From the above discussion, it has been observed that SQL injection is an attack which seeks to exploit the activities of a web application by using poorly validated or encoded user input. The information from the user is utilized as part of a command or query against a back-end database. It is also clearly evident that SQL injection is among the most common software vulnerabilities that web developers and security personnel need to take into consideration while developing secured databases. With conditions such as “1=1” being always true, there is a need to ascertain the information supplied by the users logging into databases to verify their authenticity before using the same information in a query or command. It is also noted that the use of protective mechanisms can help in preventing a system from hacker attacks. This can, therefore, ensure the security of vital information contained in the databases.