Detection of SQL Injection and Cross-site Scripting Attacks

Hi Friends,

Today while doing google..... (oops I should say bing...;) ) for SQL Injection, I came across a good article describing how to detech SQL Injection and cross-site scripting attacks. I thought of sharing it with you as well.


http://www.symantec.com/connect/articles/detection-sql-injection-and-cross-site-scripting-attacks



Regards
Gurpreet Singh Sethi

Find 2nd Monday Of The Month

Hi,

Recently one of our fellow DBA friend asked a question on MSDN. Question was, he want to have a job with 2 steps, First step will run everyday and pass on the information but second step should only get executed on 2nd Monday of the Month.

Now how to do that, ofcourse he has to put some conditions to check whether its 2nd Monday of the Month or not and then process code accordingly. So question is how to find 2nd Monday of the Month. Here is the code friends.

DECLARE @DAY VARCHAR(10)


DECLARE @DATE VARCHAR(10)

DECLARE @TODAY VARCHAR(10)


SET @DAY='Monday'

SET @DATE=CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,14-datepart(day,getdate()),getdate())), 0),111)
SET @TODAY=CONVERT(VARCHAR(10),getdate(),111)
IF @DAY= DATENAME(WEEKDAY,DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,14-datepart(day,getdate()),getdate())), 0))

AND @TODAY=@DATE

BEGIN

SELECT 'Its Second '+@DAY+' Of The Month And Date Is '+@TODAY

END

ELSE

SELECT 'Its '+@DAY+ ' Today and Date Is '+@TODAY
 
Hope this will help.

Update Statistics For Specific Schema

Hi All,

One of my fellow dba asked a question.

I have a database having multiple schema's in it. I want to update statistics for some specific schema's only?

Now how to do that. Again SP_MSFOREACHTABLE (ofcourse non documented) but the savior here.

--This will print table names first for specific schema.

Exec sp_MSforeachtable @command1 = " print '?' ", @whereand = " and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo') "

--This will update statistics for specific schema.

Exec sp_MSforeachtable @command1 = " Update Statistics ? ", @whereand = " and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'sales') "

So using above command we can do this. If we have multiple schema's simply change Where condition and make it WHERE NAME in ('a','b','c')

Query First 2 Records For Each Alphabet

Hi Friends,

Disclaimer first ;)
This question came on MSDN site few days back where one of our friend was seeking help to write a query which can retrieve first 2 records for each alphabet from a set of records. I thought of sharing it with you. But yes content/resolution is not mine I am just posting here for others help.

We have a table say named as "MSDN" with below structure & records in it.
















Results expected out of the query was like this:


So how to achieve this?

Thanks for our MSDN friend John to provide this query:

Select msdn.*
From msdn
Join
(
select empno, rank() over (partition by left(empname,1) order by empname) as r from msdn
) i on
msdn.empno=i.empno
and i.r<=2
order by empname

Original MSND post:
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9a0bee9c-855f-4591-b73b-82a71300b9d7

Effect Of Table Name Change (Using SP_RENAME) On Stored Procedures

Hi,

Recently I came across a post on MSDN its was interesting. Lets see what it was.

Q: When we rename a table using SP_RENAME command, does it affect Stored Procedure
that were depending on it?

A: I did a small repro lets discuss it here.

--Below statement will create a table TEST.
create table test (I int unique, name varchar(10),age int)
go

--Below statements will insert 3 records in table TEST.
insert into test values (1,'Robert',20)
insert into test values (2,'Bob',21)
insert into test values (3,'Sam',30)
go

--Below statement will create a procedure on table TEST (a simple SELECT * FROM TEST).
create procedure USP_SELECT_TEST AS
SELECT * FROM TEST
go

--Below statement will create a table named TEST_TMP (replica of TEST table).
CREATE TABLE TEST_TEMP (I INT UNIQUE, NAME VARCHAR(10), AGE INT)
GO

--Below statement will insert values in newly created table TEST_TEMP from TEST table.
INSERT INTO TEST_TEMP SELECT * FROM TEST
GO

