Parametric (Secured) MSSQL Query

MuhammadRehan Zafar

why we use parametric SQL query instead of simple one.

string CustomerName = "Anderson"

//Simple MSSQL Query

string qr = "SELECT CustomerCode FROM accounts WHERE CustomerName = '" + CustomerName + "' ";

// Parametric MSSQL Query

List param = new List().ToList();
sparam.Add(new SqlParameter() { ParameterName = "@AccountName", Value = CustomerName });

string qr = "SELECT CustomerCode FROM accounts WHERE CustomerName = @AccountName ";

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari4y ago
We use parameterized query to prevent from SQL injection attacks. A query can be manupulated when you apply user input directly query, therefore we use parameter to take user input and apply paramterwise.
https://www.w3schools.com/sql/sql_injection.asp
satheesh d4y ago
Using parametrized query helps prevent SQL Injection attacks. This can be done even by using stored procedures.

FOR EXAMPLE:
using (SqlConnection connection = new SqlConnection ("DBConnectionString"))
{
SqlConnection command = new SqlCommand("Select * from tblProduct where Product like '" + ProductText.Text + "%'", connection);
connection.Open();
ProductGridview.DataSource = command.ExecuteReader();
ProductGridview.DataBind();
}

If you search any item in the textbox, it will automatically filter and return the product. But there is a possible of entering bad query in the text box which will cause SQL injection. To prevent this use parametrized query or stored procedures
Satya Karki5y ago
To avoid SQL injection, we use parameterized queries.
Sachin Singh5y ago
we use parameterized queries to prevent SQL injection attacks.

let's see from your example

suppose, I am a malicious user (hacker), so instead of entering Customer Name, this is what I will enter

// I Enter :-  Sachin'+OR+1=1--

SELECT CustomerCode FROM accounts WHERE CustomerName = '" Sachin'+OR+1=1--
+ "' "

so, now you know what will happen, I will get all CustomerCode, so it will be a great loss to your company.

With parameterized query, the input goes as a parameter and doesn't concatenate with the query , and thus it prevents the SQL Injection Attack.
Srinivasan Ramamoorthi5y ago
Parameter objects avoid SQL injection when executing the query. If you are passing the qyery value as */ drop table. This will execute the query and drop the table, by using parameters the value you are entering will be saved as such in records.