T/SQL

Checking free disk space from T/SQL

I’ve seen some very creative ways to see how much free disk space SQL Servers have.  Most make use of xp_cmdshell which some very complex dos commands or vbscripts.

However there is a much easier way.  Microsoft has included the system extended stored procedure xp_fixeddrives.  It returns the all the fixed drives and the amount of free space in megs.

Denny

New INSERT syntax in SQL Server 2008

One of the very cool new feature which SQL Server 2008 gives us is an change to the INSERT statement.  Now you can specify multiple rows to insert into a table from a single insert command.

The syntax is:
CREATE TABLE TableName (Column1 INT, Column2 VARCHAR(10))
INSERT INTO TableName
(Column1, Column2)
VALUES
(1, ‘test1′), (2, ‘test2′), (3, ‘test4′)

I see this as being a very handy especially when doing an initial data load into a table as you can now load lots of data without having to run a lot of seperate insert statements.

Denny

Back To Basics: The UPDATE Statement

After you’ve inserted the data into the table, it’s time to update the data.  We do this by using the UPDATE statement.  The update statment can be used in two ways.  The first is to update a record or set of records in a single table, by simply filtering the data in the table by using values in the table.

UPDATE TableName
SET Column1 = ‘Value’
WHERE AnotherColumn = ‘AnotherValue’

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = TableName.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = TableName.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.

Denny

Back To Basics: The INSERT Statement

While the SELECT statement is probably the most important command, the INSERT comes in handy.  The INSERT statement is used to do exactly what it sounds like, it inserts data into a table.

 There are two ways to insert data into a table.  The first is to pass in each of the values, and the second is to insert the data from a select statement.

For both commands we’ll be using a new table with this definition.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertTable')
DROP TABLE InsertTable
GO
CREATE TABLE InsertTable
(id INT,
name sysname)

First lets look at passing in the values. With this syntax we specify the names of the columns, and then specify each of the values.

INSERT INTO InsertTable
(id, name)
VALUES
(0, ‘test’)

Second we’ll look at the SELECT statement. There are two ways we can do this as well. The first is to load a single set of values with the select statement. When doing this you can optionally specify the column names or not.

INSERT INTO InsertTable
SELECT 0, ‘test’

The second option with the SELECT statement is to use a SELECT statement from a table. All of the functionally of the SELECT statement is available when using the SELECT statement as part of the INSERT statement.

INSERT INTO InsertTable
SELECT id, name
FROM sysobjects

We can also do this with some of the more advanced functions of the SELECT statement.

INSERT INTO InsertTable
(name, id)
SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the INSERT statement. It includes more examples, and some of the other options which are available to you.

Denny

Back To Basics: Using Common Table Expressions

CTEs (Common Table Expressions) are one of the very cool features introduced in SQL Server 2005.  In there simplest most common form, think of them as a temporary single use view who’s context is only within the command which follows them directly.  The syntax of a CTE is very basic.

WITH CTE_Name (ColumnName, ColumnName) AS
(SELECT *
FROM Table)
SELECT *
FROM CTE_Name

The list of column names as part of the CTE defination is optional.  If all the columns are named this portion is not needed.  Here is an example from the AdventureWorks database.

WITH EmployeeData AS
(
SELECT e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle] ,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName] ,a.[PostalCode]
,cr.[Name] AS [CountryRegionName] ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] eINNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID] INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode])
SELECT *
FROM EmployeeData
WHERE CountryRegionName = ‘United States’


When done correctly CTEs can be used to link back to themselves to join child data up the chain so you can access the parent record. This is called a recursive common table expression and is done with a UNION ALL between two queries within the CTE like so.

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevelFROM HumanResources.EmployeeWHERE ManagerID IS NULL

UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1FROM HumanResources.Employee e
INNER JOIN DirectReports dON e.ManagerID = d.EmployeeID)

SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
The first part of the UNION ALL command shows us the top level employees who have no manager. The second query is used to link back to the managers to show the employee information including how many levels down the chain the record is.

Extreme care must be used when using recursive common table expressions as doing this incorrectly can put the SQL Server into a never ending loop while SQL is trying to recurse up the never ending tree.

Denny

Back To Basics: The SELECT Statement

There are four basic commands in databases.  They are SELECT, INSERT, UPDATE and DELETE.  Probably the most important of these is the SELECT command.  The SELECT command is how the data in the database is retrieved and displayed.

