March 8, 2021
Hot Topics:

OP-ED: Why LINQ to SQL is a Better Option than Straight SQL

  • By Paul Kimmel
  • Send Email »
  • More Articles »

Listing 1: This code is subject to SQL injection attack because user input is sent right to SQL Server.

Imports System.Data.SqlClient

Partial Public Class _Default
   Inherits System.Web.UI.Page

   Protected Sub Page_Load(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles Me.Load

   End Sub

   Protected Sub Button1_Click(ByVal sender As Object, _
      ByVal e As EventArgs) Handles Button1.Click

      Dim connectionString As String = _
         "Data Source=.\SQLExpress;Initial>span _
            Catalog=northwind;Integrated Security=True"

      Dim sql As String = _
         "SELECT CustomerID, CompanyName FROM Customers _
            WHERE CustomerID = '{0}'"

      Using connection As SqlConnection = _
         New SqlConnection(connectionString)
         Dim command As SqlCommand = _
            New SqlCommand(String.Format(sql, _
               TextBox1.Text), connection)

      Dim table As DataTable = New DataTable
      Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)

      GridView1.DataSource = table

      End Using
   End Sub
End Class

The sample literally reads the TextBox.Text value and plugs it into the SQL without error checking. (It is not recommended that you write code like this. Although there is code like this in production, this example is just a very straightforward way to demonstrate how injection works.)

Suppose next that what your unhappy guest does is, instead of entering a CustomerID, he enters the follow text:

"ALFKI' UNION SELECT 'database name' as dummy, name _
   FROM master.sys.databases --"

The preceding text turns the harmless looking SQL from Listing 1 to:

SELECT CustomerID, CompanyName FROM Customers _
   WHERE CustomerID = 'ALFKI' UNION _
   SELECT 'database name' as dummy, name from master.sys.databases

The comment (--) characters at the end of the injection get rid of your single quote, replacing it with his single quote and a UNION SELECT. The UNION SELECT, if successful, shows the user all of the databases on your server. Now, your web page might produce something like the results shown in Figure 3.

Figure 3: A successful UNION SELECT turns an ionocuous web page into a malicious user's snoop and destroy tool.

There are a lot of ways to fix this simple example. You could provide a drop down only. You could use regular expressions to screen the input value for unacceptable and potentially harmful values such as DELETE FROM, single quotes, and SQL comments (--), or you could use LINQ to SQL.

Page 2 of 4

This article was originally published on August 15, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date