Friday, November 20, 2009

SQL SERVER 2005 builtin Encryption



CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecretTable_SecretData_Key'
GO
CREATE CERTIFICATE SecretTable_SecretData_Key
      WITH SUBJECT = 'IWMI',
      START_DATE = '01/04/2008',
      EXPIRY_DATE = '10/31/2050'

CREATE SYMMETRIC KEY SecretTable_SecretData_Key  
    WITH ALGORITHM = TRIPLE_DES  
    ENCRYPTION BY CERTIFICATE SecretTable_SecretData_Key 

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'SecretTable_SecretData_Key';
 GO
  

ALTER TABLE <TABLE NAME>
ALTER COLUMN <COLUMN NAME> [varchar](400)

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SecretTable_SecretData_Key'
  
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
  
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY


OPEN SYMMETRIC KEY  SecretTable_SecretData_Key  DECRYPTION BY CERTIFICATE SecretTable_SecretData_Key
declare @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'SecretTable_SecretData_Key')
declare @vartest as nvarchar(400) 
SELECT convert(varchar(max), decryptbykeyautocert(cert_id('SecretTable_SecretData_Key'), NULL, EBSAL_BASIC_SALARY))   FROM HS_HR_EMP_BASICSALARY
CLOSE SYMMETRIC KEY [SecretTable_SecretData_Key]

Tuesday, November 17, 2009

Advanced SQL Injection In SQL Server Applications

Advanced SQL Injection In SQL Server Applications

Table of Contents

 

[Abstract]...........................................................................................................................3

[Introduction].....................................................................................................................3

[Obtaining Information Using Error Messages].................................................................7

[Leveraging Further Access].............................................................................................12

[xp_cmdshell]...............................................................................................................12

[xp_regread]..................................................................................................................13

[Other Extended Stored Procedures]............................................................................13

[Linked Servers]............................................................................................................14

[Custom extended stored procedures]...........................................................................14

[Importing text files into tables]...................................................................................15

[Creating Text Files using BCP]...................................................................................15

[ActiveX automation scripts in SQL Server]................................................................15

[Stored Procedures]...........................................................................................................17

[Advanced SQL Injection]................................................................................................18

[Strings without quotes]................................................................................................18

[Second-Order SQL Injection]......................................................................................18

[Length Limits].............................................................................................................20

[Audit Evasion].............................................................................................................21

[Defences]........................................................................................................................21

[Input Validation]..........................................................................................................21

[SQL Server Lockdown]...............................................................................................23

[References].....................................................................................................................24

Appendix A - 'SQLCrack'.................................................................................................25

(sqlcrack.sql).........................

 

http://www.ngssoftware.com/papers/advanced_sql_injection.pdf

 

 

Manipulating Microsoft SQL Server Using SQL Injection

 

select * from OPENROWSET( 'SQLoledb','server=pacthr\pact;uid=hsenid;pwd=hsenid','select * from hs_hr_employee' )

 

 

Manipulating Microsoft SQL Server Using SQL Injection

Monday, November 16, 2009

Auditing Through Triggers (SQL Server)


Reprinted with Permission by Quest Software June 2004


Auditing Through Triggers
Robert Marda, bigdough.com

Introduction

In this article I will give code examples for a few triggers. For an excellent explanation about triggers in general and how they work in SQL Server 7.0 and SQL Server 2000 please see the following articles written by Brian Kelley: Triggers in SQL Server 7.0 and 2000 - The Common Ground and Triggers in SQL Server 7.0 and 2000 - What's New. The first article will explain the special tables called inserted and deleted.

I believe these examples will work on SQL Server 2000, however they were only tested on SQL Server 7.0. First we need to have a table to work with. Execute this code in Query Analyzer:

CREATE TABLE [Components] (         [Iden] [int] IDENTITY (1, 1) NOT NULL ,         [ComponentName] [varchar] (25) NULL ,         [SerialNumber] [varchar] (25) NULL ,         [Comments] [varchar] (75) NULL ,         [UserName] [varchar] (50) NULL ,         [UpdateDate] [datetime] NULL ,         [UpdatedBy] [varchar] (35) NULL ) ON [PRIMARY] GO  CREATE TABLE [ComponentsDeleted] (         [Iden] [int] NOT NULL ,         [ComponentName] [varchar] (25) NULL ,         [SerialNumber] [varchar] (25) NULL ,         [Comments] [varchar] (75) NULL ,         [UserName] [varchar] (50) NULL ,         [DeletedDate] [datetime] NULL ,         [DeletedBy] [varchar] (35) NULL ,  ) ON [PRIMARY] GO

