SQL - TRANSACTION, INJECTION & EXPRESSIONS

Transactions:

·         A transaction is a unit of work that is performed against a database.

·         A transaction is the propagation of one or more changes to the database.

 For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Properties of Transactions:

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

·      Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted 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 can not be used while creating tables or dropping them because these operations are automatically commited in the database.

The COMMIT Command:

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

COMMIT;

The ROLLBACK Command:

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

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.

The syntax for SAVEPOINT command is as follows:

SAVEPOINT SAVEPOINT_NAME;

Injection:

·         If you take user input through a webpage and insert it into a SQL database, there's a chance that you have left yourself wide open for a security issue known as SQL Injection.

·         Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQL statement that you will unknowingly run on your database.

·         Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching.

Expressions:

An expression is a combination of one or more values, operators, and 

SQL functions that evaluate to a value.

Syntax:

SELECT column1, column2, columnN

FROM table_name

WHERE [CONDITION|EXPRESSION];

Boolean Expressions:

SQL Boolean Expressions fetch the data on the basis of matching single value.

SELECT column1, column2, columnN

FROM table_name

WHERE SINGLE VALUE MATCHING EXPRESSION;

SELECT * FROM CUSTOMERS WHERE SALARY = 10000;

Numeric Expression:

This expression is used to perform any mathematical operation in any query.

SELECT numerical_expression as  OPERATION_NAME

[FROM table_name

WHERE CONDITION] ;

Here numerical_expression is used for mathematical expression or any formula.

SELECT (15 + 6) AS ADDITION

+----------+

| ADDITION |

+----------+

|       21 |

+----------+

SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;

+---------+

| RECORDS |

+---------+

|       7 |

+---------+

Date Expressions:

Date Expressions return current system date and time values:

SELECT CURRENT_TIMESTAMP;

+---------------------+

| Current_Timestamp   |

+---------------------+

| 2017-03-16 11:40:23 |

+---------------------+

SELECT  GETDATE();

+-------------------------+

| GETDATE                 |

+-------------------------+

| 2017-03-16 11:43:23 |

+-------------------------+