[sql] Why do we always prefer using parameters in SQL statements?

You are right, this is related to SQL injection, which is a vulnerability that allows a malicioius user to execute arbitrary statements against your database. This old time favorite XKCD comic illustrates the concept:

Her daughter is named Help I'm trapped in a driver's license factory.


In your example, if you just use:

var query = "SELECT empSalary from employee where salary = " + txtSalary.Text;
// and proceed to execute this query

You are open to SQL injection. For example, say someone enters txtSalary:

1; UPDATE employee SET salary = 9999999 WHERE empID = 10; --
1; DROP TABLE employee; --
// etc.

When you execute this query, it will perform a SELECT and an UPDATE or DROP, or whatever they wanted. The -- at the end simply comments out the rest of your query, which would be useful in the attack if you were concatenating anything after txtSalary.Text.


The correct way is to use parameterized queries, eg (C#):

SqlCommand query =  new SqlCommand("SELECT empSalary FROM employee 
                                    WHERE salary = @sal;");
query.Parameters.AddWithValue("@sal", txtSalary.Text);

With that, you can safely execute the query.

For reference on how to avoid SQL injection in several other languages, check bobby-tables.com, a website maintained by a SO user.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-injection

Preventing SQL injection in Node.js What are good ways to prevent SQL injection? Found 'OR 1=1/* sql injection in my newsletter database How can prepared statements protect from SQL injection attacks? Why do we always prefer using parameters in SQL statements? SQL injection that gets around mysql_real_escape_string() Java - escape string to prevent SQL injection How does a PreparedStatement avoid or prevent SQL injection? How does the SQL injection from the "Bobby Tables" XKCD comic work? Are PDO prepared statements sufficient to prevent SQL injection?