1

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

Improve this question
1
  • Any problems with my answer ? Do you need more help ? Commented Dec 8, 2011 at 17:04

5 Answers 5

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

See Using Parameters with the SqlDataSource Control

And SqlDataSource.SelectParameters Property

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

Improve this answer

Comments

2

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.

Improve this answer

Comments

1

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();
Improve this answer

2 Comments

what do I put in my `case "ValidFied1". Can give me an example? Thks
@RUiHAO: All values from DropDownList1 or (this should be the same) all field names from TABLE1 which user is allowed to perform search on. This is necessary to prevent sending arbitrary content (SQL Injection) in DropDownList1.Text field.
0

  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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.