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 ];

SQL Aggregate/ Scalar Functions

SQL Aggregate/ Scalar Functions

SQL built-in functions Used in calculations on data.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

SQL aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

---

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

SQL scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

Joins In SQL

The SQL Joins is used to combine records from two or more tables in a database. A JOIN is a means for joining fields from two tables by using values common to each. It is visible that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

SQL Join Types:

There are different types of joins available in SQL:

·        INNER JOIN: returns rows when there is a match in both tables.

·        LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

·        RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

·        FULL JOIN: returns rows when there is a match in one of the tables.

·   SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

·        CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.