SQL Injection Attacks

---
--- Explore SQL Injection attacks
--- 

use ReportBuilderTraining

---
--- @StudentID is user input provided to a program from a user interface
---
--- @cmd is built from that string and executed
---

declare @StudentId varchar(50)
declare @cmd nvarchar(4000)

set @StudentId = '1'
set @cmd = 'select * from Student where StudentId=' + @StudentId
print @cmd
exec(@cmd)
go


---
--- But users can be clever and devious
---

declare @StudentId varchar(50)
declare @cmd nvarchar(4000)

set @StudentId = '1; select * from StudentHistory;'
set @cmd = 'select * from Student where StudentId=' + @StudentId
print @cmd
exec(@cmd)
go



---
--- Solution, parametarize queries
---

declare @StudentId varchar(50)
declare @cmd nvarchar(4000)

set @StudentId = '1; select * from StudentHistory;'
set @cmd = 'select * from Student where StudentId=@id'
print @cmd

exec sp_executesql @cmd, N'@id varchar(40)', @id=@studentId

go


declare @StudentId varchar(50)
declare @cmd nvarchar(4000)

set @StudentId = '1; select * from StudentHistory;'
set @cmd = 'select * from Student where LastName=@id'
print @cmd

exec sp_executesql @cmd, N'@id varchar(40)', @id=@studentId

go