T-SQL « It's Not A Bug, It's A Feature

10Jun/100

Fixing SQL Injection Attacks with a Cursor

Recently I had to fix a database that had been attacked with a SQL injection attack, surprisingly enough I was able to find the hole they attacked the site through and patch it and I was also was able to translate the attack used to infect the database and then I used the very same malicious code to attack the system to fix it.

The original attack came in on a search page with a sting like

http://www.theattckedwebsite.com/list.asp?s=dummy serch term';declare @s vArChAr(8000); sEt @s=0x546869732069732074686520542D53514C2061747461636B; eXEc(@s)--

and unfortunately this managed to get by the checks that were in place at the time and infect the database. the VARCHAR string is actually a SQL statement converted into a VARBINARY string. The malicious SQL statement is easily converted back to a human readable statement done by executing the following T-SQL statement.

1
2
3

DECLARE @s VARCHAR(8000)
SET @s=0x546869732069732074686520542D53514C2061747461636B
SELECT CAST(@s AS VARCHAR)

This SQL cursor will find the @StringToReplace in any User table with a field type of NVARCHAR, VARCHAR, TEXT, and NTEXT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

DECLARE @StringToReplace VARCHAR(100)
SET @StringToReplace = '<script src=http://www.attackerwebsite.com/attack.js></script>'

DECLARE @t VARCHAR(255),@c VARCHAR(255)
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 + '] = REPLACE(cast([' + @c + '] as varchar(max)), ' + @StringToReplace + ', '''')')
PRINT 'UPDATE [' + @t + '] SET [' + @c + '] = REPLACE(cast[' + @c + '] as varchar(max)), ' + @StringToReplace + ', '''')'
FETCH NEXT FROM tAbLe_cursoR INTO @t, @c
END
CLOSE table_cursor
DEALLOCATE table_cursor

I my opinion people who write this stuff to begin with should just be shot in the head and get it over with. but it does keep me employed, kind of a necessary evil. so hopefully this will help someone someday when you least expect it.

The amount of these types of attacks have grown over the years proving it is our job to be more diligent in our coding standards to try and keep from leaving holes in our code for the types of attacks to infect our databases.

Tagged as: Cursor, Hacking, SQL Injection, T-SQL No Comments

9Jun/100

SQL Variables as Temp Tables

How many times have you written a script which makes use of temp tables and then had something go wrong, and when you re-run your script you get this wonderful error.

1 2	Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named '#MyTempTableName' in the database.

A table variable is created in memory, and so it performs slightly better than #temp tables.
Table variables might still perform I/O to tempdb but there is not proof and the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop the temp table and the table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

So here is an Example of using a Variable Table

1
2
3
4
5
6
7
8
9
10

DECLARE @MyTable AS Table (
ID INT IDENTITY,
MyText VARCHAR(50)
)
INSERT INTO @MyTable (MyText) Values ('One')
INSERT INTO @MyTable (MyText) Values ('Two')
INSERT INTO @MyTable (MyText) Values ('Three')
INSERT INTO @MyTable (MyText) Values ('Four')
INSERT INTO @MyTable (MyText) Values ('Five')
SELECT * FROM @MyTable

And the final result:

Tagged as: T-SQL, Temp Tables No Comments

3Jun/100

SQL Convert Percentage to Decimal

Here's a nice simple method to convert a percentage value to a decimal value. I had to import some tax rates for California a couple of days ago and all the values I recieved where in the format of 9.75 but our system needed them as 0.0975 so I wrote this simple update to update all the values to the correct format.

1
2
3

DECLARE @VALUE AS VARCHAR(10)
SET @VALUE = '9.75'
SELECT CAST(CONVERT(DECIMAL(10,4) , (1.0 * CAST(@VALUE AS DECIMAL(10,8)) / 100)) AS VARCHAR) AS Pecentage

Results:

Tagged as: CAST, CONVERT, T-SQL No Comments

18May/100

CSV list to Int SQL Function

How many times have you wanted to pass in a comma delimited string of numbers to a SQL statement for an IN clause in a query against a Int field.

1
2
3
4

SELECT * FROM MyTable WHERE ID IN ('2,3,4,5,6')

Msg 245, Level 16, State 1, Line 1
Conversion failed WHEN converting the VARCHAR VALUE '2,3,4,5,6' TO DATA TYPE SMALLINT.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE FUNCTION [dbo].[CsvToInt] ( @Array VARCHAR(1000))
RETURNS @IntTable TABLE
(IntValue INT)
AS
BEGIN
DECLARE @separator CHAR(1)
SET @separator = ','
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + ','
While patindex('%,%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%,%' , @array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT @IntTable
VALUES (CAST(@array_value AS INT))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END

Now running the following SQL statement works..

1	SELECT * FROM PayPalAPI_NVPData WHERE ID IN ( SELECT IntValue FROM dbo.CsvToInt('2,3,4,5,6') )

Tagged as: SQL Function, T-SQL No Comments

It's Not A Bug, It's A Feature Just another Developer weblog

Fixing SQL Injection Attacks with a Cursor

SQL Variables as Temp Tables

SQL Convert Percentage to Decimal

CSV list to Int SQL Function

Sponsors

Categories

Site Links

Blogroll

The Cloud