CODEWITHSUNDEEP
c#asp.netsqlsql-serverstored-procedures
Greg McNulty
Greg McNulty

Reputation: 1466

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

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(); 


    }

}

Upvotes: 14

Views: 28211

Answers (5)

Nelson Rothermel
Nelson Rothermel

Reputation: 9736

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.

Upvotes: 1

StriplingWarrior
StriplingWarrior

Reputation: 156459

It'll protect you from little Bobby Tables.

http://xkcd.com/327/

Upvotes: 8

CodingWithSpike
CodingWithSpike

Reputation: 43698

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

Google SQL Injection or read here.

Upvotes: 2

Fosco
Fosco

Reputation: 38506

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.

Upvotes: 4

Justin Niessner
Justin Niessner

Reputation: 245389

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.

Upvotes: 21

Related Questions