What is SQL Injection?

SQL Injection is one of the many web attack mechanisms used by hackers to steal data from organizations. It is perhaps one of the most common application layer attack techniques used today. It is the type of attack that takes advantage of improper coding of your web applications that allows hacker to inject SQL commands into say a login form to allow them to gain access to the data held within your database.

SQL Injection is the hacking technique which attempts to pass SQL commands (statements) through a web application for execution by the backend database. If not sanitized properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.

What is the impact of SQL Injection?        
 
Once an attacker realizes that a system is vulnerable to SQL Injection, he is able to inject SQL Query / Commands through an input form field. This is equivalent to handing the attacker your database and allowing him to execute any SQL command including DROP TABLE to the database.

An attacker may execute arbitrary SQL statements on the vulnerable system. This may compromise the integrity of your database and/or expose sensitive information. Depending on the back-end database in use, SQL injection vulnerabilities lead to varying levels of data/system access for the attacker. It may be possible to manipulate existing queries, to UNION (used to select related information from two tables) arbitrary data, use sub selects, or append additional queries.

In some cases, it may be possible to read in or write out to files, or to execute shell commands on the underlying operating system. Certain SQL Servers such as Microsoft SQL Server contain stored and extended procedures (database server functions). If an attacker can obtain access to these procedures, it could spell disaster.

Unfortunately the impact of SQL Injection is only uncovered when the theft is discovered. Data is being unwittingly stolen through various hack attacks all the time. The more expert of hackers rarely get caught.

1. Malicious SQL statements:

   a. For numeric Field :-SQL Injection Based on 1=1 is Always Return True.

   b. For  String   Field:-   SQL Injection Based on ""="" is Always Return  True .

 

  Note: The Hacker used these above  malicious sql statement to fetch the record without knowing there

Password and Id.

Let See The Work Demonstration: 

 

 Is a Table Structure(Employee)                 

                                               

Emp_Id	Emp_Name	Emp_Password	Emp_Salary
1001	RahulGanga	HA123	10,000
1002	Sathya	BA321	20,000
1003	Akhil	SA132	30,000
1004	Bubi	FA213	40,000

Our Targets is:

1.Fetch the Employee Details Without Knowing Their Password and even their name Or ID.

2.Modify the Record of Employee Without Knowing Their Password.

3. Delete the Record of Emplyee Without Knowing Their Password.

Example1.(InSQL)

1.     Fetch  the Employee Details Without Knowing Their Password and even ther name Or ID.

Sol: select * from employee where Emp_Id=1 or 1=1 and Emp_Pwd='Dhoom3' or''=''

Out Put:

 

 

2.     Modify the Record of Employee Without Knowing Their Password.

 First see actual result of table "empTb"

 

Sol: update empTb set Emp_Name='Titanic',Emp_Pwd='0' where Emp_Id=1 or 1=1

     and  Emp_Pwd=123 or 1=1

Out Put:

3. Delete the Record of Emplyee Without Knowing Their Password.

 

Sol:delete from employee where Emp_Id=1 or 1=1

Out Put:

How do I prevent SQL Injection attacks?

 

This Problem is solved by parameterized sql, SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.And  it Does not take any extra values because it take the value by Add parameter .

code:

_con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        _cmd = new SqlCommand("select * from  SqlInjectionTb where id=@0 and pwd=@1", _con);

        _cmd.Parameters.AddWithValue("@0",Convert.ToInt32(TxtId.Text));

        _cmd.Parameters.AddWithValue("@1", TxtPwd.Text);

  

        _con.Open();

        _cmd.ExecuteNonQuery();

        DataTable _dt = new DataTable();

        SqlDataReader _dr = _cmd.ExecuteReader();

        _dt.Load(_dr);

        GridView1.DataSource = _dt;

        GridView1.DataBind();

Note:

It Not Accept any Extra Values by Hackers.

IT Fire Error…

