Introduction
SQL injection is an idea that malicious users can inject SQL commands into SQL Query from the input control of the page. SQL injection is the direct code insertion into the input variables used with SQL queries. SQL injection allows attackers with unauthorized access to delete/change sensitive data, modify SQL server settings, etc.
To run malicious SQL queries on a database server, the attacker first finds the input control that value is part of the SQL query. At this point, they introduce malicious SQL queries to break the application's existing functionality.
Example
Suppose I have an Employee Table, and it has some dummy data. To retrieve a particular employee record, I have written a stored procedure, which except for name and based on username, will return employee data.
Table Definition and dummy data creation script
CREATE TABLE[dbo].[Employee](
[Id][int]IDENTITY(1, 1) NOT NULL,
[Name][varchar](50) NOT NULL,
[Salary][money]NULL,
[EmailAddress][varchar](255) NULL,
[PhoneNumber][varchar](50) NULL,
[Address][varchar](max) NULL,
CONSTRAINT[PK_Employee_1]PRIMARY KEY CLUSTERED ( [Id]ASC ) WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON[PRIMARY]
) ON[PRIMARY]TEXTIMAGE_ON[PRIMARY] INSERT[dbo].[Employee](
[Name], [Salary], [EmailAddress],
[PhoneNumber], [Address]
) VALUES (
N 'Jignesh', 10000.0000, N '[email protected]',
N '123', N 'test'
) GO INSERT[dbo].[Employee](
Name], [Salary], [EmailAddress],
[PhoneNumber], [Address]
) VALUES (
N 'Tejas', 10000.0000, N '[email protected]',
N '123', N 'test'
) GO INSERT[dbo].[Employee](
[Name], [Salary], [EmailAddress],
[PhoneNumber], [Address]
) VALUES (
N 'Rakesh', 10000.0000, N '[email protected]',
N '123', N 'test'
)
Stored procedure with dynamic query
CREATE PROCEDURE GetEmployeeDetails(
@Name VARCHAR(50)
) AS BEGIN DECLARE @sqlcmd NVARCHAR(MAX);
SET @sqlcmd = N 'SELECT * FROM Employee WHERE Name = ''' + @Name + '''';
EXECUTE(@sqlcmd) END --Execute the above stored procedure
DECLARE @name VARCHAR(50) = ‘Jignesh’ EXEC GetEmployeeDetails @name
Ohh, great! This worked as expected.
![work]()
Now, I have to change the input value, and it breaks our existing functionality. Now my query returns all the rows of the employee table. This is called an SQL injection attack.
![injection]()
Solution
The solution is to use parameterized query and sp_executesql stored procedure to execute dynamic SQL. So altered procedure looks like this,
ALTER PROCEDURE GetEmployeeDetails(
@Name VARCHAR(50)
) AS BEGIN DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @sqlcmd = N 'SELECT * FROM Employee WHERE Name = @Name';
SET @params = N '@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd,
@params,
@Name;
END
![table]()
We can still use parameters if we use dynamic SQL with C# code.
SqlConnection conn = new SqlConnection("connection string");
SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM Employee WHERE Name = @Name", conn);
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50);
parm.Value = "Jignesh";
Summary
Injected code is the syntactically correct SQL query. As a prevention action, we can also validate our input. Some of the Prevention actions are mentioned below,
- Do not build T-SQL statements directly from user input.
- It doesn't concat the user input string to the input used in validation because concatenation is the main entry point of script injection.
- Inspect input variables and accept only expected values. Do not accept value, which contains escape sequences, and comment characters.
- Do not accept characters like semicolons (;), single quote ('), comment line delimiter of SQL (--)
- When working with XML documents, validate all data against the schema.
Read more articles on SQL