Parametric (Secured) MSSQL Query

Question

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<SqlParameter> param = new List<SqlParameter>().ToList();
sparam.Add(new SqlParameter() { ParameterName = "@AccountName", Value = CustomerName });

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

Sachin Singh · Answer

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.

Satya Karki · Answer

To avoid SQL injection, we use parameterized queries.

Rijwan Ansari · Answer

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

Srinivasan Ramamoorthi · Answer

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.

satheesh d · Answer

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