Why do we always prefer using parameters in SQL statements

Question

I am very new to working with databases  Now I can write SELECT  UPDATE  DELETE  and INSERT commands  But I have seen many forums where we prefer to write   SELECT empSalary from employee where salary    salary      instead of   SELECT empSalary from employee where salary   txtSalary Text   Why do we always prefer to use parameters and how would I use them   I wanted to know the use and benefits of the first method  I have even heard of SQL injection but I don t fully understand it  I don t even know if SQL injection is related to my question

User · Answer

Other answers cover why parameters are important, but there is a downside! In .net, there are several methods for creating parameters (Add, AddWithValue), but they all require you to worry, needlessly, about the parameter name, and they all reduce the readability of the SQL in the code. Right when you're trying to meditate on the SQL, you need to hunt around above or below to see what value has been used in the parameter.

I humbly claim my little SqlBuilder class is the most elegant way to write parameterized queries. Your code will look like this...

C#

var bldr = new SqlBuilder( myCommand );
bldr.Append("SELECT * FROM CUSTOMERS WHERE ID = ").Value(myId);
//or
bldr.Append("SELECT * FROM CUSTOMERS WHERE NAME LIKE ").FuzzyValue(myName);
myCommand.CommandText = bldr.ToString();

Your code will be shorter and much more readable. You don't even need extra lines, and, when you're reading back, you don't need to hunt around for the value of parameters. The class you need is here...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class SqlBuilder
{
private StringBuilder _rq;
private SqlCommand _cmd;
private int _seq;
public SqlBuilder(SqlCommand cmd)
{
    _rq = new StringBuilder();
    _cmd = cmd;
    _seq = 0;
}
public SqlBuilder Append(String str)
{
    _rq.Append(str);
    return this;
}
public SqlBuilder Value(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append(paramName);
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public SqlBuilder FuzzyValue(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append("'%' + " + paramName + " + '%'");
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public override string ToString()
{
    return _rq.ToString();
}
}

User · Answer

Old post but wanted to ensure newcomers are aware of Stored procedures   My 10   worth here is that if you are able to write your SQL statement as a stored procedure  that in my view is the optimum approach   I ALWAYS use stored procs and never loop through records in my main code   For Example  SQL Table  gt  SQL Stored Procedures  gt  IIS Dot NET  gt  Class   When you use stored procedures  you can restrict the user to EXECUTE permission only  thus reducing security risks   Your stored procedure is inherently paramerised  and you can specify input and output parameters   The stored procedure  if it returns data via SELECT statement  can be accessed and read in the exact same way as you would a regular SELECT statement in your code     It also runs faster as it is compiled on the SQL Server   Did I also mention you can do multiple steps  e g  update a table  check values on another DB server  and then once finally finished  return data to the client  all on the same server  and no interaction with the client   So this is MUCH faster than coding this logic in your code

User · Answer

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       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

User · Answer

In addition to other answers need to add that parameters not only helps prevent sql injection but can improve performance of queries  Sql server caching parameterized query plans and reuse them on repeated queries execution  If you not parameterized your query then sql server would compile new plan on each query with some exclusion  execution if text of query would differ   More information about query plan caching

User · Answer

Using parameters helps prevent SQL Injection attacks when the database is used in conjunction with a program interface such as a desktop program or web site  In your example  a user can directly run SQL code on your database by crafting statements in txtSalary  For example  if they were to write 0 OR 1 1  the executed SQL would be  SELECT empSalary from employee where salary   0 or 1 1  whereby all empSalaries would be returned  Further  a user could perform far worse commands against your database  including deleting it If they wrote 0  Drop Table employee  SELECT empSalary from employee where salary   0  Drop Table employee  The table employee  would then be deleted   In your case  it looks like you re using  NET  Using parameters is as easy as      string sql    quot SELECT empSalary from employee where salary    salary quot        using  SqlConnection connection   new SqlConnection    connection info          using  SqlCommand command   new SqlCommand sql  connection                 var salaryParam   new SqlParameter  quot salary quot   SqlDbType Money           salaryParam Value   txtMoney Text               command Parameters Add salaryParam           var results   command ExecuteReader                Dim sql As String    quot SELECT empSalary from employee where salary    salary quot      Using connection As New SqlConnection  quot connectionString quot           Using command As New SqlCommand sql  connection              Dim salaryParam   New SqlParameter  quot salary quot   SqlDbType Money              salaryParam Value   txtMoney Text                  command Parameters Add salaryParam               Dim results   command ExecuteReader           End Using     End Using   Edit 2016-4-25  As per George Stocker s comment  I changed the sample code to not use AddWithValue  Also  it is generally recommended that you wrap IDisposables in using statements

User · Answer

Two years after my first go  I m recidivating     Why do we prefer parameters  SQL injection is obviously a big reason  but could it be that we re secretly longing to get back to SQL as a language  SQL in string literals is already a weird cultural practice  but at least you can copy and paste your request into management studio  SQL dynamically constructed with host language conditionals and control structures  when SQL has conditionals and control structures  is just level 0 barbarism  You have to run your app in debug  or with a trace  to see what SQL it generates   Don t stop with just parameters  Go all the way and use QueryFirst  disclaimer  which I wrote   Your SQL lives in a  sql file  You edit it in the fabulous TSQL editor window  with syntax validation and Intellisense for your tables and columns  You can assign test data in the special comments section and click  play  to run your query right there in the window  Creating a parameter is as easy as putting   myParam  in your SQL  Then  each time you save  QueryFirst generates the C  wrapper for your query  Your parameters pop up  strongly typed  as arguments to the Execute   methods  Your results are returned in an IEnumerable or List of strongly typed POCOs  the types generated from the actual schema returned by your query  If your query doesn t run  your app won t compile  If your db schema changes and your query runs but some columns disappear  the compile error points to the line in your code that tries to access the missing data  And there are numerous other advantages  Why would you want to access data any other way

User · Answer

In Sql when any word contain   sign it means it is variable and we use this variable to set value in it and use it on number area on the same sql script because it is only restricted on the single script while you can declare lot of variables of same type and name on many script  We use this variable in stored procedure lot because stored procedure are pre-compiled queries and we can pass values in these variable from script  desktop and websites for further information read Declare Local Variable  Sql Stored Procedure and sql injections   Also read Protect from sql injection it will guide how you can protect your database   Hope it help you to understand also any question comment me

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

Examples related to sql

Examples related to sql-server

Examples related to sql-injection