SQL Server Exception Handling

SQL Server Exception Handling by TRY…CATCH

Like  C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block we can have only one CATCH block.

TRY..CATCH Syntax

BEGIN TRY

--SQL statements

--or SQL statement blocks

END TRY

BEGIN CATCH

--SQL statements

--or SQL statement blocks

END CATCH

Error Functions used within CATCH block

1.    ERROR_NUMBER()

This returns the error number and its value is same as for @@ERROR function.

2.    ERROR_LINE()

This returns the line number of T-SQL statement that caused error.

3.    ERROR_SEVERITY()

This returns the severity level of the error.

4.    ERROR_STATE()

This returns the state number of the error.

5.    ERROR_PROCEDURE()

This returns the name of the stored procedure or trigger where the error occurred.

6.    ERROR_MESSAGE()

This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception handling example

BEGIN TRY

DECLARE @num INT, @msg varchar(200)

---- Divide by zero to generate Error

SET @num = 5/0

PRINT 'This will not execute'

END TRY

BEGIN CATCH

PRINT 'Error occured that is'

set @msg=(SELECT ERROR_MESSAGE())

print @msg;

END CATCH

GO

 Note

1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.

2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.

3. Each TRY block is associated with only one CATCH block and vice versa

4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.

5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.

6. XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.

SQL Bulk insert using xml

If we need to insert the data into a table from xml file then we have below options

     

    1)    By for loop in our business logic /code behind: this approach will time consuming in case of we have a big xml file.

    2)    By using Store procedure to insert bulk record into Database 

Assume we have this xml file

<?xml version="1.0" encoding="utf-8"?>
<Employees>
  <Employee >
    <Eid>11</Eid>
    <Name>John Smith</Name>
    <Designation>Team Leader</Designation>
    <Salary>50000</Salary>
    <DeptNo>1</DeptNo>
  </Employee>
  <Employee >
    <Eid>12</Eid>
    <Name>Sumit</Name>
    <Designation>Admin</Designation>
    <Salary>1000</Salary>
    <DeptNo>2</DeptNo>
  </Employee>
  <Employee >
    <Eid>13</Eid>
    <Name>John</Name>
    <Designation>Sr. Software Devoloper</Designation>
    <Salary>2000</Salary>
    <DeptNo>2</DeptNo>
  </Employee>
 </Employees>

Then the Store procedure to insert bulk record into Database will be

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SpEmpDetailsIns]
(@xmlstr ntext)
as
begin
  declare @hDoc int
  exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
  insert into tbl_xml_Emp
    select xml.Eid,xml.name,xml.Designation,xml.Salary,xml.Deptno
    from OPENXML(@hDoc,'/Employees/Employee',2)
    with(Eid int,
         name varchar(50) 'Name',
         Designation varchar(50) ,
         Salary money,
         Deptno int 'DeptNo')xml
exec sp_xml_removedocument @hDoc
 
end 
 

1.    Format your XML to look like

<Employee >
<Eid>1001</Eid>
<Name>BBB</Name>
<Designation>Software Devoloper</Designation>
<Salary>30000</Salary>
<DeptNo>20</DeptNo>
</Employee>

did you notice how <Employee> element have the column as children and each column value is a node text not an attribute.

Then you will need to use "2" in the OPENXML function call

from OPENXML(@hDoc,'/Employees/Employee',2)
 

2.    <Employee Eid="1001" Name="BBB" Designation="Software Devoloper" Salary="30000" DeptNo="20"> </Employee>

No change required for the OPENXML function call it remains same as before

Injection in SQL SERVER

SQL injection is a technique where hacker can inject SQL commands into an SQL statement, via web page.Injected SQL commands can compromise the security of a website.

SQL Injection Based on 0=0 is Always True

Let's say that the original purpose of the code was to create an SQL statement to select a user with a given Emp_id.

Emp_id: 112 or 0=0

SELECT * FROM Emp_Details WHERE Emp_id = 112 or 0=0

The SQL above is valid. It will return all rows from the table, as WHERE 0=0 is always true.

 

SQL Injection Based on ""="" is Always True

Here is a public construction, used to verify user login on a web page:

User Name:

Password:

The Final Statement will be

UserName = request.form("UserName");
Pass = request.form ("UserPass");