(All these code samples can be run on all versions of Microsoft SQL Server from 7.0 up.)

Like a regular sentence there are a few basic parts of the SELECT statement.  First there is a list of columns that you want to see.  Then there is the FROM portion of the statement.  This is the table or tables which you will be getting the data from.  Then is the WHERE portion of the statement which is the data filter.

When you put all this together the SELECT statement will look something like this.

SELECT id, name
FROM sysobjects
WHERE type = ‘U’

If you were going to read this as a normal sentence it would read something like:
I want to see the "id" and "name" columns, from the "sysobjects" table, where the "type" column has a value of "U".

Now if we need to look at data from two different tables which is combined together we add a JOIN statement between the FROM and WHERE portions of the SELECT statement.

SELECT sysobjects.name, syscolumns.name
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’

You can see from the above statement that we are getting the table names and column names for all the user tables. When you look at the JOIN command you see that we are matching up the id column from both of the tables. Now in this example the column name is the same in both tables. This isn’t always the case. If you don’t know how the tables relate to each other, you can usually look at the foreign key constraints to see how the tables relate to each other.

If you want to get counts of data you can use the GROUP BY clause along with the COUNT() function. In this next statement we will count the number of columns in each table.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’
GROUP BY sysobjects.name

When ever you use a mathematical function such as MIN(), MAX(), AVG(), COUNT(), etc you have to add the GROUP BY clause to the SELECT statement so that SQL Server knows how to roll up the data.

Now as a last part of the SELECT statement to look at today, we are going to filter our GROUP BY query further, by showing all the tables that have over 5 columns. To do this we use the HAVING clause. Without the HAVING clause we would have to do a very complex query as a sub-query in the WHERE clause which would be very inefficient.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’
GROUP BY sysobjects.name
HAVING count(*) > 5

The HAVING phrase is very simple in it’s syntax. You put the math function that you want to use (in this case COUNT(*)) then <, >, <> or = and what ever you want to compare it to.

I hope that you find this post useful. I’ve barely scratched the surface of the SELECT statement, and what it can do. I encourage everyone to open up Books OnLine and read through the information on the SELECT statement. It includes more examples, and some of the other options which are available to you.

Denny

Log Shipping without SQL Server Enterprise Edition

Microsoft’s Log Shipping is pretty good.  But it requires that you have SQL Server Enterprise Edition on both the machines.  This makes the solution fairly expensive.  Because of this I’ve written a replacement which can be used on any edition of SQL Server including SQL Server Express Edition (as long as you use a different scheduler).

I recommend keeping the drive letters the same on the two machines, but this isn’t required.

First backup the database to log ship and restore it to the backup server using the WITH STANDYBY option.  You are now prepped to start shipping the transaction log.

Setup a job on the primary server which backs up the log every few minutes to a network share on the backup server.  I use code alone these lines.

backup log Northwind to disk='\\backupsql\BackupFolder\northwind.log' with NOINIT, NOSKIP, NOFORMAT
go

Add a second step to the job which uses osql to start a job on the backup server.  Something like this.  (The on failure action should be to Quit with Success for this step.)

osql -S BackupSQL -E -Q "msdb.dbo.sp_start_job 'Restore Northwind Log'"

