SQL Injection-An Introduction

SQL Injection is a technique to potentially impact the system of web world. It is used to take advantage of available vulnerable points of the system. It is just like inserting a SQL statement to be run on our database without information and confirmation to take full advantage of weak point. For example, in a registration or login page of a web system, instead of entering required information we used to enter SQL statement and force to run on our database to get their information from the database.

For example let's take a SQL statement

Select * from employee where first_name='"+first_name+"';

Here user is asked to enter first name and if user enter first_name as pawan then SQL statement would be similar to Select * from employee where first_name='pawan';

But if user enter first_name as pawan;drop table employee-- then SQL statement would be similar to  

Select * from employee where first_name='pawan';drop table employee--

Here the semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. Here the modified code is syntactically correct and hence it will be executed by the server. But when SQL Server processes this statement, SQL Server will first select all records in employee where first_name is pawan. Then, SQL Server will drop table employee.

Difference between JOIN and UNION

JOIN
JOIN is used to display data from more than two tables.

SELECT FIRST_NAME ,LAST_NAME,JOB_ID FROM TABLE1 T1 JOIN TABLE2 T2 ON
(T1.JOB_ID=T2.JOB_ID);
NOTES: To join n tables together, we need a minimum of n-1 join conditions.

UNION
UNION is used to display combined data from two tables into a single result set.
SELECT FIRST_NAME ,LAST_NAME,JOB_ID FROM TABLE1
UNION
SELECT FIRST_NAME ,LAST_NAME,JOB_ID FROM TABLE2
NOTES: The number of columns and order must match in all tables.

Difference between ORDER BY and GROUP BY

ORDER BY

ORDER BY is used to sort the rows.It affects only the order of result set.
SELECT FIRST_NAME,LAST_NAME,JOB_ID,DEPARTMENT_ID FROM EMPLOYEE
ORDER BY JOB_ID
NOTE:If we are using ORDER BY clause,it must be last clause of the SQL statement.

GROUP BY
GROUP BY is used to divide the rows into group in a table. GROUP BY specifies how the rows should be grouped.
SELECT DEP_ID,AVG(SAL) FROM EMPLOYEE
GROUP BY DEP_ID
NOTE:Rows are sorted by ascending order of the columns included in the GROUP BY clause by default.

Difference between BETWEEN and IN

BETWEEN
BETWEEN is used to display rows based on a range of values.
SELECT FIRST_NAME, SALARY FROM EMPLOYEE
WHERE SALARY BETWEEN 4000 and 10000;
NOTE: Values specified with the BETWEEN condition are inclusive. We have to specify the lower limit first.

IN
IN is used to test for values in a list
SELECT FIRST_NAME, SALARY, EMP_ID FROM EMPLOYEE
WHERE EMP_ID IN (150,250,300);
NOTE: THE IN condition can be used with any data type.

Difference between DELETE,DROP and TRUNCATE

DELETE

DELETE is used to remove exiting rows from a table.
DELETE FROM TableName
WHERE condition;
If we omit the WHERE clause all rows in the table are deleted otherwise specific rows are deleted based on the condition. After performing a DELETE operation we need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
NOTE: All DELETE triggers on the table will fire.

DROP
DROP is used to delete the definition of oracle table.When we drop a table,the database loses all the data in the table and all the indexes associates with it.
DROP TABLE TableName
After dropping table any views and synonmys are still remained there but are invalid.

TRUNCATE
TRUNCATE is used to remove all rows from a table and release the storage space used by the table.
TRUNCATE TABLE TableName
NOTE:DROP and TRUNCATE are DDL but DELETE is DML.So when we use TRUNCATE we cannot roll back row removed and even no triggers will be fired.