CodeName Katmai (This is NOT the National Park in Alaska!!!)

Katmai is a Microsoft codename assigned to the next version of its Relational database management system, Microsoft SQL Server. The release date is unknown and very few details about the product are known. Interviews with Microsoft executives, however, hint that there is a move towards "unstructured data". Furthermore, Microsoft has hinted that WinFS, the future file system for Windows, would no longer exist as a separate product offering, but many of its features would be rolled into Katmai. Katmai and other subsequent releases of SQL Server will be essential to Microsoft's Data Platform Vision, a campaign targeted at fully integrating disparate, yet related, types of data.

Known Feature Overview
Unstructured Data - Support beyond text, numbers, and XML; images, music, etc.
Integration into Windows - Because WinFS has been integrated into SQL server, it has been hinted that Katmai will be integrated into a future release of Windows as well.

External links
[1] - Interview with Microsoft's Flessner.
[2] - WinFS Team Blog about WinFS rollup into Katmai and other various products.
[3] - Data Platform Vision

Auditing Data Changes on SQL Server Tables with Triggers

First we need a table:

CREATE TABLE dbo.AuditEmployee
(
EmployeeID INT,
UserName VARCHAR(255),
Operation CHAR(1)
CHECK Operation IN ('U', 'I', 'D', 'S'),
dt SMALLDATETIME NOT NULL
DEFAULT GETDATE()
)

Keeping the original tables in place, you can have the following:

CREATE TRIGGER Audit
ON dbo.Employee
FOR INSERT, UPDATE, DELETE
AS
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
IF (SELECT COUNT(*) FROM deleted) > 0
BEGIN
-- update!
INSERT AuditEmployee
(EmployeeID, UserName, Operation)
SELECT EmployeeID, SUSER_SNAME(), 'U'
FROM inserted
END
ELSE
BEGIN
-- insert!
INSERT AuditEmployee
(EmployeeID, UserName, Operation)
SELECT EmployeeID, SUSER_SNAME(), 'I'
FROM inserted
END
END
ELSE
BEGIN
-- delete!
INSERT AuditEmployee
(EmployeeID, UserName, Operation)
SELECT EmployeeID, SUSER_SNAME(), 'D'
FROM deleted
END
GO

So, try out these examples, and you can see how the trigger captured all of your auditing:

INSERT Employee(FullName, Salary)
VALUES('Aaron Bertrand', 10000)
INSERT Employee(FullName, Salary)
VALUES('Joe Schmoe', 15000)
UPDATE Employee SET salary = 30000
WHERE EmployeeID = 1
-- handles multiple rows as well!
-- let's give everyone a raise to demonstrate
UPDATE Employee SET Salary = Salary + 2000
DELETE Employee WHERE EmployeeID = 2
SELECT * FROM Employee
SELECT * FROM AuditEmployee

SQL Server Magazine Events

Upcoming Live Web Seminars
Moving to 64-Bit SQL Server 2005

Make the most of your SQL 2005 systems - deploy them on a 64-bit platform. Join Rick Heiges for this free Web seminar and learn which versions of SQL 2005 have native 64-bit support, and how to leverage additional performance and scalability opportunities on the 64-bit architecture. Are you ready to migrate from 32-bit SQL Server to a new 64-bit platform? Register today!

Live Event: Friday, September 29
SQL Server Clustering for High Availability

Achieve your database high availability goals. Learn how Microsoft's Clustering Services can be used to improve SQL Server availability, and get guidelines for how clusters can assist with your disaster recovery strategy. Also learn to upgrade and manage your existing clusters. Register today for this free Web seminar!

Live Event: Thursday, October 19
On-Demand Web Seminars
Expect the Unexpected: Disaster Recovery for your Windows-based Applications
Learn to differentiate between alternative solutions to disaster recovery for your Windows-based applications, and to ensure seamless recovery of your key systems ? whether a disaster strikes just one server or the whole site. On-Demand Web Seminar
Special Offer
SQL Server Guide to Clustering Alternatives - Free Whitepaper!
Want the convenience of a server cluster without the expense? Learn about server cluster alternatives that provide high availability, preventative maintenance, and fallover capabilities at pricing that fits your budget. Download the whitepaper today!

