In this lab, we clean up a couple of egregious security holes in the current implementation of the Movies application. The first relates to SQL injection attacks and the second relates to clear-text passwords. There are, mind you, many more holes, but we'll content ourselves with these.

SQL Injection Attacks

SQL injection attacks are possible with your database application code doesn't check the data it is receiving from the user interface. One common example of this is when a login/password-checking page is coded something like this:

SQLCommand.CommandText = "SELECT password FROM Users WHERE loginID = '" + _
                                               txtLogin.Text + "     '"
dataReader = SQLCommand.ExecuteReader()
dataReader.Read()
If txtLogin.Text = dataReader.GetString(0) Then
    'The user passes the login check.
Else
    'The user fails the login check.
End If

Note here the that whatever the user types into the login text field (i.e., txtLogin) will be passed, without scrutiny to SQL Sever in the SQL command. This may seem reasonable, but is, in fact, a major security hole. What if the user types ';drop table myTable;-- into the Login text field, as shown here:

Given the code listed above, the SQL command sent to SQL Server would turn out to be:

SELECT password FROM Users WHERE loginID = '

';drop table myTable;--

'

The inclusion of the ";" splits the command into 2 separate SQL commands, both of which will be executed by SQL Server. The first will fail to find any record with login ID equal to '', so the user login is destined to fail (as it did in the example image above), but the second command will also run doing whatever the user wanted it to do (i.e., dropping/deleting the myTable table). Can you figure out what the "--" does at the end? It is very important.

Try this out on your movies application. If you code is anything like the code given above, you'll be able to:

1. Create a dummy table in SQL Server (e.g., myTable), one that you won't mind dropping.

2. Try using the exploit just discussed to drop your dummy table. Note that if your attack succeeds, SQL Server may still keep the dummy table name in its list of tables but you won't be able to list the records. If your code is not susceptible to this type of SQL injection, make sure that you know why.

3. Now, do a similar exploit to insert a new user record into your database with a userID/password pair of your choice. If you succeed with this, you should be able to log into your system with the "fake" userID/password that you've added.

It's scary how simple this sort of thing is with SQL.

Preventing SQL Injection Exploits

There are relatively simple ways to prevent SQL injection attacks, but the first principle of security is always this: Never assume anything! Don't assume that people won't try to hack your site; they will. Don't assume that hackers won't be able to guess your database and code structure; they will - standard structures like these just aren't that hard to guess. To help prevent these attacks, you should implement layers of security such as the following:

• Use SqlCommand parameters to construct SQL dynamic commands rather than using simple concatenation. Here is a simple example of this:

  SQLCommand.CommandText = "SELECT password FROM Users WHERE loginID = @loginID"
  SQLCommand.Parameters.Add("@loginID", txtLogin.Text)
  

  Notice that the login ID typed in by the user is filtered through an SQL parameter. Among other things, this will automatically escape all the non-value characters (e.g., ', ;, -). Change your login event handler code to use SQL command parameters now and verify that this prevents the simple exploits you did before.

• Don't use the systems administrator account (i.e., sa) to access your database. This account has far more privileges that most web applications require, some of which are very dangerous (e.g., drop table). Rather, use a SQL Server login with limited privileges. If you haven't done this already, do so now as follows:

  1. In SQL Server, create a new "Login" for your Movies application, using SQL Server authentication and setting Movies and the default database.
  2. Under the "Database Access" tab, select the correct database, choose "public" and click on "Properties" then "Permissions". Now, set the public permissions on this login to be only those required for your application (i.e., SELECT/UPDATE privilege for only the Movies database).
  3. In VS.Net, change the connection string in your application pages to use this new login/password rather than the sa login/password.

• Use stored procedures where possible. We won't have time to cover stored procedures in this course, but they compile the SQL programs that access the database within SQL Server, beyond the control of your server-side ADO.Net code.

• Turn off debugging messages in IIS so that hackers can't use your helpful error messages to reverse-engineer the structure of your code and database. This is relatively simple, but given that we're still developing the applications, we won't do it now.

Make the changes required above and then double-check that your system still works properly.

Encrypting Passwords

One last common security measure is to encrypt your passwords in the database. You don't want to have people's passwords lying around in clear text, even on your server. To implement password encryption, change your code as follows:

1. In Sql Server, change the password of one of your Movies users to:

   5A105E8B9D40E1329780D62EA2265D8A
   

   This is the MD5 encrypted form of the "test1" password.

2. Modify your password checking code to encrypt the password typed in by the user before comparing it to what comes out of the database. Here is the authentication function that does the encryption:

   FormsAuthentication.HashPasswordForStoringInConfigFile(yourPasswordField.Text,"md5")
   

   Note that this function provides both "MD5" and "SHA1" hashing methods. You may need to include the following command at the very top of your login.aspx.vb file in order to access the Hash function:

   Imports System.Web.Security
   

   The system should now behave as it did before, using encrypted rather than clear text passwords. Note, however, that your old userID/password pairs won't work until you load the encrypted form of the other passwords into the database.

To incorporate encryption into your applications, you will likely need a "Change Password" page that allows users to change their password to something other than 'test1'.

Checking In

Use KV to submit the modified login page code file from your Movies application.

Your Final Project

There is no project #14; you should, however, incorporate the security measures discussed in this lab (i.e., SQL command parameters, limited-privilege SQL Server accounts, and encrypted passwords) into your PDP.