After execution of this above code it absolutely fire the exception as use see below :

Difference between RANK, DENSE_RANK and ROW_NUMBER in SQL

Previously We discussed that How to Use  Dense_Rank() function to find Nth highest salary in sql server.

Now we are going to discuss What is difference between 

             Dense_Rank()     &      Rank()     &     Row_Number()

As I told you that Dense_Rank() create  one temporary  run time column which numbers the Salary according to the Rank priority like (1,2,3.....).

Rank() and  Row_Number() do the same thing but in different Ways. How?

Lets discuss- 

1-  Rank()-  It generate Temporary column at rum time, and do the numbering for every distinct row ,but it contains gaping in sequencing number.

Lets understand with query  and Example :

select Employee_Name,Salary, Rank() over(order by Salary desc) from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) that’s why query give sequence (1) in Column RK because it gives different sequence number to distinct  Record.

John have Second highest salary, but it have Sequence 3 instead of 2 in Column RK because there are two members (Carry and Paul) having same salary.

There could be possible that 3 Members have highest salary (10000) then first three members will get sequence (1) and John will get 4 in RK column.

That’s why we mentioned above that function Rank() contains gaping in sequencing number.


2-Dense_Rank()-It generate Temporary column at rum time, and do the numbering for every distinct row ,but it doesn't contain gaping in sequencing number.

Lets understand with query  and Example :

select Employee_Name,Salary, Dense_Rank() over(order by Salary desc) as RK from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) that’s why query  give sequence(1) in Column RK because it gives different sequence number to distinct  Record.

John have Second highest salary , it have Sequence 2  there is no gaping in Sequencing number

That’s why we mentioned above  that function  Dense_Rank() doesn't contain gaping in sequencing number.


3- Row_Number() – It also generate Temporary column at rum time, but the main thing is that it do numbering for every individual row without any gaping in sequencing number.

Let’s understand with query and Example:

select Employee_Name,Salary, Row_Number() over(order by Salary desc) as RK from Employee_Master

Explanation – Here we can see that Carry and Paul have same salary (10000) but they have different Sequence(1,2) .

That’s why we mentioned above  that  function  Row_Number() do numbering for every individual row without any gaping in sequencing number.

Difference in a Glance – 

 

Author-Er. Rahul Kr. Yadav

Remove duplicate records from a table in SQL Server

Many developers face this problem, that they have column with duplicate data, and want to delete duplicate records from SQL table , but one should never delete duplicate record without observing that ,that what  is going to delete ,and prefer to have latest record. So I am explain some steps to delete duplicate records from table and assuming that and table has unique incremental id.

1.Table  with duplicate record:-

insert into student values('yashodhara')

insert into student values('New')

insert into student values('New')

insert into student values('Old')

insert into student values('Old')

insert into student values('Avarage')

insert into student values('Keep')

insert into student values('Keep')

insert into student values('After')

2. check data

select * from student

Output is:

3.To find which record is duplicate and how many times it is repeated

 Select name,COUNT(*)as duplicate from student group by name having count(*)>1 order by count(*) desc

Out put is 

4. Delate duplicate records and keep lates record of every dulicate data

delete from student where rollno not in(

select max(rollno) from student group by name

)

5 Check final data

select * from student

                                                 Author: Er. Yashodhara Sharma

Difference between Stored Procedure and Function in SQL

In many Interviews this question is very frequently asked by the interviewer, that what difference between Stored Procedures and Functions .So I am explaining some differences

1.         Stored Procedures in database are in compiled Format but Functions compiled at run time.

2.         We can use DML Statements (insert, update and delete database table) in stored procedure ,but in functions we cannot use DML Statements

3.         Function can be used with in stored procedures, but stored procedures cannot be used with in Functions.

4.         Functions can execute with select statements, but stored procedures cant not used with select statements, for executing procedure exec/execute key word we have to put before Procedure name.

