Overview Of The Dangers Of Sql Illiteracy Computer Science Essay

Published:

Abstract- This document gives formatting instructions for authors preparing papers for publication in the Proceedings of an IEEE conference. The authors must follow the instructions given in the document for the papers to be published. You can use this document as both an instruction set and as a template into which you can type your own text.

Keywords- Include at least 5 keywords or phrases

Introduction

Throughout the developed world, governments, defence industries, and companies in finance, power, and telecommunications are increasingly targeted by overlapping surges of cyber attacks from criminals and nation-states which seek economic or military advantage. The number of attacks is now so large and their sophistication so great, that many organizations are having trouble determining which new threats and vulnerabilities pose the greatest risk and how resources should be allocated so that it is ensured most probable and damaging attacks are dealt with first. Exacerbating the problem is that most organizations do not have an Internet-wide view of the attacks and hence are vulnerable.

Lady using a tablet
Lady using a tablet

Professional

Essay Writers

Lady Using Tablet

Get your grade
or your money back

using our Essay Writing Service!

Essay Writing Service

 In 2010, with a growing reemphasis on security, the Open Web Application Security Project or in short OWASP, has updated their list of the ten most serious web application vulnerabilities.  This list has only been updated only in 2004 and 2007. [1]  

Injection

Cross-site scripting(XSS)

Broken authentication and threat management

Cross-site request forgery(CSRF)

Security misconfiguration

Insecure cryptographic storage

Failed to restrict url access

Insufficient transport layer protection

Unvalidated redirects and forwards

SQL Injection

A common and easy way which can be used and deployed by hackers for exploiting the vulnerabilities in a website is Injection and in particular SQL Injection.

SQL injection is a code injection technique which exploits a security vulnerability occurring in the database layer of an application. In this kind of attack malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and hence unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside one another. [2]

SQL injection attacks are also commonly known as SQL insertion attacks. Any procedure that constructs SQL statements should always be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives including parameterized data can be manipulated by a skilled and determined attacker.

The primary form of SQL injection technique consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata in the website. When these stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

The danger of SQL illiteracy

Despite growing interest in other storage media, relational databases are still the dominant data storage medium for majority Web applications and web based applications. Indeed, as SQLite and other databases which can be embedded in applications are increasingly growing in popularity, we're seeing the use of relational databases expand even further into the market.

It seems though, that actual knowledge of SQL seems to be falling. We can blame this primarily on the growing popularity of persistence frameworks that abstract the database away, allowing developers to interact with databases without writing much (or any) SQL. E.g. Ruby on Rails has ActiveRecord, Django has its own persistence components, and there are many other ORM frameworks for Java that work very well in this concept. There are even a number of persistence frameworks for the PHP and others. As developers start to rely on these frameworks, their SQL skills grow rusty over a period of time. Many developers don't even learn SQL in depth, rather just the basics.

This becomes a big liability. Even if we don't have to produce SQL in order to include it in our application, we still need to know it in order to write ad hoc queries. Knowing SQL lets us write a single query to answer questions like, "What's the average total for orders received from customers in Texas over each of the last six months?" We've seen developers trying to take care of such kinds of questions by pulling back individual records and aggregating the results within the code. They should be using SQL efficiently instead.

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

As a developer people are not comfortable with SQL. The other thing to be looked into is that languages and frameworks come and go, but SQL, CSS, HTML, and JavaScript are going to be with us for a long, long time. Any investment we may make in those areas will certainly pay off.

Forms of Vulnerability

The most common operation in SQL is the query, which is data from one or more tables, or expressions as required.

A query includes a list of columns which is to be included in the final result immediately following the SELECT keyword. An asterisk ("*") can also be used to specify that the query should return all columns of the tables being queried. SELECT statement is the most complex statement in SQL, with optional keywords and clauses that include the following:

FROM

JOIN

WHERE

GROUP BY

HAVING

ORDER BY, etc.

Incorrectly Filled Escape Characters

This type of SQL injection occurs when user input is not filtered for escape characters and is then passed into an SQL statement. The result is potential manipulation of the statements performed on the database by the end-user of the application.

The following line of code should illustrates this vulnerability:

