SQL Server Exception Handling by TRY…CATCH

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 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();