SQL Power Injector v1.1

SQL Power Injector is a graphical application created in .Net 1.1 that helps the penetrating tester to inject SQL commands on a web page.
For now it is SQL Server, Oracle and MySQL compliant, but it is possible to use it with any existing DBMS when using the inline injection (Normal Mode).
Moreover this application will get all the parameters you need to test the SQL injection, either by GET or POST method, avoiding thus the need to use several applications or a proxy to intercept the data.

Features:

• Supported on Windows, Unix and Linux operating systems
  SQL Server, Oracle, MySQL and Sybase/Adaptive Server compliant
  SSL support
  Load automatically the parameters from a form or a IFrame on a webpage (GET or POST)
  Detect and browse the framesets
  Option that auto detects the language of the web site
  Find automatically the submit page(s) with its method (GET or POST)displayed in a different color
  Single SQL injection
  Blind SQL injection
  Comparison of true and false response of the page or results inthe cookie
  Time delay
  Response of the SQL injection in a customized browser
  Fine tuning parameters injection
  Can parameterize the size of the length and count of the expectedresult to optimize the time taken by the application to execute the SQLinjection
  Multithreading
  Option to replace space by empty comments /**/ against IDS or filterdetection
  Automatically encode special characters before sending them
  Automatically detect predefined SQL errors in the response page
  Automatically detect a predefined word or sentence in the response page
  Real time result
  Possibility to inject an authentication cookie
  Can view the HTML code source of the returned page
  Save and load sessions in a XML file

You can find out more here:
SQL Power Injector
Download the latest version now.

Ninja - SQL Injection Tool

SQLninja is a little toy that has been coded during a couple of pen-tests done lately and it is aimed to exploit SQL Injection vulnerabilities on web applications that use Microsoft SQL Server as their back-end.

It borrows some ideas from similar tools like bobcat, but it is more targeted in providing a remote shell even with paranoid firewall settings.

It is written in perl and runs on UNIX-like boxes.
Here’s a list of what it does so far:
Upload of nc.exe (or any other executable) using the good ol’ debug script trick
TCP/UDP portscan from the target SQL Server to the attacking machine, in order to find a port that is allowed by the firewall of the target network and use it for a reverse shell
Direct and reverse bindshell, both TCP and UDP
DNS-tunneled pseudoshell, when no TCP/UDP ports are available for a direct/reverse shell, but the DB server can resolve external hostnames.

Being an alpha version and since it was originally supposed to be just a quick&dirty toy for a pentest, there are lots of bugs waiting to be found and fixed so go ahead and download it !
More tunneling options (e.g.: HTTP, SMTP, …) will be added in the future together.

You can read more and download sqlninja here:ttp://sqlninja.sourceforge.net/

SQL Server T-SQL LPAD & RPAD Functions (String Padding Equivalent to PadLeft & PadRight)

Here is my method for achieving left and right string padding in the Microsoft SQL Server T-SQL language. Unfortunately T-SQL does not offer functions like Oracle PL/SQL's LPAD() and RPAD() and C#'s PadLeft() and PadRight() functions. However, you can achieve the same thing using the T-SQL REPLICATE and LEN functions. Suppose you have a numeric column called Quantity and you need to return it as a string left-padded with 0's to exactly ten characters. You could do it this way:

SELECT REPLICATE('0', (10 - LEN(CAST(Quantity, VARCHAR)))) + CAST(Quantity, VARCHAR) AS PaddedQuantity FROM TableX

The calls to the CAST function are based on the assumption that the value you padding is numeric. If Quantity were already an string you could do it like this:

SELECT REPLICATE('0', (10 - LEN(Quantity))) + Quantity AS PaddedQuantity FROM TableX

In certain cases you might be concerned that that value you want to pad might be wider than your maximum number of characters. In that case you could use a CASE block to check the LEN of your input value to avoid passing a negative result to the second argument of the REPLICATE function. No need to worry about passing a 0 to REPLICATE, though: it will simply return an empty string, which is what you'd want since no padding would be necessary.
Update: I decided to go ahead and turn these into user defined functions. Here is a script for fnPadRight and fnPadLeft:

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadRight]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadRight]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadRight]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadRight]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO