Roll-your-own login database with SQL and C#.NET
This is all pretty simple really. There's two parts to it: a database to store usernames and passwords, and code to validate the user on the web page or application. This code can be used in C#.NET applications, or in the code-behind files of ASP.NET applications. It will translate to VB fairly simply, but the SQL part would remain the same whether you use VB, C# or anything else.
First, we will create a database table and stored procedures to store new users and validate the existing users. In this example I will not use encryption, but if you wanted to, these same database principles would apply. For SQL Server 2000 and later editions, you can create a case-sensitive column when you declare your table, so to enforce case-sensitivity, we will nip any problems in the bud right here. Here's the code to create the table:
create table user_login ( rep_id int identity(1,1) NOT NULL, username varchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, password varchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL )
The COLLATE statements cause SQL server to treat these columns as case-sensitive in all queries from now on. This is a really great feature because programmers can't mess this up, no matter what, these columns will be case-sensitive. In order to increase performance, you should create an index on this table for the username column. You may also consider declaring (username + password) to be the primary key and making that the clustered index. If you have different user levels on your site, you should store the user level in this table, and add it to the stored procedures below as well. This table could also be used to store contact information for the user, but I prefer to store that in another table (using the rep_id to join), which keeps the login table fast, particularly if there are a lot of users. Now that our table is created, we need stored procedures to interact with it. The reasons to use stored procs are too numerous to list, but in this case, we are looking for security, and we want to avoid SQL Injection attacks if at all possible. Using stored procs exclusively is a great first step in the security battle. Another step will be shown later and the third step, encryption, will not be discussed here, but is a great addition to any security model. We need two stored procs, one to create new users, and one to validate existing users.
create proc create_user @username varchar(100), @password varchar(100) as declare @result int --first make sure this is a valid new user --username must be unique if exists (select 1 from user_login where username = @username) begin set @result = -1 end else begin insert user_login (username, password) select @username, @password set @result = @@identity end select result = @result return @result GO create proc validate_user @username varchar(100), @password varchar(100) as declare @result int if exists (select 1 from rep where username = @username and password = @password) begin select @result = rep_id from rep where username = @username and password = @password end else begin select @result = -1 end select result = @result return @result GO
These procs are fairly straightforward. The first one creates a new user. If the user was not created because of non-unique username, then the proc returns -1, otherwise it will return the ID of the new user. The second procedure is similar. It takes the username and password, and returns the ID of the user who logged in. If the login is incorrect, the proc returns -1. In order to make things easier on the recieving end, I 'select out' the values, as well as 'return' them from both procs. This gives me options in picking up that value in client apps.
In order to prevent SQL Injection attacks, we need to check all user input if it is going to be used in an SQL command string. This should be done with web apps or windows apps, since users may be malicious in either case. The following simple function will negate most attempts at SQL Injection, by replacing all occurences of '(single quote) with ''(two single quotes) and \(blackslash) with \ (two backslashes).
private string sql_fix(string s) {
return s.Replace("'", "''").Replace("\\", "\\\\");
}
Interestingly enough, this allows usernames or passwords with any number of single quotation marks or backslashes. In order to use our new database, we will need code to create new users. This code can be written very simply in C#. Assuming you have correctly opened your database connection, and you have some form elements with the new username and password, you can create a new user with the following C# code. I've used the sql_fix() function here, since this is user input being passed to SQL Server.
public int create_user(string username, string password) {
int result = -1;
SqlCommand cmd = new SqlCommand("exec create_user '" + sql_fix(username) + "', '" + sql_fix(password) + "'", conn);
SqlDataReader r = c.ExecuteReader();
if (r.GetInt32(0) > 0) {
//we got a positive number back, so it worked
result = r.GetInt32(0);
} else {
throw new Exception("User creation failed");
}
r.Close();
return result;
}
This method mimics the behavior of the SQL proc it's tied to, returning the ID of the user who was created, or throwing an exception if the user creation failed. You could modify the stored proc and this function to indicate a better error message, by sending an error code or message back from the stored proc. You could also pick up this value and use it in your application to do further interaction with the new user. To validate a user, you simply call our other stored procedure and act accordingly. In this example, I'm just going to throw an exception if the login is incorrect, but you could handle the situation any way you see fit. I've used the sql_fix() function again to avoid any possible SQL Injection attacks.
public int validate_user(string username, string password) {
int result = -1;
SqlCommand c = new SqlCommand("exec validate_user '" + sql_fix(username) + "', '" + sql_fix(password) + "'", conn);
SqlDataReader r = c.ExecuteReader();
if (r.GetInt32(0) > 0) {
//we got a positive number back, so they are an authorised user
result = r.GetInt32(0);
} else {
throw new Exception("Incorrect login");
}
r.Close();
return result; //this will be -1 unless updated by a successful login
}
This method again mimics it's SQL proc, returning the ID of the user who logged in.
By using these two functions with your database, you should be able to manage
user logins simply and effectively. You may wish to add encryption to this, and
further validation of the username and password strings (ie: to check for 'strong' passwords
in user creation), but this should give you a good basis to get started with. Please leave comments
if you have any questions and I will be able to answer them for everyone!
Have fun with it!
Jasmine