5.         Functions can used as the column in select Statements, Stored procedure cannot.

6.         Functions can used with WHERE and HAVING ,Stored procedure Cannot be used with Where and having 

7.         In Functions only input parameter we can use, In stored procedure both input and output parameter can use.

8.         Functions must return a value, but in case of stored procedure they may or not return value.

9.         Functions can use with join because table valued function return table with can use in join ,stored procedure can not used with a  join

10.       In Function Table variable can be used temporary table cannot used with functions, In stored procedure table variable and temporary table both can used.

11.       In function exception handling cannot be done (we cannot use try and catch with in the functions),Exception handling  can be done with in stored procedure 

Examples

1.         In Functions only input parameter we can use, In stored procedure both input and output parameter can use.

                         Both input and output parameter can used with stored procedure.

                        We can not use output parameter in function. 

2.         DML  Statements in Stored procedures and Function

                            

                          we can use DML statements in stored procedure.

 

DML statement through error when using in function.

3.         Functions can used as the column in select Statements, Stored procedure cannot.

                           Function can use with select statement.

 

Stored procedure can not use with select statement.

4.         In function exception handling cannot be done (we cannot use try and catch with in the functions),Exception handling  can be done with in stored procedure 

                          In Stored Procedure we can use try catch.

 

In Function try catch not working.

Autor: Er. Yashodhara Sharma

Find Nth highest Salary without Using TOP and Max Keyword

For this we use Dense_Rank() Function

Now we will discuss how it works , and we will generate query for desired output step by step

There is a table Employee_Master which contain employee names and salary.

Now we will apply select query  and short it out in Descending order,This query will arrange the records in Descending order on the behalf of Salary.

select* from Employee_Master order by Salary desc

Now we are going to apply Dense_Rank() function, before applying this we need to understand that how it works ??

When we apply Dense_Rank() function it will create  one temporary  run time column which numbers the Salary according to the Rank priority like (1,2,3.....)

select Emp_Name,Salary,DENSE_RANK() over(order by Salary Desc)as RK from  Employee_Master

Now we can easily find the Nth highest salary from last query .

We will treat above last query as SUBQUERY and find out the Nth highest  salary with the help of Where Clause which will apply on Temporary Column(RK) like that-

If We want to find 3rd highest Salary-

select* from (select Emp_Name,Salary,DENSE_RANK() over(order by Salary Desc)as RK from  Employee_Master)as Employee_Master where RK=3

                                         
                                                      Author-Er. Rahul Kr. Yadav

Windows 10 is free for 6x more powerful Raspberry Pi 2

The successor to the world's favourite tiny computer, Raspberry Pi, has defied Moore's Law which predicts the amount of transistors on a processor chip will double every couple of years. In fact, the Pi v2 has six times the processing performance of its predecessor thanks to a new quad-core ARMv7 processor running at 900 MHz

How to get nth salary in sql

We are going to discuss very important Topic which usually asks in the Interviews.

We will create the required query step by step-

select * from Employee_Master

Firstly we need to specify what will be the output, for this we need to apply Desc order in select query, this query arrange all the records of  table in Descending Order on the Behalf of Salary.

select * from  Employee_Master order by Salary desc

Since We Need to find out Nth Highest salary(3rd),We will apply TOP keyword in last query.

select top(3) * from Employee_Master order by Salary desc



With the help of this output we can identify that what Should be the 3rd Highest salary .It will be 6000 which belongs to Employee Sagar, Because we have already selected Top 3 after applying Descending Order in select query.  

Now we need to  Generate query  for desired output.


We will use last query as Sub-Query and apply SELECT query on this subquery after that apply ASC order  and apply  TOP(1) on this query , it will generate desired output like that.

Select Top(1)* from (select top(3) * from Employee_Master order by Salary desc) as Employee_Master order by Salary asc

                                                   Author -Er. Rahul Kr. Yadav