What is passing parameters to SQL and why do I need it?

Question

Beginner here:

In this answer to my question of how to insert data into SQL Server he mentioned passing parameters instead of string concatenation like I currently have.

Is this really necessary for security? If so, what exactly is passing parameters? When i google it I get a lot about stored procedures. Is that what I want, I do not know about stored procedures....yet.

If you can point me in the right direction, I would appreciate that.

Thanks.

EDIT:

Ok, here is what I got. It seems to update the database correctly and eventually I will change the hard coded ints to inputs from a label. Please confirm if how I did this is not vulnerable to any sql injection or hacks.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;



public partial class Stats : System.Web.UI.Page
{

    public SqlDataReader DataReader;
    public SqlCommand Command;
    string queryString = ("INSERT INTO UserData (UserProfileID, ConfidenceLevel, LoveLevel, HappinessLevel) VALUES (@UID, @CL, @LL, @HL);");
    //string queryString = ("INSERT INTO UserData (UserProfileID, ConfidenceLevel, LoveLevel, HappinessLevel) VALUES ('a051fc1b-4f51-485b-a07d-0f378528974e', 2, 2, 2);"); 

    protected void Page_Load(object sender, EventArgs e)
    {
       LabelUserID.Text = Membership.GetUser().ProviderUserKey.ToString();

    }

    protected void Button1_Click(object sender, EventArgs e)
    {

        //connect to database
        MySqlConnection database = new MySqlConnection();
        database.CreateConn(); 

        //create command object
        Command = new SqlCommand(queryString, database.Connection);

        //add parameters. used to prevent sql injection
        Command.Parameters.Add("@UID", SqlDbType.UniqueIdentifier);
        Command.Parameters["@UID"].Value = Membership.GetUser().ProviderUserKey;

        Command.Parameters.Add("@CL", SqlDbType.Int);
        Command.Parameters["@CL"].Value = 9;

        Command.Parameters.Add("@LL", SqlDbType.Int);
        Command.Parameters["@LL"].Value = 9;

        Command.Parameters.Add("@HL", SqlDbType.Int);
        Command.Parameters["@HL"].Value = 9;

        Command.ExecuteNonQuery(); 


    }

}

Answer 1

Passing parameters to SQL saves you from having to build a dynamic SQL string.

Building dynamic SQL statements is a HUGE security risk because people can inject their own SQL code into your application, possibly executing undesirable commands against your data.

There are some good samples of possible SQL Injection attacks at:

SQL Injection Attacks by Example

There are two ways of passing parameters to SQL statements. One is to use Stored Procedures like you mentioned. The other is to use parameterized queries (which is actually what I prefer).

A parameterized query is actually quite easy in .NET:

using(SqlConnection conn = new SqlConnection(connString))
{
    SqlCommand command = 
        new SqlCommand("SELECT * FROM Users WHERE Username = @Username", conn);

    command.Parameters.Add(new SqlParameter("@Username", "Justin Niessner"));

    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataTable dt = new DataTable();

    adapter.Fill(dt);
}

In that example, the parameter was @Username and we used the Parameters collection of the SqlCommand object to pass in the value.

Answer 2

It'll protect you from little Bobby Tables.

http://xkcd.com/327/

Answer 3

Here is an example:

        SqlConnection myConn = new SqlConnection("my connection string");

        SqlCommand myCmd = new SqlCommand("myStoredProcName", myConn);

        myCmd.CommandType = CommandType.StoredProcedure;

        myCmd.Parameters.AddWithValue("@cGroupID", 0).Direction = ParameterDirection.InputOutput;
        myCmd.Parameters.AddWithValue("@gType", "C");
        myCmd.Parameters.AddWithValue("@requirement", "Y");
        myCmd.Parameters.AddWithValue("@usercode", "XX");

        myConn.Open();
        myCmd.ExecuteNonQuery();

        int covID = (int)myCmd.Parameters["@cGroupID"].Value;

Using Parameters is a good way to prevent some errors, and help stop injection vectors. It also allows OUTPUT parameters, as in the example above where cGroupID comes back with a value that I can access.

Answer 4

The primary issue with simply concatenating strings is that it often leaves you vulnerable to SQL Injection attacks.

Google SQL Injection or read here.

Answer 5

In addition to SQL injection and the like, parameterized queries always look the same to the SQL server. Most likely the query execution plan will be cached, so if you send the same query again it will run faster. This would be very noticeable if you are running the same query many times in a loop or you have many clients querying your server.