Stop SQL Injection Attacks

ask the PRO

LANGUAGES: C#

TECHNOLOGIES: SQL Server | Forms Authentication

Stop SQL Injection Attacks

Learn the skills - and get the code - you need to stop this common form of malicious meddling.

By Jeff Prosise

Q. My company is interested in using forms authentication to restrict access to portions of our Web site, but we're concerned about SQL Injection attacks. Does ASP.NET's forms authentication module contain built-in protection against such attacks? If not, what must I do to guard against them?

A. ASP.NET does not prevent SQL Injection attacks from succeeding, but it provides tools for stopping them. The onus is on you to apply those tools. Do it right and you can make forms authentication virtually bulletproof against SQL Injection attacks.

SQL Injection attacks are among the most common - and the most onerous - hack attacks directed against Web sites. If you are unfamiliar with SQL Injection attacks, check out the code for the Web page in Figure 1.

<%@ Import NameSpace="System.Data.SqlClient" %>

<html>

<body>

<h1>Please Log In</h1>

<hr>

<tr>

<td>

<asp:TextBox ID="UserName" RunAt="server" />

</td>

</tr>

<tr>

<td>

Password:

</td>

<td>

<asp:TextBox ID="Password" TextMode="password"

RunAt="server" />

</td>

</tr>

<tr>

<td>

<asp:Button Text="Log In" OnClick="OnLogIn"

RunAt="server" />

</td>

</tr>

</table>

</form>

<hr>

</body>

</html>

void OnLogIn (Object sender, EventArgs e)

{

if (Authenticate (UserName.Text, Password.Text))

Output.Text = "Welcome, " + UserName.Text + "!";

else

Output.Text = "Invalid login";

}

bool Authenticate (string username, string password)