statement = "SELECT * FROM `users` WHERE `name` = '" + userName + "';"

The above SQL code is designed to pull up the records of the specified username from its table of users. However, if the "userName" variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author had intended to do. For example, the setting of "userName" variable as

' or '1'='1

Or using comments for blocking the rest of the query (there are three types of such sql comments): [3]

' or '1'='1' -- '

' or '1'='1' ({ '

' or '1'='1' /* '

renders this SQL statement by the parent language as:

SELECT * FROM `users` WHERE `name` = '' OR '1'='1';

If this code was to be used in an authentication procedure then this example could be used to force the selection of a valid username because the evaluation of '1'='1' which is always true.

The following value of "userName" in the statement below would cause the deletion of the "users" table as well as the selection of all data from the "userinfo" table (which in essence reveals the information of every user), using an API which will allows multiple statement viz:

a';DROP TABLE `users`; SELECT * FROM `userinfo` WHERE 't' = 't

The above input renders the final SQL statement as follows:

SELECT * FROM `users` WHERE `name` = 'a';DROP TABLE `users`; SELECT * FROM `userinfo` WHERE 't' = 't';

While most SQL server implementations may allow multiple statements to be executed with one call in this way, some SQL APIs such as PHP's mysql_query(); function do not allow this for the sake of security reasons. This prevents attackers from injecting entirely separate queries, but it doesn't stop them in any way from modifying queries.

Incorrect Type Handling

This form of SQL injection occurs when a user supplied field is not strongly typed or when it is not checked for type constraints. This could take place whenever a numeric field is to be used in a SQL statement, but the programmer makes no checks to validate that the user supplied input is numeric or other datatype. For example:

statement := "SELECT * FROM `userinfo` WHERE `id` = " + a_variable + ";"

It is clear from this statement that the author intends a_variable to be a number which correlates to the "id" field. However, if it is in fact a string then the end-user may manipulate the statement in the way they choose, thereby bypassing the need for escape characters. For example, setting a_variable to

1;DROP TABLE `users`

will drop (delete) the "users" table from the database, since the SQL would be rendered as:

Lady using a tablet
Lady using a tablet

This Essay is

a Student's Work

Lady Using Tablet

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Examples of our work

SELECT * FROM `userinfo` WHERE `id`=1;DROP TABLE `users`;

Blind SQL Injection

Blind SQL Injection is used whenever a web application is vulnerable to an SQL injection but the results of the injection are not visible to the attacker. The page with the vulnerability may not be one that displays the data required but will display differently depending on the results of a logical statement which is injected into the legitimate SQL statement called for that page. This type of attack can become time-intensive because every time a new statement must be crafted for each bit recovered. Though now there are several tools that can automate these attacks once the location of the vulnerability and the target information has been established. [4]

Conditional Responses: This type of blind SQL injection forces the database to evaluate a logical statement on an ordinary application screen.

SELECT `booktitle` FROM `booklist` WHERE `bookId` = 'OOk14cd' AND '1'='1';

This will result in a normal page while

SELECT `booktitle` FROM `booklist` WHERE `bookId` = 'OOk14cd' AND '1'='2';

will likely give a different result depending on wether the page is vulnerable to a SQL injection. An injection like this might suggest the attacker that a blind SQL injection is possible, which leaves the attacker to devise statements that evaluate to true or false depending on the contents of another column or table outside of the SELECT statement's column list. [5]

Conditional Errors: This blind SQL injection causes an SQL error which is done by forcing the database to evaluate a statement that causes an error if the WHERE statement is true. For example,

SELECT 1/0 FROM `users` WHERE `username`='Ralph';

The division by zero will only be evaluated and result in an error if user Ralph exists.

Time Delay: Time delays are a type of blind SQL injection that causes the SQL engine to execute a long running query or a time delay statement which depends on the logic injected. The attacker can then measure the time the page may take to load and hence determine if the injected statement is true.

Mitigation [6]

The Mitigation of SQL injection vulnerability would be taking one of the two paths i.e. either using prepared statements with dynamic SQL commands or using stored procedures along with callable statements. Whichever way the user adopts the data validation is must.

Input Validation

