Place for SQL Developers

Detection of SQL Injection and Cross-site Scripting Attacks

2007-12-20T13:26:00.002-08:00

I have found below article good enough to know about sql injection and more information.

http://www.securityfocus.com/infocus/1768

Duplicate Records

2007-12-20T12:55:00.000-08:00

--Sample data
/*
ID MAPID ADDRESSCOUNT
111 54560 4
132 54560 0
198 23429 1
240 29584 1
248 29584 0
Below is the code to delete duplicate records which are having addresscount = 0
*/
create table duptest
(
Id int,
MAPID int,
AddressCount int
)

insert into duptest
values(111,54560,4)
insert into duptest
values(132,54560,0)
insert into duptest
values(198,23429,1)
insert into duptest
values(240,29584,1)
insert into duptest
values(248,29584,0)

select MAPID,count(mapid)
from duptest
group by mapid
having count(mapid) > 1
DELETE
FROM duptest
WHERE MAPID IN
(select MAPID
from duptest
group by mapid
having count(mapid) > 1) and AddressCount = 0

Database Block information

2007-11-29T12:23:00.002-08:00

/*
sp_block_info - This will show all blocks on the database and is helpful
if you are currently experiencing blocking as it shows the statement
being blocked and the blocking statement. This is a custom proc taken
from the Microsoft SQL Server Development Customer Advisory Team Blog.
Here is the code for it.
NOTE: this only works on databases running SQL Server 2005 as it uses
Dynamic Management Views.
*/
CREATE proc sp_block_info
as
select getdate() as BlockDate,
db_name(resource_database_id) as [database],
t1.resource_associated_entity_id as [blk object],
t1.resource_type as [lock type],
t1.request_mode as [lock req], --- lock requested
t1.request_session_id as [waiter sid], --- spid of waiter
t2.wait_duration_ms as [wait time],
(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch,
(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt, --- statement blocked
t2.blocking_session_id as [blocker sid], -- spid of blocker
(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address

Welcome to SQL Developers

2006-02-13T02:16:00.000-08:00

Buddies,
Welcome to SQL buddies blog. Hope this blog makes everyone find more interesting information & knowledge on SQL and be a part of your life soon.