sql=" SELECT * FROM Emp_Details WHERE UserName ='" + UserName + "' AND PassWord ='" + Pass + "'"

Hackers might get access to user names and passwords in a database by simply inserting “or ""=" into the user name or password text box.

The code at the server will create a valid SQL statement like this:

SELECT * FROM Emp_Details WHERE UserName ="" or ""="" AND PassWord ="" or ""=""

The result SQL is valid. It will return all rows from the table Users,since  WHERE ""="" is always true.

SQL Injection Based on Batched SQL Statements 

Most databases support batched SQL statement, separated by semicolon.

e.g.

SELECT * FROM Emp_Details ; DROP TABLE EmpLoginDetails

The SQL above will return all rows in the Emp_Details table, and then delete EmpLoginDetails table.

If we had the following server code:

UserName = request.form("UserId");
txtSQL = " SELECT * FROM Emp_Details WHERE UserName = " + UserName;

And the following input:

User Name: 105; DROP TABLE EmpLoginDetails

The code at the server would create a valid SQL statement like this:

SELECT * FROM Emp_Details WHERE UserName = 105; DROP TABLE EmpLoginDetails

Parameters for Protection

The only way to protect a web page from SQL injection, is to use SQL parameters. SQL parameters are values that are added to an SQL query at execution time, in a controlled manner. The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.

UserName = request.form("UserId");
sql = " SELECT * FROM Emp_Details WHERE  UserName = @a";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@a", UserName);
command.ExecuteReader();

SQL Date Functions

SQL Date Functions

Important built-in date functions in MySQL:

• NOW() : Returns the current date and time
• CURDATE() : Returns the current date
• CURTIME() : Returns the current time
• DATE() : Extracts the date part of a date or date/time expression
• EXTRACT() : Returns a single part of a date/time
• DATE_ADD() : Adds a specified time interval to a date
• DATE_SUB() : Subtracts a specified time interval from a date
• DATEDIFF() : Returns the number of days between two dates
• DATE_FORMAT() : Displays date/time data in different formats

Built-in date functions in SQL Server:

•    GETDATE() : Returns the current date and time
•    DATEPART() : Returns a single part of a date/time
•    DATEADD() : Adds or subtracts a specified time interval from a date
•    DATEDIFF() : Returns the time between two dates
•    CONVERT() : Displays date/time data in different formats

Data types for storing a date or a date/time value in the database:

•    DATE: format YYYY-MM-DD
•    DATETIME: format: YYYY-MM-DD HH:MI:SS
•    TIMESTAMP: format: YYYY-MM-DD HH:MI:SS
•    YEAR: format YYYY or YY

Data types for storing a date or a date/time value in the database:

•     DATE: format YYYY-MM-DD
•     DATETIME: format: YYYY-MM-DD HH:MI:SS
•     SMALLDATETIME: format: YYYY-MM-DD HH:MI:SS
•     TIMESTAMP: format: a unique number

SQL Transaction

SQL Transaction

Transactions are units of work accomplished in a logical order, whether in a manually by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database.

Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions:

Transactions have four standard properties, usually referred to by the acronym ACID:

Atomicity: certifies that all operations within the work unit are completed successfully; otherwise, the transaction is terminated at the point of failure, and previous operations are rolled back to their former state.

Consistency: ensures that the database properly changes states upon a successfully committed transaction.

Isolation: enables transactions to operate independently of and transparent to each other.

Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

There are following commands used to control transactions:

COMMIT: to save the changes.

ROLLBACK: to rollback the changes.

SAVEPOINT: creates points within groups of transactions in which to ROLLBACK

SET TRANSACTION: Places a name on a transaction.

Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

The COMMIT Command:

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

Syntax:

COMMIT;

The ROLLBACK Command:

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Syntax:

ROLLBACK;

The SAVEPOINT Command:

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

This command serves only in the creation of a SAVEPOINT among transactional statements. The ROLLBACK command is used to undo a group of transactions.

Syntax:

ROLLBACK TO SAVEPOINT_NAME;

The RELEASE SAVEPOINT Command:

The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.

Syntax:

RELEASE SAVEPOINT SAVEPOINT_NAME;

Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the SAVEPOINT.

The SET TRANSACTION Command:

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows.

For example, you can specify a transaction to be read only, or read write.

Syntax:

 SET TRANSACTION [ READ WRITE | READ ONLY ];