1) Data Sanitization: Data sanitization is the key. Best way to sanitize the data is to use default deny, regular expression. In the following example regular expression would return only letters and numbers:

S/[^0-9a-zA-z//g

We must write specific filters for efficiency. As far as possible use numbers and letters. If there is a need to include punctuation marks of any kind in the code, convert them by HTML and encode them. E.g. " become """ or > becomes ">". For instance if the user is submitting the E-mail address which allow only @, -, . and _ in addition to numbers and letters to be used, it will work only after they have been converted to their HTML substitutes.

Use of Prepared statements

The prepared statements should be used whenever the stored procedures cannot be used for whatever reasons and dynamic SQL commands have to be used.

Use a PreparedStatement to send precompiled SQL statements with one or more parameters in the query. Parameter place holders in a prepared statement are represented by the '?' and are known as bind variables. Prepared statement are generally immune to SQL Injection attacks. This is because the database will use the value of the bind variable exclusively and not interpret the contents of the variable in any way. PL/SQL and JDBC are common programs which allow for prepared statements. Prepared statements should be extensively used in the servers for both security and performance reasons.

Use Minimum Privileges

Programmer must make sure that application user has specific bare minimum rights on the database server. If the application user on the database uses ROOT/SA/dbadmin/dbo on the database, then it surely needs to be reconsidered if application user really needs such high amount of privileges or can they be reduced and hence secured to greater extent. Do not give the application user permission to access system stored procedures which mayallow access to the ones that are user created.

Stored procedures

To secure an application against SQL query injection, developers must never allow a client-supplied data to modify the syntax of SQL statements. In fact, the best protection against this is to isolate the web application from SQL altogether. All SQL statements required by the application should be in stored procedures and kept on the database server itself. The application should execute the stored procedures using a safe interface such as Callable statements of JDBC or CommandObject of ADO, etc.

ADO's Command Object

By using the command object, we can isuue database commands. These commands can be, but are obviously not limited to, query strings, prepared query strings, and associated parameters with query strings. The command object can either open a new connection or use an existing connection which allows performing queries

A Sample Code is as follows:

Sub ParameterExample()

Dim cmd As New ADODB.Command

Dim rs As New ADODB.Recordset

Dim prm As ADODB.Parameter

' Set the command's connection using a connection string.

cmd.ActiveConnection = "DSN=pubs;uid=sa"

' Set the command's text, and specify that it is an SQL statement.

cmd.CommandText = "byroyalty" //Name of the stored procedure

cmd.CommandType = adCmdStoredProc //Type is set to invoke a stored procedure

' Set up a new parameter for the stored procedure.

Set prm = cmd.CreateParameter("Royalty", adInteger, adParamInput, , 50)

' This sets up template for parameter

cmd.Parameters.Append prm

' Create a recordset by executing the command.

Set rs = cmd.Execute

' Loop through the recordset and print the first field.

Do While Not rs.EOF

Debug.Print rs(0)

rs.MoveNext

Loop

' Close the recordset.

rs.Close

End Sub

If arbitrary statements, i.e. dynamic SQL statements, must be used, use PreparedStatements instead. Both PreparedStatements and stored procedures compile the SQL statement before the user input is added which makes it impossible for user input to modify the actual SQL statement.

Real World Examples of threat

Web Application Security Consortium in its statistical report of the year 2007 showed SQL Injection as the second greatest threat in terms of prevalence of 'high risk level vulnerabilities'.[26]

Fig. 1 The probability to detect the most risky vulnerabilities in Web application [26]

Some Practical live Examples where SQL Injection has been used for the purpose of cyber attacks are as follows:

On November 1, 2005, a high school student used SQL injection to break into the site of a Taiwanese information security magazine and steal customers' information.[7]

On January 13, 2006, Russian computer criminals broke into a Rhode Island government web site and allegedly stole credit card data from individuals who had done business online with state agencies, using SQL Injection.[8]

On March 29, 2006, Susam Pal discovered a major SQL injection flaw in an official Indian government tourism site.[9]

On March 2, 2007, Sebastian Bauer had discovered an SQL injection flaw in the login page of knorr.de. [10]

On June 29, 2007, a computer criminal defaced the Microsoft U.K. website simply by using SQL injection.[11][12] A U.K. based website 'The Register' quoted a Microsoft spokesperson acknowledging the problem that had occured.

In January 2008, numerous PCs were infected by an automated SQL injection attack which was exploiting a vulnerability in application code that used Microsoft SQL Server as its database store.[13]

In July 2008, the famous anti-virus company Kaspersky's Malaysian site was hacked by a Turkish hacker going by the handle of "m0sted". He claimed to have used SQL injection

On April 13, 2008, the Sexual and Violent Offender Registry of Oklahoma had to shut down its website for 'routine maintenance' after being informed that 10,597 Social Security numbers belonging to sex offenders had been downloaded by unknown users via an SQL injection attack[14]

In May 2008, a server farm located in China used automated queries to Google's search engine to identify SQL server websites which had vulnerability to the attack of an automated SQL injection tool.[13][15]

In 2008, from April till August, a sweep of attacks began exploiting the SQL injection vulnerabilities of the Microsoft's IIS web server and the SQL Server database server. The attacks did not require guessing the name of a table or column, and corrupted all text columns in all tables on a single request.[16] A HTML string that is used to reference a malware JavaScript file was appended to each value. When that database value was later displayed to a website visitor, the script attempted several approaches at gaining control over the visitor's system. The number of exploited web pages was later estimated at around 500,000.[17]

On August 17, 2009, the United States Justice Department charged an American citizen Albert Gonzalez and two unnamed Russians with the theft of 130 million credit card numbers which was done by using an SQL injection attack. In reportedly "the biggest case of identity theft in American history", the man stole cards from a number of corporate victims over the internet after researching their payment processing systems. Among the companies hit were giants like credit card processor Heartland Payment Systems, convenience store chain 7-Eleven, and supermarket chain Hannaford Brothers.[18]

In December 2009, an attacker breached a RockYou plaintext database which contained the unencrypted usernames and passwords of about 32 million users .This was done simply using an SQL injection attack.[19]

In July 2010, a South American security researcher, who goes by the handle 'Ch Russo', obtained sensitive user information from the popular BitTorrent site 'The Pirate Bay'. He gained access to the site's administrative control panel and exploited an SQL injection vulnerability that enabled him to collect user account informations including IP addresses, MD5 password hashes and records of which torrents each individual users have downloaded.[20]

On July 24-26, 2010, attackers from within Japan and China used an SQL injection to gain access to customers' credit card data from Neo Beat (an Osaka-based company) which runs a large and famous online supermarket site. The attack also affected seven business partners which included supermarket chains Izumiya Co, Maruetsu Inc and Ryukyu Jusco Co. The theft of data affected a reported 12,191 customers at once. As of August 14, 2010 it was reported that there have been more than 300 cases of credit card information being used by third parties for purchasing goods and services in China.

On September 19th during the 2010 Swedish general election a voter was found attempting a code injection by hand, writing SQL commands as part of a write in vote.[21]

On 8th November 2010 the British Royal Navy website was found to be compromised using SQL injection . [22][23]

On 5th February 2011 HBGary, which is a technology security firm, was broken into by an anonymous user using a SQL injection in their CMS-driven website and stole information. [24]

On March 27th 2011, mysql.com, which is the official homepage for MySQL, was found compromised using SQL blind injections. [25]

Conclusions

SQL injection attacks are a serious concern for application developers round the globe as they can be used to break into supposedly secure systems and steal, alter, or destroy data. It's all too easy to leave ourselves vulnerable to these attacks, regardless of which version of ASP.NET or either similar kinds we are using. In fact, we don't even need to be using ASP.NET to be susceptible to SQL injection attacks. Any application that queries a database and uses user-entered data to do so, including Windows Forms applications is a potential target of an injection attack.

Protecting ourselves against SQL injection attacks is however not very difficult. Applications that are immune to SQL injection attacks are found to validate and sanitize all user input, never use dynamic SQL, execute using an account with few privileges, hash or encrypt their secrets, and present error messages that reveals little or if possible no useful information to the hacker. By following a multi-layered approach to prevention we can be assured that if one defence is circumvented, we will still be protected. [27]