• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

Asp.Net SqlServer Injection attack

in the following example it is possible to have an sql injection attack?

To query some products for ex. the swebsite returns the following link:
http://www.mywebsite.com/products.aspx?idcat=14&index=3

After this code  query some records to display:
Dim cat As String=Request.QueryString("IdCat")
Dim Index as String=Request.QueryString("Index")
SqlStr = "Select name,price from products where idcat=" & Cat & " and index= " & index
adapter = New SqlDataAdapter(SqlString, conn)
adapter.Fill(dataset, "Products")
How can prevent.
0
rflorencio
Asked:
rflorencio
3 Solutions
 
DhaestCommented:
Some other interesting articles about this subject

How To: Protect From SQL Injection in ASP.NET
http://msdn.microsoft.com/en-us/library/ff648339.aspx

Stop SQL Injection Attacks Before They Stop You
http://msdn.microsoft.com/en-us/magazine/cc163917.aspx
0
 
Alan WarrenCommented:
Hi rflorencio,

Probably the best defence against SQL injection is to use a stored procedure rather than concatenated SQL.
CREATE PROCEDURE [dbo].[Products_Sel]
	 @IdCat int = 0  -- modify types as required
	,@Index int = 0
AS
BEGIN

  SELECT name,price 
  FROM products 
  WHERE idcat=@IdCat and [index]=@Index

END

-- Testing
-- exec Products_Sel 2, 6

Open in new window

Then on your page something like this:
<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
  <form id="form1" runat="server">
    <div>
      Select CategoryID: 
      <asp:DropDownList ID="CatDropDownList" runat="server" AutoPostBack="True">
        <asp:ListItem Selected="True" Value="1">Cat1</asp:ListItem>
        <asp:ListItem Value="2">Cat2</asp:ListItem>
      </asp:DropDownList><br />
     Select Index: <asp:DropDownList ID="IndexDropDownList" runat="server" 
        AutoPostBack="True">
        <asp:ListItem Value="1">Index1</asp:ListItem>
        <asp:ListItem Value="2">Index2</asp:ListItem>
      </asp:DropDownList>


      <asp:DataList ID="ProductsDataList" runat="server" DataSourceID="ProductsSqlDataSource">
        <ItemTemplate>
          name:
          <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' />
          <br />
          price:
          <asp:Label ID="priceLabel" runat="server" Text='<%# Eval("price") %>' />
          <br />
          <br />
        </ItemTemplate>
      </asp:DataList>

     <asp:SqlDataSource 
          ID="ProductsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:cnAkoJo %>"
          SelectCommand="Products_Sel" SelectCommandType="StoredProcedure" >
          <SelectParameters>
            <asp:ControlParameter ControlID="CatDropDownList" Name="IdCat" 
              PropertyName="SelectedValue" Type="Int32" />
            <asp:ControlParameter ControlID="IndexDropDownList" Name="Index" 
              PropertyName="SelectedValue" Type="Int32" />
          </SelectParameters>
      </asp:SqlDataSource>    

    </div>
  </form>
</body>
</html>

Open in new window

Nobody types anything in, no SQL injection!

Alan ";0)
0
 
rflorencioAuthor Commented:
Hi  alanwarren,

Even if i do not use stored procedures in some cases, using parameters in queries, has the same effect right?
0
 
Alan WarrenCommented:
Well, IMHO no.

The point is that when using a stored procedure your only sending the name of the stored procedure and any associated parameters up the wire, but using concatenated SQL you are sending the entire executable SQL script, which could be intercepted and modified along the way.

I think interception is highly unlikely but it could happen.

However, when using a stored procedure, in the event of malicious interception, you are not giving out much information about your db structure (field names, table names etc...), that's why it's considered best defence against sql injection.

Alan
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now