Blog Archives

Query Sql Version Microsoft MSSQL

    set nocount on
    go
    select @@version
    go
    select 'Edition: ' + convert(char(30), serverproperty('Edition'))
    go  
    select 'Product Version: ' + convert(char(20), serverproperty('ProductVersion'))
    go
    select 'Product Level: ' + convert(char(20),serverproperty('ProductLevel'))
    go
    set nocount off
    go

References
https://support.microsoft.com/kb/321185

http://blogs.msdn.com/b/euanga/archive/2008/01/23/confused-by-sql-server-version-numbers.aspx

Advertisements

Custom DAL Class SQL ORM ASP .NET

(common.DataObject may be of your choosing or may simply replace with dynamic)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Data.SqlClient;

using AIS.Common; //this is a common namespace I use in examples throughout my site
using System.Reflection;
using System.Dynamic;

//TODO: consider returning ienumerable in sp return values for lazy eval vs .tolist immediate eval
namespace AIS.DAL.AppName
{
    public static class StoredProcedures
    {
        public delegate void ErrorHandler(Exception ex);
        /// <summary>
        /// If no custom error handling is bound to this event, exceptions will be thrown back up to the calling function.
        /// If custom handling is bound to this event, ensure it does not perform a redirect or kill the thread unless you intend to abort the procedural
        /// steps following the method/function call which threw the error.
        /// </summary>
        public static event ErrorHandler HandleError;

        #region Unique Procedures
        public static List<Common.DataObject> LoadUserSessions_All(dynamic o)
        {
            return ExecuteRead("an_get_db_fn1", o);
        }

        public static List<Common.DataObject> LoadUserSessionsDetails_LiveStream(dynamic o)
        {
            return ExecuteRead("an_get_db_fn2", o);
        }

        public static List<Common.DataObject> LoadUserSessionsDetails_Live(dynamic o)
        {
            return ExecuteRead("an_get_db_fn3", o);
        }

        public static int LogChat()
        {
            return ExecuteScalar("an_get_db_fn4", null);
        }

        public static int LogError()
        {
            return ExecuteScalar("an_get_db_fn5", null);
        }
        #endregion

        //TODO: consider hiding from external assemblies which would require strong mappings above
        #region Execution Logic
        public static List<Common.DataObject> ExecuteRead(string procedurename, dynamic param)
        {
            try
            {
                SqlDataSource sds = new SqlDataSource();
                sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
                sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
                sds.SelectCommand = procedurename;

                if (param != null)
                {
                    foreach (PropertyInfo pi in param.GetType().GetProperties())
                    {
                        object pval = pi.GetValue(param, null);
                        if (pval != null)
                        {
                            sds.SelectParameters.Add(pi.Name, pval.ToString());
                        }
                    }
                }

                List<Common.DataObject> results = new List<Common.DataObject>();
                //sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<dynamic>(o, ref results));
                sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<Common.DataObject>(o, ref results));

                return results;
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
                return null;
            }
        }

        public static void Load_AddResult<t>(Common.DataObject o, ref List<t> results)
        {
            try
            {
                t r = (t)Activator.CreateInstance(typeof(t));

                foreach (PropertyInfo pi in typeof(t).GetProperties())
                {
                    object v = o[pi.Name].ToString();
                    Type pt = Type.GetType(pi.PropertyType.FullName);
                    //try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
                    //catch (Exception ex) { HandleError_Condensed(ex); }

                    o.Add(pi.Name, Convert.ChangeType(v, pt));
                }

                results.Add(r);
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
            }
        }

        //public static void Load_AddResult<t>(dynamic o, ref List<t> results)
        //{
        //    try
        //    {
        //        t r = (t)Activator.CreateInstance(typeof(t));

        //        foreach (PropertyInfo pi in typeof(t).GetProperties())
        //        {
        //            object v = o[pi.Name].ToString();
        //            Type pt = Type.GetType(pi.PropertyType.FullName);
        //            try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
        //            catch (Exception ex) { HandleError_Condensed(ex); }
        //        }

        //        results.Add(r);
        //    }
        //    catch (Exception ex)
        //    {
        //        HandleError_Condensed(ex);
        //    }
        //}

        public static void ExecuteNonScalar(string procedurename, dynamic param)
        {
            try
            {
                ExecuteScalar(procedurename, param);
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
            }
        }