Insert/Update Triggers

One of the simplest ways to design a trigger is one that fires whenever any column in a table is updated or whenever a row is inserted. The following code can be executed in Query Analyzer to create a trigger that captures the date a row was inserted or updated and the login for the person that made the last change:

CREATE TRIGGER updatedby ON dbo.Components FOR INSERT, UPDATE AS UPDATE c SET UpdateDate = getdate(), UpdatedBy = SYSTEM_USER FROM inserted i INNER JOIN dbo.Components c ON i.Iden = c.Iden

To test the trigger insert some rows into the table Components and then update one or two rows. Now suppose you only need to know when a component is swapped for a similar component. A change in the serial number would indicate such a swap. You could modify the above trigger (using an ALTER TRIGGER command) to only fire when the SerialNumber column is changed. Execute this code:

ALTER TRIGGER updatedby ON dbo.Components FOR INSERT, UPDATE AS  IF UPDATE(SerialNumber) BEGIN       UPDATE c SET UpdateDate = getdate(), UpdatedBy = SYSTEM_USER      FROM inserted i      INNER JOIN dbo.Components c ON i.Iden = c.Iden  END

Test this trigger by updating a value in the column Comments, then in the column SerialNumber.

Delete Trigger

Now you want to capture the components that are deleted from the components table. These could be components being returned. The below trigger will insert a row into ComponentsDeleted whenever a row is deleted from Components:

CREATE TRIGGER deletedby ON dbo.Components FOR DELETE AS INSERT INTO ComponentsDeleted (Iden, ComponentName, SerialNumber, Comments, UserName, DeletedDate, DeletedBy) SELECT Iden, ComponentName, SerialNumber, Comments, UserName, getdate(), SYSTEM_USER FROM deleted

Delete a row or two from table Components. Now take a look at your ComponentsDeleted table. You will find your deleted rows there with the date and time they were deleted.

Conclusions

Setting up a simple auditing system using triggers similar to those described in this article will take very little time and could be useful if you must know when certain actions are performed in your database and by whom.

 

 

From: Gayanga [mailto:Gayanga@hsenid.lk]
Sent: Wednesday, September 21, 2005 1:52 PM
To: developers@hsenid.lk
Subject: Auditing Through Triggers (SQL Server)

 

 

Nice way to use functions _ORACLE

Hi !
Check this

CREATE OR REPLACE PROCEDURE SP_HS_HR_MEDI_MEDICAL_LETTER (MEDI_CURSOR in OUT
MEDICAL_LETTER_PACKAGE.MEDICAL_LETTER_CURSOR,EMP_NO IN VARCHAR2,SCHEME_CODE
IN VARCHAR2)
AS
BEGIN
OPEN MEDI_CURSOR FOR


SELECT E.EMP_NUMBER, E.EMP_TITLE,
E.EMP_MIDDLE_INI, E.EMP_SURNAME,
S.MSC_CODE,
S.MSC_NAME, S.MSC_AMOUNT-SUM (R.MRG_CLAIM_AMOUNT) AS BALANCE
FROM HS_HR_MEDI_SCHEME S, HS_HR_MEDI_REGISTER R, HS_HR_EMPLOYEE E
WHERE ((S.MSC_CODE = R.MSC_CODE)
AND (E.EMP_NUMBER = R.EMP_NUMBER)
AND (E.EMP_NUMBER = EMP_NO)
AND (S.MSC_CODE = SCHEME_CODE))
GROUP BY E.EMP_NUMBER,
E.EMP_TITLE,
E.EMP_MIDDLE_INI,
E.EMP_SURNAME,
S.MSC_CODE,
S.MSC_NAME,
S.MSC_AMOUNT
UNION ALL