{

SqlConnection connection = new SqlConnection

("server=localhost;database=weblogin;uid=sa");

try {

connection.Open ;

string cmdtext = String.Format

("select count (*) from users where " +

"username=\'{0}\' and cast (password as " +

"varbinary)=cast (\'{1}\' as varbinary)",

username, password);

SqlCommand command =

new SqlCommand (cmdtext, connection);

int count = (int) command.ExecuteScalar ;

return (count > 0);

}

finally {

connection.Close ;

}

</script>

Figure 1. DumbLogin.aspx uses a SELECT COUNT command to validate a username and password.

Like many login pages used in conjunction with forms authentication, DumbLogin.aspx solicits a username and password from the user to query a credentials database. For this example, I utilize a SQL Server database named WebLogin, which you can create with the SQL installation script in Figure 2.

CREATE DATABASE WebLogin

USE WebLogin

CREATE TABLE Users (UserName varchar(32) NOT NULL,

Password varchar(32) NOT NULL)

INSERT INTO users (UserName, Password)

VALUES ('Bob', 'mxyzptlk')

INSERT INTO users (UserName, Password)

VALUES ('Alice', 'nomalice')

Figure 2. This SQL script creates the WebLogin database used by DumbLogin.aspx. You can execute the script with an OSQL command or with the SQL Server Query Analyzer.

WebLogin contains a table named "Users" that holds two username/password pairs. DumbLogin.aspx's Authenticate method checks the validity of the credentials the user enters by executing a SELECT COUNT command against the Users table. If the user types "Bob" and "mxyzptlk," this query results:

SELECT COUNT (*) WHERE UserName='Bob' AND

CAST (Password) AS VARBINARY=CAST ('mxyzptlk') AS VARBINARY

SELECT COUNT returns the number of records that meet the criteria specified in the query; 1 if a matching record exists, 0 if it does not. (In case you were curious, casting the password to VARBINARY is a sneaky way to perform a case-sensitive comparison.) DumbLogin.aspx greets a successful login with a personalized greeting. An unsuccessful login produces an "Invalid login" message.

At first glance, DumbLogin.aspx looks quite reasonable. Closer examination reveals, however, that DumbLogin.aspx's Authenticate method is wide open to SQL Injection attacks. To demonstrate, open DumbLogin.aspx in your browser and enter this username:

' OR 1=1--

The Authenticate method validates the login, even though there is no such user in the database and you entered no password.

You just perpetrated a classic SQL Injection attack. The username you entered results in this query being executed against the database:

SELECT COUNT (*) WHERE UserName='' OR 1=1-- AND

CAST (Password) AS VARBINARY=CAST ('') AS VARBINARY

Get the picture? The "OR 1=1" in the WHERE clause matches every record in the database. In addition, the characters "--" comment out the rest of the statement, rendering the AND clause irrelevant. Because the Users table contains two records, the SELECT COUNT command returns 2 and Authenticate returns true. This is hardly a contrived scenario. The number of Web sites vulnerable to this type of attack is surprising. If you do not take steps to prevent attack, you'll leave the door open to malicious users who wish either to view sensitive data or destroy it. For an example of how harmful SQL Injection attacks can be, run the sample again and enter this username:

' OR 1=1;DROP TABLE USERS--

Check your database and you'll find that the Users table no longer exists. In addition to fooling the Authenticate method, this bogus username uses a DROP TABLE command to delete the table from the database.

Now that you understand how SQL Injection attacks work, what can you do to thwart them? Here's a three-step recipe for stopping SQL Injection attacks in their tracks:

1. Never trust user input. Instead, validate the input and disallow characters that can be used in SQL Injection attacks, cross-site scripting attacks, and other hacks. A typical strategy is to use ASP.NET's validation controls to reject usernames and passwords containing non-alphanumeric characters.

2. Use stored procedures instead of dynamic SQL statements. Stored procedures are generally favored because they're fast. But they also provide added security because they are far less vulnerable to SQL Injection attacks than dynamically compiled SQL statements.

3. Use a weak account to execute database access code. The DROP TABLE command succeeded because it was executed through a connection opened with SQL Server's built-in SA account, a privileged account intended for administrators. Had DumbLogin.aspx used a weak account that lacked permission to drop tables, DROP TABLE would have failed.

Figure 3 contains a revised version of DumbLogin.aspx, named SmartLogin.aspx.

<%@ Import NameSpace="System.Data" %>

<%@ Import NameSpace="System.Data.SqlClient" %>

<html>

<body>

<h1>Please Log In</h1>

<hr>

<tr>

<td>

<asp:TextBox ID="UserName" RunAt="server" />

</td>

<td>

<asp:RequiredFieldValidator

ControlToValidate="UserName"

ErrorMessage="Please enter a user name"

Display="dynamic"

RunAt="server"

<asp:RegularExpressionValidator

ControlToValidate="UserName"

ValidationExpression="[a-zA-Z0-9]*"

ErrorMessage="Invalid user name"

Display="dynamic"

RunAt="server"

</td>

</tr>

<tr>

<td>Password:</td>

<td>

<asp:TextBox ID="Password" TextMode="password"

RunAt="server" />

</td>

<td>

<asp:RequiredFieldValidator

ControlToValidate="Password"

ErrorMessage="Please enter a password"

Display="dynamic"

RunAt="server"

<asp:RegularExpressionValidator

ControlToValidate="Password"

ValidationExpression="[a-zA-Z0-9]{8,}"

ErrorMessage="Invalid password"

Display="dynamic"

RunAt="server"

</td>

</tr>

<tr>

<td>

<asp:Button Text="Log In" OnClick="OnLogIn"

RunAt="server" />

</td>

</tr>

</table>

</form>

<hr>

</body>

</html>

void OnLogIn (Object sender, EventArgs e)

{

if (Authenticate (UserName.Text, Password.Text))

Output.Text = "Welcome, " + UserName.Text + "!";

else

Output.Text = "Invalid login";

}

bool Authenticate (string username, string password)

{

if (!Page.IsValid)

return false;

SqlConnection connection =

new SqlConnection ("server=localhost;" +

"database=weblogin;uid=safeuser;pwd=safeuser");

try {

connection.Open ();

SqlCommand command = new SqlCommand

("proc_IsUserValid", connection);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add ("@UserName", username);

command.Parameters.Add ("@Password", password);

int count = (int) command.ExecuteScalar ();

return (count > 0);

}

finally {

connection.Close ();

}

</script>

Figure 3. SmartLogin.aspx validates a user's login name and password against the WebLogin database and guards against SQL Injection attacks.

SmartLogin.aspx incorporates all three fixes described in this article. Here's a rundown on those fixes.

First, SmartLogin.aspx uses validation controls to filter user input. The RegularExpressionValidator attached to the username control rejects usernames containing non-alphanumeric characters. The RegularExpressionValidator attached to the password control does the same and also ensures that the password is at least eight characters long. Expressions such as "' OR 1=1-" do not make it past the validators. For good measure, SmartLogin.aspx pairs each TextBox control with a RequiredFieldValidator as well.

Second, SmartLogin.aspx executes the SELECT COUNT command using a stored procedure named proc_IsUserValid. Even if bogus input were to make it past the validators, the stored procedure would return 0 if passed the username "' OR 1=1--".

Third, SmartLogin.aspx does not use the SA account to access the database. Instead, it uses a weaker account named SafeUser that lacks permission to drop tables. In fact, the SafeUser account created by the revised database script in Figure 4 lacks the power to do anything but call proc_IsUserValid.

CREATE DATABASE WebLogin

USE WebLogin

CREATE TABLE Users (UserName varchar(32) NOT NULL,

Password varchar(32) NOT NULL)

INSERT INTO users (UserName, Password)

VALUES ('Bob', 'mxyzptlk')

INSERT INTO users (UserName, Password)

VALUES ('Alice', 'nomalice')

CREATE PROCEDURE proc_IsUserValid

@UserName VarChar (32), @Password VarChar (32) AS

SELECT COUNT (*) FROM Users WHERE UserName = @UserName AND

CAST (Password AS VarBinary) = CAST (@Password AS VarBinary)

EXEC sp_addlogin 'SafeUser', 'safeuser', 'WebLogin'

EXEC sp_grantdbaccess 'SafeUser'

GRANT EXECUTE ON proc_IsUserValid TO SafeUser

Figure 4. This revised installation script creates the WebLogin database, the stored procedure used to validate usernames and passwords, and the SafeUser account used to execute the stored procedure.

Remember, it is up to you to stop SQL Injection attacks. Validating user input, using stored procedures, and using weak accounts that lack the permissions needed to harm a database build a three-ring fence around your data. In today's world, anything less is asking for trouble.

The sample code referenced in this article is available for download.

Jeff Prosise is author of several books, including Programming Microsoft .NET (Microsoft Press). He also is a co-founder of Wintellect (http://www.wintellect.com), a software consulting and education firm that specializes in .NET. Got a question for this column? Submit queries to [email protected].

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.

Comments

Plain text