In our last post we listed the 10 types of attacks that could potentially come against a web sites or app. The most prevalent type of attack is the injection. And the most common type of injection is the SQL Injection. This type of attack involves adding valid SQL keywords, symbols or command into data entry forms that permit users to perform database operations. These would include log in forms, send password by e-mail forms, search forms, comment forms, etc. By using this exploit, crafty intruders have been able to gain access to user data such as user names and e-mail addresses which can ultimately enable them to retrieve passwords. They also could discover information about the database itself such as table names, field names, number of records; they may learn enough information to delete information or add records such as a new user with a password.

To handle these attacks we need to follow several established guidelines:

  • Recognize that user input cannot be trusted
  • Avoid the use of dynamically created SQL statements
  • Use the lowest access level possible to the database
  • Handle SQL errors but do not disclose much information in error messages


First, working on the assumption that all user input is untrustworthy, we must be careful to validate all the text that is entered into a form. But every program’s validation needs will be different. One way to handle validation is to programmatically blacklist all prohibited characters. But this might be problematic since there will often be expectations that the application should allow. The better method of validation is to whitelist all acceptable input. This can be accomplished by defining regular expressions or patterns of text search strings. For instance the regular expression:

^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$

can be used to validate a basic e-mail address.

This expression can be used in C# with the Regex (Regular Expression) class:

Regex validate-email = new Regex("([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})");
// Find a match in the string
Match validation-result = validate-email.Match(user-email);
if (validation-result.Success)
  Process(user-email);
else
  DisplayErrorMessage();

And in PHP:

if (preg_match(“^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$”, $user-email))
  Process($user-email);
else
  DisplayErrorMessage();

More can be found on using regular expressions at regular-expressions.info

It also may be necessary to escape special characters if they are allowed. Escaping means to add a back slash or an escape character to characters with special significance such as single and double quote marks, back slashes, newline and carriage return symbols. There are functions available to add escape characters to strings such as MySQL’s mysql_real_escape_string() in PHP. In C# Regex.Escape() can be used.

And if a web page’s form uses hidden fields, these should also be validated since an intruder could alter the values in these fields.

Second, we should recognize that we should not dynamically create SQL statements since they are vulnerable to injections by intruders. We can instead use either prepared (parameterized) queries or stored procedures. For MySQL/PHP a parameterized query could be executed in this manner using MySQLi functions:

//prepare our query
$my-query = $mysqli->prepare("INSERT INTO Users (user-nane) VALUES (?)");
$my-query->bind_param("s", $user-entered-name);
$my-query->execute();
$my-query->close();

And in this manner in in C#:

SqlCommand command = new SqlCommand("select First-Name, Last-Name from Users where User-Name = @User", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@User";
param.Value = inputUser;
command.Parameters.Add(param);
db-output = command.ExecuteReader();

// write each record
while(db-output.Read())
{
  Console.WriteLine("{0}, {1}",
  db-output["First-Name"],
  db-output["Last-Name"]);
}
if (db-output!= null)
{
  db-output.Close();
}

Third, we should limit the access that we allow to the database by not giving admin type access rights to all the applications’ database accounts. Some accounts can be read only; others can be limited to certain database views, but having just one account with complete administrator access is risky.

Finally, we have to be careful about how we deal with errors and exceptions because these can become a source of information for the intruder. Also, proper error handling can limit the scope of an injection attack. Accordingly, our database errors messages should be cryptic and not disclose much detail about exact nature of an error or give away details about the database. Error codes should be displayed instead. Never should a message reveal that a field or table does not exist. And never should invalid input result in a ‘Server Error 500’. If invalid input reaches to the level of the server it means that the web page or application has inadequate error handling and is vulnerable to injection attacks.

Thus, by following established practices in validation, querying, limiting access and handling errors our web pages and applications can be made resistant to injection attacks.

For more on preventing SQL Injections see:
SQL Injection Attacks by Example
Stop SQL Injection Attacks Before They Stop You
Dynamic SQL & SQL injection