SELECT E.EMP_NUMBER, E.EMP_TITLE,
E.EMP_MIDDLE_INI, E.EMP_SURNAME,
S.MSC_CODE,
S.MSC_NAME, S.MSC_AMOUNT

FROM HS_HR_MEDI_SCHEME S,HS_HR_EMPLOYEE E

WHERE S.MSC_CODE = SCHEME_CODE
AND E.EMP_NUMBER = EMP_NO
AND E.EMP_NUMBER NOT IN
(SELECT E.EMP_NUMBER
FROM HS_HR_MEDI_SCHEME S, HS_HR_MEDI_REGISTER R, HS_HR_EMPLOYEE E
WHERE (S.MSC_CODE = R.MSC_CODE)
AND (E.EMP_NUMBER = R.EMP_NUMBER)
AND (E.EMP_NUMBER = EMP_NO)
AND (S.MSC_CODE = SCHEME_CODE));

END;
/

Regards
Bandula Liyanage, Software Engineer
HTA
hSenid Software International (PVT) Ltd
Telephone: +94-112-699754 Fax: +94-112-673845
E-mail: bandula@hsenid.lk <mailto:bandula@hsenid.lk>
Web: www.hsenid.com

Its all about people
**** DISCLAIMER *****
This e-mail and any attachments thereto are intended for the sole use of the
recipient(s) named above and may contain information that is confidential
and/or proprietary to the hSenid Software International (Pvt.) Ltd. Any use
of the information contained herein (including, but not limited to, total or
partial reproduction, communication, or dissemination in any form) by
persons other than the intended recipient(s) is prohibited. If you have
received this e-mail in error, please notify the sender immediately and
delete it.

How to get OLD / NEW Values in SQL Server Triggers

CREATE TRIGGER trig_updateEmployeeName

 

ON HS_PR_EMPLOYEE

 

FOR UPDATE

 

AS

 

DECLARE @oldName VARCHAR(100)

 

DECLARE @newName VARCHAR(100)

 

IF NOT UPDATE(EMP_FIRST_NAME)

 

BEGIN

 

RETURN

 

END

 

SELECT @oldName = (SELECT EMP_FIRST_NAME FROM Deleted)

 

SELECT @newName = (SELECT EMP_FIRST_NAME FROM Inserted)

 

PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'

 

Sunday, November 15, 2009

When, How, Where and Why to use Dispose

When, How, Where and Why to use Dispose

 

Dispose, Part 1
What does that pesky Dispose(bool disposing) method do for me?

On a blank form, you’ll see several lines generated:


private System.ComponentModel.Container components = null;
protected override void Dispose( bool disposing ) {
    if( disposing ) {
         if (components != null) {
            components.Dispose();
         }
     }
     base.Dispose( disposing );
}



Essentially this is placeholder code for when you add a component that isn’t a control to the form. When the form disposes, this code is there to dispose it’s associated components. The best example of this is the System.Windows.Forms.Timer and System.Windows.Forms.ToolTip.

If I drop on a timer from the toolbox onto the form and look in InitializeComponent, there are two new lines generated:


this.components = new System.ComponentModel.Container();
this.timer1 = new System.Windows.Forms.Timer(this.components);


Essentially, the constructor of Timer has just added itself to the components collection. When the form is in the process of disposing it will dispose the timer.

 

Dispose - Part 2
What is the purpose of the two dispose methods on Control & Component, etc?

Several pieces of background information:

 

Component implements IDisposable

Control inherits Component, thus inheriting IDisposable.

IDisposable has one method called Dispose()

Garbage collection (Finalization) happens on a different thread


Component (base class of Control) has the following pattern:

~Component() {
  // I am in the finalizer
  Dispose(/*currently disposing=*/false);
}             

Dispose() {
  // I’ve been told to clean up outside the finalizer
  Dispose(/*currently disposing=*/true);
  
 }

protected virtual void Dispose(bool disposing) {
    if (disposing) {
        // do stuff that can only be done from the main thread.
    }
    else {
       // this is the finalizer thread.  Be wary of touching other
       // objects as they may have already finalized. 
    }
   // do cleanup actions that can be done either from the finalizer 
   // thread or the main thread.
}



