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

Monday, November 9, 2009

Dissable the network card from a DOS command

netsh interface set interface "Local Area Connection" DISABLE

 

netsh interface set interface "Local Area Connection" ENABLE

 

Tuesday, September 22, 2009

Microsoft SQL Server 2005 Database Encryption Step-by-Step


Microsoft SQL Server 2005 Database Encryption Step-by-Step
This is a how-to guide which will aims to help ms sql server developers and ms sql server administrators to implement Microsoft SQL Server 2005 Encryption methodologies.
This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later (MS SQL2008 aka Katmai)
Creating Master Key
database master key from sys.symmetric_keys
Before using encryption algoritms in SQL Server 2005 and SQL Server 2008, a master key should be created in the database where encryption is going to be used.
Note that master key is created seperately for each database on a SQL Server database instance
Before creating a master key, sql developers or sql server database administers that has the required permissions can run the below t-sql select query to see if a master key is created before.
SELECT * FROM sys.symmetric_keys
If there has been created a master key, you will see a result that is similar to below if you are running the t-sql select from sys.symmetric_keys view in MS SQL Server 2005,

Note that in SQL2008 (CTP6 or February CTP) sys.symmetric_keys view has additional columns:

name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid
key_thumbprint
provider_type
cryptographic_provider_guid
cryptographic_provider_algid

It is important that for a database in MS SQL Server, there can be only one master key in other words a single master key can be created on a database. A second master key can not be created in a sql server database.

You can use the below t-sql script code in order to create a master key in the sql database.
/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'
GO
It is important that you keep the encryption password in a safe place or keep backups of your sql server database master key.
I'm going to deal with master key backup later in this tutorial.

You can drop or remove an existing master key using the DROP MASTER KEY t-sql command.
If you try to drop a master key which has been used for creating other database objects like certificates the DROP MASTER KEY sql command will fail.
If you run the DROP MASTER KEY sql server statement after a certificate is defined which we will see in the next step, the following error message is going to be informing the sql server programmer about the dependent certificate.
Msg 15580, Level 16, State 1, Line 2
Cannot drop master key because certificate 'PasswordFieldCertificate' is encrypted by it.




Microsoft SQL Server 2005 Database Encryption Step-by-Step

Wednesday, August 12, 2009

SQL Server server Script which will shrink all databases


DECLARE @cmd1 varchar(2000),
@cmd2 varchar(2000)

SET @cmd1 = 'BACKUP LOG [?] WITH NO_Log'
SET @cmd2 = 'DBCC SHRINKDATABASE ([?],TRUNCATEONLY)'

EXEC sp_msforeachdb @command1 = @cmd1,
@command2 = @cmd2


Tuesday, August 4, 2009

Send Email via Telnet

WARNING: Contains ferociously nerdical content
On occasion, we write code that sends email. Sometimes, it actually works the first time.
More often, we need to figure out why not.
While it's nicest to have the computer emulate what the human would get bored doing, sometimes it helps to have the human do what the computer can't quite seem to do on its own.
For that reason, we sometimes resort to manual telnet sessions with a remote mail server.
Simple manual telnet session with mail host
Uppercase/lowercase does not appear to be significant.
You do/type this
Server responds with
Telnet to hostname on port 25
220 (then identifies itself - possibly with several lines of 220 + text)
HELO your_domain_name or whatever
250 (followed by human readable message)
MAIL FROM:you@hostname.com (ie, your email address)
250 is syntactically correct (or similar)
RCPT TO:them@someplace_else.com (email address you want to send to)
250 is syntactically correct
DATA
Tells you to send data then CRLF period CRLF at end
You type your message then CRLF period CRLF (ie, type a period on a line by itself then hit ENTER)
250
QUIT
Signoff message