        public static int ExecuteScalar(string procedurename, dynamic param)
        {
            try
            {
                SqlDataSource sds = new SqlDataSource();
                sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
                sds.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
                sds.UpdateCommand = procedurename;

                if (param != null)
                {
                    foreach (PropertyInfo pi in param.GetType().GetProperties())
                    {
                        object pval = pi.GetValue(param, null);
                        if (pval != null)
                        {
                            sds.SelectParameters.Add(pi.Name, pval.ToString());
                        }
                    }
                }

                return sds.Update();
            }
            catch (Exception ex)
            {
                HandleError_Condensed(ex);
                return 1; //1 signifies error in tsql
            }
        }
        #endregion

        private static void HandleError_Condensed(Exception ex)
        {
            if (HandleError != null) { HandleError(ex); } else { throw new Exception(ex.Message, ex); } 
        }
    }
}

MSSQL User Defined Functions vs Stored Procedures

I received this question earlier today, and thought it was a valid question often misunderstood, and deserving of a small write-up:
“Should a User Defined Function be your first choice instead of a Stored Procedure?”

While there are many pros and cons of each not covered in this write-up (review your versions on MSDN for details), including some features which may not be apparent until you have an issue to troubleshoot (such as sp_who filtering), you can generally ask yourself a single question up front that can help you determine which you should use.

Simply, if the db functionality you need to implement in the function/procedure requires
any DML (insert/update/delete), then go with a stored procedure. Advanced selects and/or filters are best left up to views/table valued functions.

Additionally, do not be afraid to use a combination of functions and procedures especially if there is a goal of re-usability, in accordance with the design considering the planned growth of your db as your software & db architecture permits. On that note, consider and test the performance differences of these implementations, as a compiled/cached function/procedure containing more logic internally may outperform one utilizing logic that is spread throughout.

References
SP_who filtering UDF vs SP, http://stackoverflow.com/questions/2567141/use-sql-to-filter-the-results-of-a-stored-procedure
Data Manipulation Language, http://en.wikipedia.org/wiki/Data_manipulation_language
(Some) Differences (about.com), http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm
(Some) Differences (stackoverflow), http://stackoverflow.com/questions/2039936/difference-between-stored-procedures-and-user-defined-functions

mssql aes function encrypt decrypt in microsoft sql

Simple symmetric encryption in MSSQL. Copied from MS references below:

USE AdventureWorks2012;
--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE Sales09
   WITH SUBJECT = 'Customer Credit Card Numbers';
GO

CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Sales09;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard 
    ADD CardNumber_Encrypted varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.  
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
    , CreditCardID)));
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.