In other words Dispose(bool disposing) is the unified place for cleanup code. It is called from both the Finalizer and when someone explicitly calls the IDisposable.Dispose method.

If you want to prevent the finalizer from getting called, you can call GC.SupressFinalize from this method if disposing is true.

Here's a more in depth article on how to implement Dispose and Close methods. One little known fact: If you implement Dispose, you should allow it to be called multiple times.

Next time: Can I just let the GC get everything? When do I have to worry about Dispose?

 

Dispose, Part 3
Can I just let the GC get everything? When do I have to worry about Dispose?

 

If the object implements IDisposable then you should think about how the object is getting cleaned up.

Objects that implement IDisposable usually do so because they are holding on to real resources that should be freed deterministically.

Rule of thumb:
If the object implements IDisposable then you should think about how the object is getting cleaned up.


When a control is disposed, it disposes all of its children. The only time you get automagic disposal of controls is when you do Application.Run(new Form()). (We like to call this showing the form modelessly). When the form gets WM_CLOSE message, it starts cleaning itself up while it can.

However if you do a Form.ShowDialog(), the form is NOT disposed. (We like to call this showing the form modally). This is because you may want to go in after the form has closed and look at the state of the child controls to determine the next action of your program.

If you add and remove controls from your form dynamically, you should call dispose on them – otherwise you’ll accumulate extra unwanted window handles in your process. Remember you only have to dispose the topmost control – so if you’re swapping in and out a panel – disposing the panel disposes it’s child controls.

Dispose is not just for controls though – Brushes, Pens, and Fonts implement IDisposable as well. These hold onto GDI objects, not window handles. If you leak these objects, it can cause quite a perf problem in your application as System.Drawing may start to GC extra times to get the number of handles back in check. SystemPens and SystemBrushes do not need to be disposed – these are cached objects. SystemFonts do as they are a live fetch from the OS of the current MenuFont etc.

However, if you are creating a new control that holds onto its own IDisposable object (e.g. it’s own tooltip), that control should override Dispose(bool disposing) and add calls to Dispose() for these objects.

If you are concerned about a leak in your code – bring up task manager, switch to processes. View->Select Columns and tick off USER objects and GDI objects. This should show you the live count of how many handles you have out.

In summary

Modeless Form (Application.Run(new Form)) - NO
SystemPens, SystemBrushes - NO

Modal Form (Form.ShowDialog) - YES
Dynamic panel that’s swapped in and out - YES
Pens, Brushes, Fonts, Graphics objects - YES
Regions, other advanced System.Drawing objects - YES
SystemFonts (these are not cached) - YES
Timers, Tooltips, other components - YES

Next time: interesting uses of dispose

 

Dispose - Part 4
Cool things you can do with dispose

Now that we're all convinced that using Dispose is the greatest thing next to sliced bread, lets look at some syntatic sugar in C# that makes life just that much better.

If you are thinking of creating an IDisposable object, then disposing it within the same function, the conscientious thing to do is something like this:


Graphics g = Graphics(this.Handle);
try {
   g.DrawRectangle(..);
}finally {
   if (g != null) {
      g.Dispose();
   }
}


It turns out that C# already has a keyword for this - the using statement. The same thing can be expressed as such:


using (Graphics g = Graphics.FromHwnd(this.Handle)) {
   g.DrawRectangle(..);
}


It turns out this syntax is also great for defining things like transactions...

using (new MyTransaction()) { ... }


where the MyTransaction class or struct implements IDisposable. If this is the common usage of MyTransaction, consider making it a struct instead of a class so your object will be allocated on the stack, not the heap.

 

Links:

IDisposable.Dispose remarks section

Performance Considerations for Run-Time Technologies in the .NET Framework

Writing High-Performance Managed Applications : A Primer

Implementing Finalize and Dispose To Clean Up Unmanaged Resources

C# Language Specification: The using statement

 

Please note: The following article was extract from http://www.windowsforms.net

Setup for Performance Testing: Clear cache, buffers (MSSQL) [sql] [testing] [performance] [mssql]

For Microsoft SQL (MSSQL).
Use this to clear the cache and buffers to ensure comparison are accurate.

dbcc freeproccache
go
dbcc dropcleanbuffers
go