CODEWITHSUNDEEP
c#asp.netsqlsql-servercode-injection
Mike91
Mike91

Reputation: 540

Preventing SQL injection when setting a sqldatasource select statement in ASP.NET c#

I have a quick question with regards to preventing SQL injection in my application.

Two different features of my web application use textboxes that a user can use to search for items from a MSSQL database. This information is displayed in a GridView.

I create the queries by using this method:

sqldatasource.SelectCommand = "SELECT x from x where this_id LIKE '%" + txtbox.Text + "%' ORDER BY x ASC";

Obviously taking user input and entering it straight into a query like this will open it up to basic SQL injection. Please could someone explain how I can parametrise this to prevent SQL injection in my application?

Thanks in advance

Upvotes: 1

Views: 2595

Answers (3)

Mike91
Mike91

Reputation: 540

Thanks for your answers. In the end I used this from the MSDN site on parametrised queries:

    GetSubInfo.SelectParameters.Add("xparam", txtbox.Text);
    GetSubInfo.SelectCommand = "SELECT x from x where x_id LIKE @xparam ORDER BY x ASC";

This is very similar to Hogan's answer, with slightly different syntax. Hope this helps!

Upvotes: -1

Hogan
Hogan

Reputation: 70523

sqldatasource.SelectCommand = @"SELECT x from x where this_id LIKE @inText ORDER BY x ASC";

cmd.Parameters.AddWithValue("@inText", "%" + txtbox.Text + "%");

Note, you are going to be in for pain if you start a LIKE with a wildcard. A big table will be sad and go slowly.

Upvotes: 5

Andrew Gray
Andrew Gray

Reputation: 3790

One (not very good, but better than nothing) way is to sanitize the contents of txtBox.Text, and then use the sanitized result.

sqlsource.SelectCommand = "SELECT x from x where this_id LIKE '%" + SanitizedString(txtBox.Text) + "%';";

// ... codes ...

private string SanitizedString(string given)
{
   string sanitized = given.Replace(";", string.Empty);
   sanitized = sanitized.Replace("--", string.Empty);
   // ... ad nauseum, conditions galore ...

   return sanitized;
}

...However, that's VERY labor intensive.

The thing to do would be to use a bind variable in the form of a stored procedure. From what I can see, you could bind your filter txtbox.Text condition, and then just pass that in.

The query would be simple enough on the db, something like:

SELECT x from X where this_id LIKE :filter ORDER BY x ASC

To call it, you would need to research how MSSQL communicates with stored procedures.

Bind variables make your code thoroughly SQL-injection-proof. Love bind variables, for they will love you. And evil hax0rs will not.

Upvotes: 0

Related Questions