SQL Injection and Other Web Login Issues
SQL injection allows external hackers of a web application to exploit private or public databases through manipulation of the user interface, especially during login. Like many programming languages, SQL Server’s Transact-SQL (T-QSL) allows multiple command statements on a single line separated by semicolons. SQL injection further takes advantage of the way SQL handles single quote string delimiters and double-dash comment prefixes. Other hacker injection techniques might involved malformed SQL statements to bypass database security, and wreak havoc on valuable information assets.
To illustrate the SQL injection scenario, a typical login screen usually requests a user name and a password to gain access to secure areas of a web application. If a hacker surfs the public areas of an Internet website, he or she might find the name of a high-ranking member of that organization in an employee contact page. The hacker then might go to the login area for external users with private area database accounts and enter variations of the person’s name.
The login page might look like the following:
The user would typically enter a user name and password, and the web application fetches the database row that coincides with the user name entered in the form textbox control. A program method compares the password entered against the password stored in the database. If a match is made, the web application method returns a Boolean true, and the IIS server sends the browser an authorized HTML page for viewing or altering sensitive information.
Perhaps the worst method that a novice programmer creates for the login click event in the above form code-behind file is a concatenated SQL string that allows the hacker to inject malicious code into the user name textbox.
private void btnSubmitLogin_Click(object sender, System.EventArgs e)
{
string strConnection = "server=localhost;database=northwind;uid=sa;pwd=;";
SqlConnection connection = new SqlConnection(strConnection);
connection.Open();
string strSQL = "SELECT * FROM Users WHERE UserName='" + txtUserName.Text + "' AND Password='" + txtPassword.Text + "'";
int intReturnRowCount;
SqlCommand cmdSQL = new SqlCommand(strSQL, connection);
intReturnRowCount = (int) cmdSQL.ExecuteScalar();
if (intReturnRowCount > 0)
{
Server.Transfer("SensitivePage.aspx");
}
else
{
lblMessage.Text = "Login incorrect. Please try again.";
}
connection.Close();
}
Instead of entering the user name and password as above, the hacker enters OR 1 = 1--‘. The SQL string that goes to the database stored procedure compiler in this case is:
SELECT * FROM UserTable WHERE UserName = ‘’ OR 1 = 1 -- AND Password = ‘’
The database engine executes the malicious concatenated string, and ignores the “AND Password = ‘’ “ since it has become a SQL comment. The “UserName = ‘’ “ is an invalid expression in the WHERE clause, but the “OR 1 = 1” portion evaluates to a Boolean true. The resultset returned from the “SELECT * “ SQL query includes all the rows from the Users table because of the true condition in the WHERE clause.
The moral is: do not use imbedded and dynamically-built SQL strings for login; instead, use stored procedures approved by a DBA. Furthermore, the application login logic should count the number of tries that an intruder attempts to enter a password, and lock the intruder out after a certain number of tries with that user name.
Another good programming login technique is to validate the login strings at the HTML page level and in a business logic layer against a regular expression that also removes any unwanted password characters without notifying the user. The code to check for bad characters against a regular expression could be:
public static string CleanStringRegex(string inputText)
{
//*********************************************************************
//
///
/// Validates the input text using a Regular Expression and replaces any input expression
/// characters with empty string.Removes any characters not in [a-z A-Z 0-9_].
///
///
The text to validate.
///
Sanitized string
//
//*********************************************************************
if (inputText != null && inputText.Length != 0)
{
RegexOptions options = RegexOptions.IgnoreCase;
return AppBase.BLL.ReplaceRegex(inputText, @"[^\\.!?""',\-\w\s@]", options);
}
else
{
throw new ArgumentException("Parameter may not be null or empty.");
}
}
The code above does not inform the intruder that any unwanted characters were removed, but if the malicious characters are present, the offending character is sent to the ReplaceRegex method that will substitute a empty string instead. The ReplaceRegex method would be:
private static string ReplaceRegex(string inputText, string regularExpression, RegexOptions options)
{
//*********************************************************************
//
///
/// Removes designated characters from an input string input text using a Regular Expression.
///
///
/// For a good reference on Regular Expressions, please see
/// - http://regexlib.com
/// - http://py-howto.sourceforge.net/regex/regex.html
///
///
The text to clean.
///
The regular expression
///
Sanitized string.
//
//*********************************************************************
if (inputText != null && inputText.Length != 0)
{
Regex regex = new Regex(regularExpression, options);
return regex.Replace(inputText, "");
}
else
{
throw new ArgumentException("Parameters may not be null or empty.");
}
}
The method above returns the sanitized string without informing the intruding hacker. The thrown exception in both the methods above should not be sent back to the user as well; instead, it should be handled at the server code-behind page, and the user should see a friendly, but less informative prompt. Number of tries lockout should still occur, however.
ASP.NET JavaScript validators can also require more than empty strings and detect bad characters as well. These validators should not be used alone, however, since the JavaScript can be seen by the hacker by right clicking on the HTML page, and viewing the code in a text reader like Note Pad.
The best programming practice is to use a stored procedure that accepts the username and password as parameters in lieu of the concatenated SQL string. Server-side application methods in the business logic layer would validate the user and return a success or failure to a calling method from the page code-behind that is also on the server. The stored procedure adds a layer of abstraction that keeps the hacker away from database tables that have no ASP.NET permissions granted by the DBA.