Friday, November 20, 2009
SQL SERVER 2005 builtin Encryption
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' )
Monday, November 16, 2009
Auditing Through Triggers (SQL Server)
|
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
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. | Rule of thumb: |
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]
Use this to clear the cache and buffers to ensure comparison are accurate.
dbcc freeproccache
go
dbcc dropcleanbuffers
go