CODEWITHSUNDEEP
c#asp.netsql-server-2005sql-injection
user1084683
user1084683

Reputation:

Prevent SQL Injection in SELECT statement

I am using VS2005 C# ASP.NET and SQL Server 2005.

I have a search function on my asp page and I feel that my SELECT query is vulnerable to SQL injection.

This is my current SELECT statement:

string LoggedInUser = (User.Identity.Name);

SqlDataSource1.SelectCommand = "SELECT * FROM [TABLE1] where [" + DropDownList1.Text + "] like '%" + searchTB.Text + "%' AND [empUser] LIKE '%"+LoggedInUser+"%'";
SqlDataSource1.DataBind();

*where searchTB is my search text box; DropDownList1 is my search category; and LoggedInUser is the username of the logged in user.

I have implemented parameter instead of concatenation in one of my INSERT statement:

string sql = string.Format("INSERT INTO [TABLE2] (Username) VALUES (@Username)");
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("Username", usernameTB.Text);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

I would like to change my SELECT statement like my INSERT statement, using parameter instead. May I know how should I change it?

Thank you

Upvotes: 1

Views: 2523

Answers (5)

Zo Has
Zo Has

Reputation: 13028

  1. You can simply use a filter expression for the SQL datasource SQL Datasource filter expression

  2. You can write your own select function method with object datasource/datatable

Upvotes: 0

dknaack
dknaack

Reputation: 60466

You can add parameters to your selectcommand using

SqlDataSource s = new SqlDataSource();
s.SelectParameters.Add("paramName", "paramValue");

There are other parameter collections for delete, update and insert too.

s.DeleteParameters
s.UpdateParameters
s.InsertParameters

More Information:

MSDN: SqlDataSource.SelectParameters Property

Programmatically Using SqlDataSource

hope this helps

Upvotes: 4

Thea
Thea

Reputation: 8067

Write a method that gets the data sourse and use sql parameters for the query. Here is a good example how to add parameters in a command object

SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

I would use a method for the query so that I separate the Database Access from the UI functionality. Also, this allows to reuse the query.

Upvotes: 2

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10512

It's not a straightforward task to dynamically specify a fieldname in query, so I'd suggest just doing switch/case validation for field name, like this:

switch (DropDownList1.Text)
{
    case "ValidField1":
    case "ValidField2":
    ...
        break;
    default: 
        throw new ArgumentException(...); // or prevent query execution with some other statement
}

SqlDataSource1.SelectCommand = "SELECT * FROM [TABLE1] where [" + DropDownList1.Text + "] like @value AND [empUser] LIKE @user";
SqlDataSource1.SelectParameters.Add("value", "%" + searchTB.Text + "%");
SqlDataSource1.SelectParameters.Add("user", "%"+LoggedInUser+"%");
SqlDataSource1.DataBind();

Upvotes: 1

ukessi
ukessi

Reputation: 1391

See Using Parameters with the SqlDataSource Control

And SqlDataSource.SelectParameters Property

You can specify SelectParameters Property for SqlDataSource to use parameterized SQL query

Upvotes: 2

Related Questions