Setup a restore job on the backup server (making sure that the name matches the job name in the second step of the backup job.  The restore job will have four steps in it.

Step 1 (T/SQL):

/*This first part of the code ensures that no one is using the database that we are about to restore.  If we don't do this then the restore will fail.*/
declare @spid as varchar(10)
declare @CMD as varchar(1000)
declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid =
        (select dbid from sysdatabases where name = ‘Northwind’)
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
    set @CMD = ‘kill ‘ + @spid
    exec (@CMD)
    fetch next from cur into @spid
END
close cur
deallocate cur
go


Step 2 (OS Command):

del d:\RestoreFolder\Northwind.2.log
REM /*This removed the last file we processed.*/

Step 3 (OS Command):

move d:\RestoreFolder\Northwind.log d:\RestoreFolder\Northwind.2.log
REM /*This moves the current file into place for processing.*/


Step 4 (T/SQL):

declare @i int
declare @j int
set @j = 1
set @i = 0
restore headeronly from disk=’d:\RestoreFolder\Northwind.2.log’ /*This tells us how many transaction log backups there are in the file that we need to restore.*/
set @i = @@ROWCOUNT
while @i+1 > (@j)  /*This loop runs us through the file restoring the logs in order.*/
BEGIN
    restore log Northwind from disk=’d:\RestoreFolder\Northwind.2.log’
        WITH FILE = @j,
            STANDBY = ‘F:\MSSQL\Backup\RMDBArchive.sby’ /*This keeps the database in standby mode ready for more restores.*/
    set @j = @j + 1
END

If your folders don’t exist between the two servers you’ll need to add the MOVE parameter to the restore commands.

If you do being using this please post a note to the comments if you can.  I like to know who is using this log shipping code.

XML in the database. What’s the big deal any way?

So, we’ve had the ability to use XML within the SQL Server databases since SQL 2000 came out.  What’s the big deal you ask?  How can I use it to make my life easier you also ask?

When I first started looking into this whole XML thing I was thinking the same thing.  How is this blob of data going to make my life easier?  Well when used correctly it most certenly can.  In SQL Server (up through SQL 2005 at least) we don’t have a way to pass more than one record into a stored procedure.  If you have an order entry system and the custom wants to order 3 items you have to run three commands on the SQL Server, doing three inserts one at a time.  If you instead passed those three items from the UI to the database as an XML document you could then process all three items in a single command thereby improving performance.

declare @XML XMLSET @XML = ‘<ROOT><Customer ID=”12″>
<Order ID=”7498″>
<Item ID=”415″ Qty=”1″ />
<Item ID=”87547″ Qty=”2″ />
<Item ID=”4125″ Qty=”1″ />
</Order>
</Customer>
</ROOT>’
DECLARE @hDoc INT
exec sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML (@hDoc, ‘//Customer/Order/Item’)
WITH (CustomerId INT ‘../../@ID’,
OrderId INT ‘../@ID’,
ItemId INT ‘@ID’,
Qty INT ‘@Qty’)
exec sp_xml_removedocument @hDoc
As you can see by the 0utput we have a table which we can process in bulk.  Instead of running a sample piece of code if we were to build this into a stored procedure we could do something like this.

Here is our LineItems table.

CREATE TABLE LineItems
(OrderId INT,
ItemId INT,
Qty INT,
ItemPrice NUMERIC(6,2))
And here is our procedure which adds the XML data to the LineItems table.  In a production system business logic would need to be added to ensure stock on hand, and to return estimated ship dates to the client.
CREATE PROCEDURE usp_AddLineItems
@Items XML
AS
DECLARE @hDoc INT


exec sp_xml_preparedocument @hDoc OUTPUT, @Items


INSERT INTO LineItems
SELECT OrderId, ItemId, Qty, ItemPrice
FROM OPENXML (@hDoc, ‘//Customer/Order/Item’)
WITH (CustomerId INT ‘../../@ID’,
OrderId INT ‘../@ID’,
ItemId INT ‘@ID’,
Qty INT ‘@Qty’,
ItemPrice NUMERIC(6,2)
‘@ItemPrice’) exec sp_xml_removedocument @hDocGO

Since we don’t want to create a web based order system for this little demo here we can run the procedure and verify the output.

declare @XML XMLSET @XML = ‘<ROOT><Customer ID=”12″>
<Order ID=”7498″>
<Item ID=”415″ Qty=”1″ ItemPrice=”12.95″/>
<Item ID=”87547″ Qty=”2″ ItemPrice=”16.25″/>
<Item ID=”4125″ Qty=”1″ ItemPrice=”8.25″/>
</Order>
</Customer>
</ROOT>’
EXEC usp_AddLineItems @XMLGO
SELECT *
FROM LineItemsGO

As we can see this is much more efficient than having to run three insert statements as they would require at least three rights to the disk, plus how ever many reads are needed to do the business logic. By using the XML we can do it all with a single write to the disk, and a single set of reads for the business logic. In this example we have reduced our disk activity by 33%. If we were processing 10 line items we would have reduced our disk activity 90%. And since disk performance is usually the bottleneck of any database reducing the disk IO any way we can is a good thing.

Denny

Avoiding SQL Injection Attacks

The most common way for people to insert invalid data or cause damage to your database is through what is called a SQL Injection Attack.  This is when malicious code is placed within the responses which are expected and that code is not caught and it instead executed.  Depending on what level of access to the SQL Server your application account has will determine how successful the injection attack will be.

There are ways to protect your self.  The first and most important is that the account which the application uses to connect to the database should have the least set of rights necessary to do it’s job.  Don’t make the account dbo or sysadmin just so that everything works correctly without you having to modify the rights when you add objects.  The second requires your front end developers (the ASP, .NET, etc folks) to understand the potential problem so that they code the application in such a manor that the risk is reduced or removed.

(Please keep in mind that I’m not an application coder so my application code shown below may not be perfect.)

If they use in-line T/SQL commands such as this:

dim v_ado, v_sql, v_conn
v_conn = “Provider=sqloledb;Data Source=sqlserver;Initial Catalog=AdventureWorks;User ID=UserName;Password=password;”
v_sql = “exec usp_Something @var1=’” & request.form(“Var1″) & “‘, @var2=’” & request.form(“Var1″)
set v_ado = CreateObject(“ADODB.RecordSet”)
on error resume next
v_ado.open v_sql, v_conn, 3, 3
…

Then you are open to an injection attack.  This is because I can simply put “‘; SELECT * FROM sys.tables” as one of the form fields which I am submitting back to you and my SELECT statement will be executed against your SQL Server.  Depending on how your rights are setup will then determine how successful my command is.  The configuration of your UI will then determine what data if any is returned back to me through the UI.  If I find that I have rights to do stuff and I can get the UI to return data to my I can then have the SQL Server return me your customer data, or if I’m feeling like causing you some headaches I can drop all your tables by using the correctly crafted code.

The best way to not be open to these attacks is to use parametrized code on the front end.  This allows you to pass in your variables but without leaving the site open to attack as a T/SQL command isn’t passed directly to the SQL Server.  This is done using the technique shown here.

Dim v_ConnString As String
v_ConnString = “Server=servername;Database=AdventureWorks;uid=username;pwd=password;”
Dim v_Connection As New SqlConnection(v_ConnString)
Dim v_Command As New SqlCommand(“sp_helpdb”, v_Connection)
v_Command.Parameters.Add(“var1″)
v_Command.Parameters(“var1″).Value = request.form(“Var1″)
v_Command.CommandType = CommandType.StoredProcedure
v_Connection.Open()

If your developers insist on using the first method all is not lost.  There is still a method which can be used to help protect the database.  Each value which is returned from the end user has to be validated.  Any single quote values should be replaced with two single quote values.  This way anything which the malicious code executor tries to push through is turned back into a string value.  Also all inputs regardless of data type should be surrounded by single quotes.  If this isn’t done then when passing in a number value the attacker can easily run there own code without having to get caught by the single quote check.

dim v_ado, v_sql, v_conn
v_conn = “Provider=sqloledb;Data Source=sqlserver;Initial Catalog=AdventureWorks;User ID=UserName;Password=password;”
v_sql = “exec usp_Something @var1=” & request.form(“Var1″) & “, @var2=’” & request.form(“Var1″)
set v_ado = CreateObject(“ADODB.RecordSet”)
on error resume next
v_ado.open v_sql, v_conn, 3, 3
…

If I then set the value of the Var1 form element to “6; select * from sys.tables” I will be able to query all the tables and my code won’t ever be caught by the single quote check.

Unforunately once the data has been submitted to the database, there isn’t much which can be done within SQL to decide is there is malicious code within the string.  Mostly because your stored procedure probably won’t ever see the malicious code.  The attacker will terminate the stored procedure and run their command after (using the technique I showed above) so any potential checking that you did within the procedure won’t do anything.

These techniques are not foolproof.  The best protection method against an injection attack is to parametrized code method shown above.  No matter what the attacker places in the form fields it won’t be executed as the engine (ASP, .NET, JSP, PHP, etc) will simply take the value and add it as a parameter to the procedure no matter how the attacker tries to escape out to run there own code.  Protection the database from an injection attack is a shared responsibility between the DBA and the front end developer.  Neither person or team should try to put the entire responsibility on the other team.  DBAs need to make sure that object rights are as minimal as possible, and front end developers need to make sure that the commands never get sent to the database in the first place.

Have you seen any other techniques which can be used to protect the database.  Share them in the comments below.

Denny

New Article: SQL Server encryption vs. hashing for data security

I’ve published a new tip over on SearchSQLServer.com entitled “SQL Server encryption vs. hashing for data security“.

(It was published a couple of days ago, I just didn’t get notice until today.)

Denny