This article is all about Sql Parameterized query
What is Sql Parameters?
Sql parameters are like program method parameters. We pass param to get filtered result.
Example:
“select * from teacher where name=”+ @name Here @name is parameter. This sql query will return data from teacher table where name will match to the specified value.
Why we need this?
There are two advantages
- Prevent sql injection
- Speedup sql query execution in some scenarios where query doesn’t change but value changes
What is Sql Injection?
It is a technique through which user passes malicious code to database to manipulate database. This malicious code could update database table or drop database.
Example of sql query:
string query = “select name from teacher where name=” + Request.QueryString(“name”);
where name supplied by user as input is “name = ‘rajesh’; drop % 20table % 20teacher”
This code will execute for name=’rajesh’ and will also drop teacher table. Which is unexpected behavior. This is impact of sql injection.
We already know using parameterized query we can avoid Sql Injection.
What is Parameterized Query?
Any input passed in sql query should be supplied as parameter not as query.
Example:
string name = “raj”;
string sql = “SELECT * from teacher where name = @name”;
using (SqlConnection connection = new SqlConnection(/* connection details */))
using (SqlCommand command = new SqlCommand(sql, connection))
{
var salaryParam = new SqlParameter(“name”, SqlDbType.VarChar);
salaryParam.Value = name;
command.Parameters.Add(salaryParam);
var results = command.ExecuteReader();
}
Here we are passing name as sql parameter. This query will get all records matching to name equals to ‘raj’.
How this parameter is saving us from Sql Injection?
First, Parameter is converted to expected data type. If integer is expected, then we cannot send varchar.
Second, when parameter is passed through Sql command then it is not substituted directly into the query. There is a system stored procedure sp_executesql which is called to parse the input parameters. If parameter is not correct, then query is not executed, and error is thrown.
Visit this good read on SQL Injection Prevention.
About Author
Rajesh Yadav
Rajesh Yadav is passionate ASP.NET developer and hold expertise over web technologies like ASP.NET 2.0/3.5/4.0, LINQ, Java Script, Angular JS, MVC, Sharepoint, Soap and Rest Services, IIS and emerging technologies like Cloud. Learning new technologies and sharing excite him the most. Currently Rajesh is exploring Azure and AWS.
For more Articles and updates keep visiting blog section of www.iadaptertechnology.com and Phototor