SELECT CardNumber, CardNumber_Encrypted 
    AS 'Encrypted card number', CONVERT(nvarchar,
    DecryptByKey(CardNumber_Encrypted, 1 , 
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
    AS 'Decrypted card number' FROM Sales.CreditCard;
GO

References:

http://msdn.microsoft.com/en-us/library/bb669072.aspx

http://msdn.microsoft.com/en-us/library/ms179331.aspx

Arithmetic overflow error converting type to data type numeric

This error usually occurs when the type you are trying to convert to a decimal or numeric is larger than the column or casted type you are converting to.

Ex.

insert into tablename (decnumber) values (cast(1000.00 as decimal(5,2)))

Since the number specifed is 7 digits long include after the decimal and the converted type can only contain a maximum of 5 digits, you will receive this error.

Changing the table to hold 7,2 and/or changing this in the cast will resolve it.

References
devshed (forums), http://forums.devshed.com/ms-sql-development-95/arithmetic-overflow-error-converting-numeric-to-data-type-numeric-89157.html

DLL in Stored Procedure MSSQL

Extended Stored Procedures (MSDN), http://support.microsoft.com/kb/190987

Thread Local Storage in an extended stored procedure (MSDN), http://support.microsoft.com/kb/163449

How to Create secure relational databases in SQL

The first step to a secure DB, is a well structured DB and the application layers that access it.

Take the following example of a table containing factories and the robots they produce.

FACTORYNAME ROBOTNAME
FACTORY1 Ron
FACTORY1 John
FACTORY2 Bob
FACOTRY2 Bill

This serves a good purpose by itself, and allows for easy manipulation and viewing of the stored data, but this table could very quickly grow out of hand. What if you needed to store the parts that are required to make each robot? Or if some factories share the types of robots that they can create?

First, lets separate the data into two separate tables of factories and robots:

“SP_GET_FACTORIES”

FACTORYID FACTORYNAME
1 FACTORY1
2 FACTORY2

“SP_GET_ROBOTS”

ROBOTID ROBOTNAME
1 Ron
2 John
3 Bob
4 Bill

In order to group these two together. we now require a mapping table.
“FactoryRobotMapping”

FACTORYID ROBOTID
1 1
1 2
2 3
2 4

To see this data in a single set, like your original table, you would run a select statement with a join. Some programmers put these statements with joins directly in their code as plain text while others keep them in the database.

Relatively newer technologies, such as Microsoft Linq or Ruby have their own syntax for performing these joins and queries which is ultimately translated to plain sql behind the scenes.

Unfortunately, this often leads to a common misconception that because you are using Linq, Ruby or another data access layer that your work ends there.

Consider the following scenario:
App A->retrieves a join of factories and robots
App B->also retrieves the same join

To make this simpler, you create a new project (this would be your data access layer) and add a new method called “GetRobotsInFactories” which uses Linq, plain sql, ROR or some other technology to run the select with the join.

So now you have:
App A->DAL->GetRobotsInFactories
App B->DAL->GetRobotsInFactories

For future changes, simply modify GetRobotsInFactories, upload the DLL and that’s it! Or is it?

What if a new business change now requires you to only get robots in factories after specific manufacture dates?

You could just add an optional parameter to GetRobotsInFactories, but now you have to code in conditional logic to your function checking if the parameter is null, and forming a different query based on this. If there are 10 more parameters like this, your function could now very quickly become much more troublesome to support, especially when certain combinations of parameters may be required.

This creates a weaker structure, and therefore opens the door to security vulnerabilities. The approach on separating the DAL into a new project and wrapping everything in here is great, but you must be very careful on how your DAL accesses the DB.

What’s the alternative?

Views are joins

For starters, creating views in the database for your joins will save you from having to constantly type out select statements with joins and improve your productivity in database debugging greatly.

An example of such join for our above is:

“VW_JOIN_FACTORIES_ROBOTS”

select * from factories inner join robots on 
factories.factoryid = robots.robotid

This now also allows you to perform specific select statements against the view, which will look very similar to your first table you created before segregating the data.

Cool, now what?

You might consider plugging in a simple “select * from VW_JOIN_FACTORIES_ROBOTS” into that GetRobotsInFactories function, but not done yet.

Stored Procedures offer security

Many developers understand the minimal amount of SQL required to power their app, or have heard of or created and modified views as well as stored procedures, but often are not aware of the security benefits.

Stored procedures encapsulate the data you need to retrieve. This allows you to control not only the tables being modified, but the way they are modified, dependencies, constraints, parameters and even database permissions on who can execute or modify which stored procedures and ultimately obfuscate your underlying data from would be attackers or meddlers.

Consider the following stored procedure:

“SP_GET_ROBOTS_IN_FACTORIES”

USE [RobotCo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIED ON
GO
CREATE PROCEDURE [dbo].[RobotCo] (@MfgDate datetime)
AS
BEGIN
SET NOCOUNT ON;
select * from VW_JOIN_FACTORIES_ROBOTS where datetime between @MdfDate and getdate()
END

You can now change your “GetRobotsInFactories” function to call “SP_GET_ROBOTS_IN_FACTORIES” and simply pass in date as a parameter to the SP. Future changes will only require you to add parameters to the SP and then simply update the DAL/dbml or definition if you’re using linq/ror/etc.

Locking it down

All structures are already in place! You’re next steps are to make sure the applications have their own SQL user accounts with only access to execute the specified stored procedures.

Without direct access to the view or the tables, exploitation of the app will prevent attackers from retrieving or manipulating the data directly.

Hopefully this offered some insight on database level security, as well as understanding some of the benefits of view and SP utilization for better overall software architecture.

References
Microsoft Linq, http://msdn.microsoft.com/en-us/netframework/aa904594
Ruby on Rails, http://rubyonrails.org/

MSSQL Quick Reference

Snippets below have been condensed from their original sources for brevity. See references for original articles.

Great reference on joins: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Visual_SQL_JOINS_V2

Show all tables in specific database: (MSSQL equivalent of MySql “show tables”)

select name from <database name>..sysobjects where xtype = 'U'

Insert into:

INSERT INTO MusicArtists (FirstName, LastName, Instrument)
VALUES ('Bobby', 'Lee', 'fiddle');

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists;

INSERT INTO Duos (Member1) 
    SELECT FirstName + ' ' + LastName FROM MusicArtists 
    WHERE MusicianID > 3;

INSERT INTO Residents (Name, Occupation)
    SELECT Name, Occupation FROM Immigration 
    WHERE Residency = 'granted';

INSERT INTO Insurance (Name) 
    SELECT Employee.Username FROM Employee 
    INNER JOIN Project ON Employee.EmployeeID = Project.EmployeeID 
    WHERE Project.ProjectName = 'Hardwork';

Insert if not exists:

IF NOT EXISTS (SELECT * FROM dbo.Applications WHERE Username = Tom ANDApplication = Calculator) BEGIN
 INSERT INTO dbo.Applications 
 (Date, Username, Application, Version) 
 VALUES
 ('3/10/2009', 'Tom', 'Calculator', '2.0') 
END

Trigger Syntax Structure:

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS 
[ { IF UPDATE ( column ) 
[ { AND | OR } UPDATE ( column ) ] 
[ ...n ] 
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
{ comparison_operator } column_bitmask [ ...n ] 
} ] 
sql_statement [ ...n ] 
} 
} 

Basic If, Then , Else and case:

--longer
create proc sp_generic (@cust int, @type int)
if @type = 1
 select * from customer where customerid = @cust
else
 Select * from deleted_customers where customerid = @cust

--shorter
create proc sp_generic (@deptid int )
select * from employees where departmentid = case when @dept >0 then @dept else departmentid end
order by departmentid

Insert Trigger:

CREATE TRIGGER trig_addAuthor 

ON authors 

FOR INSERT 

AS

-- Get the first and last name of new author 

DECLARE @newName VARCHAR(100) 

SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

-- Print the name of the new author 

PRINT 'New author "' + @newName + '" added.'

Update Trigger:

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.objects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER Person.reminder;
GO
CREATE TRIGGER reminder
ON Person.Address
AFTER UPDATE 
AS 
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
GO
-- Test the trigger.
UPDATE Person.Address
SET PostalCode = 99999
WHERE PostalCode = '12345';
GO

Get and/or compare today’s date:

convert(varchar,CURRENT_TIMESTAMP,101)
convert(varchar(12),getdate(),101)

Insert or Update with case statements:

USE [mydatabasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_update_manufacturedrobots_count]
	@mfgid int,
	@robotid int,
	@paintbotcount int=null,
	@drillbotcount int=null,
	@hammerbotcount int=null
AS
BEGIN

	SET NOCOUNT ON;

declare @id int
select @id = id from manufacturedrobots where mfgid=@mfgid and robotid=@robotid
if @id is not null
update manufacturedrobots set 
paintbotcount=case
when @paintbotcount is null then paintbotcount 
else @paintbotcount end, 
favorite=case
when @drillbotcount is null then drillbotcount 
else @drillbotcount end, 
checkedin=case
when @hammerbotcount is null then hammerbotcount 
else @hammerbotcount end
where id=@id
else
insert into manufacturedrobots (paintbotcount,drillbotcount,hammerbotcount) values  (@paintbotcount,@drillbotcount,@hammerbotcount)

END

Cursor:

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO

References:
MSDN, http://msdn.microsoft.com/en-us/library/ms187326.aspx
DevGuru, http://www.devguru.com/technologies/t-sql/7124.asp
DevArticleshttp://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/
MSDN, “How to debug stored procedures in Visual Studio .NET”, http://support.microsoft.com/kb/316549
CodeGuru, http://www.codeguru.com/forum/showthread.php?t=473102
DaniWeb, http://www.daniweb.com/forums/thread43719.html
SqlAuthority, http://blog.sqlauthority.com/2008/03/05/sql-server-simple-example-of-cursor-sample-cursor-part-2/