--Below statements will get OBJECT ID for TEST, TEST_TEMP & USP_SELECT_TEST objects created above.
SELECT OBJECT_ID('TEST')
SELECT OBJECT_ID('TEST_TEMP')
SELECT OBJECT_ID('USP_SELECT_TEST')
GO
--Output for each above command respectively
Object Name Object ID
TEST 1723153184
TEST_TEMP 1771153355
USP_SELECT_TEST 1755153298

--Below statement will show dependencies of above created objects.

SELECT * FROM SYS.SQL_DEPENDENCIES

class class_desc object_id column_id referenced_major_id referenced_minor_id is_selected is_updated is_select_all
----- ------------------------------------------------------------ ----------- ----------- ------------------- ------------------- ----------- ---------- -------------
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 1 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 2 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 3 0 0 1

--So in above output what we are seeing is that stored procedure USP_SELECT_TEST having OBJECT ID 1755153298
--is refered by table TEST having object id 1723153184 (which is fare statement isn't it).


--Below statement will rename table TEST to TEST_OLD.
SP_RENAME 'TEST','TEST_OLD'
GO

Output
Caution: Changing any part of an object name could break scripts and stored procedures.

--Below statement will rename TEST_TEMP to TEST.
SP_RENAME 'TEST_TEMP','TEST'
GO
Output
Caution: Changing any part of an object name could break scripts and stored procedures.

--Again running OBJECT ID statement to find if after rename OBJECT_ID has changed or not.
SELECT OBJECT_ID('TEST')
SELECT OBJECT_ID('TEST_TEMP')
SELECT OBJECT_ID('USP_SELECT_TEST')
SELECT OBJECT_ID('TEST_OLD')

--Output for each above command respectively
Object Name Object ID
TEST 1771153355
TEST_TEMP NULL (As there is no table with name TEST_TEMP.)
USP_SELECT_TEST 1755153298
TEST_OLD 1723153184

Now if we compare both OBJECT_ID outputs we are seeing that previously (Prior to SP_RENAME command)
table TEST was having OBJECT_ID 1723153184 but now it has value 1771153355 (which was previously allocated
to table TEST_TEMP) no why it is like this. This is because only the logical name of the table has changed
in actual OBJECT_ID remains the same.

Now if we again run the SYS.SQL_DEPENDENCIES statement we will see.

SELECT * FROM SYS.SQL_DEPENDENCIES

class class_desc object_id column_id referenced_major_id referenced_minor_id is_selected is_updated is_select_all
----- ------------------------------------------------------------ ----------- ----------- ------------------- ------------------- ----------- ---------- -------------
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 1 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 2 0 0 1
0 OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND 1755153298 0 1723153184 3 0 0 1

So USP_SELECT_TEST having OBJECT_ID 1755153298 is still referring to table TEST_OLD with OBJECT_ID 1723153184 however
currently table TEST is having OBJECT_ID 1771153355.
So that means that if we use SP_RENAME statement it will only rename the NAME of the object not the underlying OBJECT_ID. OBJECT_ID will remain the same. Also in meta data tables objects are refered with OBJECT_ID's not NAME.

That is the reason when you check DEPENDENCIES in Management Studio it still shows old OBJECT i.e. TEST_OLD.


Regards
Gurpreet Sethi

How to take backup of Database Diagrams

Hi,

I came across a question on MSDN, thought lets share it with you as well. Q: We have a Database Diagram in one of our database, we want to move this diagram to some other instance of SQL. How to do that? Now this is pretty good I can say, I don't remember if I could have ever though of it.

So lets try to explore it. So lets first create a database diagram:

Environment

SQL Server Edition Version: SQL Server 2008 (10.0.2531) Developer Edition x86

Windows XP SP3

So I used ADVENTUREWORKS database and created Database Diagram (as shown in above picture). Now when ever we created Database Diagram, its meta data gets saved inside same database (under SYSTEM tables).

Prior to jumping to think how to move this diagram to another instance, I would like to think about how normally we take/move databases or its objects?

If I am not wrong we have EXPORT/IMPORT utility to take object level movement of database objects. So lets try to explore the same.

So as we can see, we can export the database diagram but we also have to export objects ith which Database Diagram objects are associated. If we don't include them in Export them table will be moved along with data but we will be able to open this Database Diagram completely.

Regards

Gurpreet Sethi

Calculate Days, Hours, Mins & Seconds From Two Dates

Hi,

Recently one of our friend posted a question on MSDN. Question was to how to calculate Different between 2 dates in terms of Day, Hour, Month & Seconds. One of our other friend (Santy The Tango Charlie) shared the code. I though of sharing it with you as well.

SET NOCOUNT ON

GO

DECLARE @CDate DATETIME

DECLARE @EDate DATETIME

SET @CDate = getdate()

--SET @CDate = '2011-01-01 10:00:00'

SET @EDate = str(year(@CDate))+'-12-31'

select 'StartDate' = @CDate , 'YearEndDate' = @EDate select 'Months' = DATEDIFF(mm,@CDate,@EDate) , 'Days' = DATEDIFF(dd, DATEADD(mm,(DATEDIFF(mm,@CDate,@EDate)),@CDate) ,@EDate) , 'Hours' = case when (DATEDIFF(hh,@CDate,convert(varchar(11),@EDate+1,101)) - (DATEDIFF(dd,@CDate,@EDate)*24))=24 then '00' else DATEDIFF(hh,@CDate,convert(varchar(11),@EDate+1,101)) - (DATEDIFF(dd,@CDate,@EDate)*24) end , 'Minutes' = case when (abs(DATEDIFF(mi,'01:00:00','00:'+str(substring(convert(varchar(11),@CDate,108),4,2))+':00')))=60 then '00' else abs(DATEDIFF(mi,'01:00:00','00:'+str(substring(convert(varchar(11),@CDate,108),4,2))+':00')) end , 'Seconds' = case when (abs(DATEDIFF(ss,'00:01:00','00:00:'+str(substring(convert(varchar(11),@CDate,108),7,2) ))))=60 then '00' else abs(DATEDIFF(ss,'00:01:00','00:00:'+str(substring(convert(varchar(11),@CDate,108),7,2) )))

end

Original Post http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1aa5e849-043a-4870-8379-8ba6db19be95 Regards Gurpreet Sethi

SQL 2008 Maintenance Plan Error - 'Alter failed for Server '

Hi,

One of my fellow DBA team member came to me with a problem, but let me provide descriptions of this first.

Description
************

we have a recetnly installed server ofcourse SQL Server 2008, created a maintenance plan with CHECK DATABASE INTEGRITY TASK with INCLUDE INDEXES option selected.



When we execute this maintenance plan it fails with below error:




So as the screenshot mentions that plan is failing because its not able to perform ALTER task in current SQL Instance.

Now why is it so? I am just created a Maintenance Plan with Database Integrity task and its failing? Why?

Troubleshooting
***************

I ran profiler and checked why its failing?

In Profiler I found errors like below when it was trying to execute SP_CONFIGURE 'USER OPTION',0 ; RECONFIGURE

Error: 5808, Severity: 16, State: 1
Ad hoc update to system catalogs is not supported.

Wait.. why its complaining AD HOC UPDATE TO SYSTEM CATALOGS is not supported? Why its trying to change SYSTEM CATALOGS at first place? In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables. In SQL Server 2005 and newer, the system tables are gone, replaced with the Resource Database and system views. This option is no longer supported in SQL Server 2005 and beyond, and though you can set Allow Updates to 1 with no error, as soon as you run RECONFIGURE, you will receive the error that ad hoc updates are not supported. Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.

As per Books On Line:

Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

So i checked current setting SP_CONFIGURE and found ALLOW_UPDATE config value as 1. So i configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.

Below is the command used for setting this option

SP_CONFIGURE 'ALLOW UPDATE',0
RECONFIGURE

Regards
Gurpreet Sethi