Contents tagged with SQL

  • Using Parameters with Dynamic SQL

    Some programming situations require you to use Dynamic SQL. Of course the problem with using Dynamic SQL is that this can lead to SQL Injection attacks. However, you can avoid these problems, by just changing how you submit Dynamic SQL to your back end database.

    To illustrate this, consider the sample table of users listed below.

    CREATE TABLE User
    (
    Login char(16) not null primary key,
    Password varchar(20) not null
    )
    go
    INSERT INTO User values('PSheriff', 'password')
    go
    INSERT INTO User values('Keng', 'password')
    go

    You can copy and paste the above SQL code into your database management system and create this table. After you have created this table, you might create a login screen and use some code like the following to see if the users are in the User table.


    Private Sub BADLoginCode()
     Dim sql As String
     Dim cmd As SqlClient.SqlCommand
     Dim rows As Integer

     sql = " SELECT Count(*) As TotalRows FROM User "
     sql &= " WHERE Login = '" & txtLogin.Text & "'"
     sql &= " AND Password = '" & txtPassword.Text & "'"

     cmd = New SqlClient.SqlCommand(sql)
     cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)
     cmd.Connection.Open()

     rows = Convert.ToInt32(cmd.ExecuteScalar())

     If rows > 0 Then
       MessageBox.Show("Success")
     Else
       MessageBox.Show("Failure")
     End If
    End Sub

    The problem with the above code is if a hacker where to type in the following into the Login ID field;

    ' OR 1=1 DELETE FROM Users --

    Then the resulting SQL that is submitted to the back end database is the following:


    SELECT Count(*) As TotalRows
    FROM User
    WHERE Login = '' OR 1=1
    DELETE FROM User --'
    AND Password = ''


    As you can see this will not only allow the user into the application, but will also delete all other users in the system! This is not a good thing.

    However, with just a very minor change to this code, you can protect yourself against this type of attack. Just like when calling a stored procedure you use Parameters on the command object to submit the login id and password to the input parameters, you can do the same type of coding with dynamic SQL.


    Private Sub LoginGood()
     Dim sql As String
     Dim cmd As SqlClient.SqlCommand
     Dim rows As Integer

     sql = " SELECT Count(*) As TotalRows FROM User "
     sql &= " WHERE Login = @sLogin "
     sql &= " AND Password = @sPassword"

     cmd = New SqlClient.SqlCommand(sql)
     cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)
     cmd.Connection.Open()
     cmd.Parameters.Add(New _
       SqlClient.SqlParameter("@Login", SqlDbType.Char))
     cmd.Parameters.Add(New _
       SqlClient.SqlParameter("@Password", SqlDbType.Char))
     cmd.Parameters.Item("@Login").Value = txtLogin.Text
     cmd.Parameters.Item("@Password").Value = txtPassword.Text

     rows = Convert.ToInt32(cmd.ExecuteScalar())
     If rows > 0 Then
       MessageBox.Show("Success")
     Else
       MessageBox.Show("Failure")
     End If
    End Sub

    Notice the use of the Parameters in the dynamic SQL. When you now run this code, the command object and the parameters ensure that no SQL injection attacks will affect your code.

    Good Luck With Your Coding,
    Paul Sheriff

    ** SPECIAL OFFER FOR MY BLOG READERS **
    Visit http://www.pdsa.com/Event/Blog for a free eBook on "Fundamentals of N-Tier".

     

    Read more...

  • SQL for the .NET Developer

    There are a lot of .NET Developers out there that are not taking advantage of all the SQL has to offer. If you are writing .NET code to iterate over database tables to aggregate data, ie, summing, averaging, etc., then you need to learn more about SQL. Why write all that code in VB.NET or C# when you could use ANSI SQL commands to do the job for you. You will not only save programming time, but also memory and processing time for your users.

    Read more...