Categories
sql sql-injection sql-server

Why do we always prefer using parameters in SQL statements?

122

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.

0

    142

    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 = "SELECT empSalary from employee where salary = @salary";
    
    using (SqlConnection connection = new SqlConnection(/* connection info */))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        var salaryParam = new SqlParameter("salary", SqlDbType.Money);
        salaryParam.Value = txtMoney.Text;
    
        command.Parameters.Add(salaryParam);
        var results = command.ExecuteReader();
    }
    
    Dim sql As String = "SELECT empSalary from employee where salary = @salary"
    Using connection As New SqlConnection("connectionString")
        Using command As New SqlCommand(sql, connection)
            Dim salaryParam = New SqlParameter("salary", 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.

    12

    • great solution. But can you explain a bit more, why and how using parameters is safe. I mean it still looks like the sql command will be same

      – Sandy

      Sep 21, 2011 at 20:49

    • 2

      SQL Server treats the text inside the parameters as input only and will never execute it.

      – Chad Levy

      Sep 21, 2011 at 20:57

    • 3

      Yes, you can add multiple parameters: Insert Into table (Col1, Col2) Values (@Col1, @Col2). In your code you’d add multiple AddWithValues.

      – Chad Levy

      Sep 21, 2011 at 20:57


    • 1

      Please don’t use AddWithValue! It can cause implicit conversion issues. Always set the size explicitly and add the parameter value with parameter.Value = someValue.

      Mar 11, 2015 at 15:41

    • 2

      You should really use salaryParam.Value = CDec(txtMoney.Text): SQL Server money is Decimal in .NET: SQL Server Data Type Mappings. And the parameter name needs the “@”, as in “@salary”.

      Mar 16, 2017 at 19:30

    85

    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.

    4

    • 1

      great solution. But can you explain a bit more, why and how using parameters is safe. I mean it still looks like the sql command will be same.

      – Sandy

      Sep 21, 2011 at 20:48

    • 1

      @user815600: a common misconception – you still believe that the query with parameters will take in the value and substitute the parameters for the actual values – right? No this is not happening! – instead, the SQL statement with parameters will be transmitted to SQL Server, along with a list of parameters and their values – the SQL statement is not going to be the same

      – marc_s

      Sep 21, 2011 at 20:57

    • 1

      that means sql injection is being monitored by sql server internal mechanism or security. thanks.

      – Sandy

      Sep 21, 2011 at 21:01

    • 6

      Much as I like cartoons, if you’re running your code with sufficient privilege to drop tables, you probably have wider issues.

      – philw

      Aug 3, 2013 at 16:34

    11

    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

    1

    • 2

      This is more pertinent than one might think. Even a “small” query can be executed thousands or millions of times, effectively flushing the entire query cache.

      – James

      Jan 12, 2017 at 17:10