Sunday, February 19, 2012

What is SQL Injection in ASP.NET?

SQL injection can occur when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Conventional security measures, such as the use of SSL and IPSec, do not protect your application from SQL injection attacks. Successful SQL injection attacks enable malicious users to execute commands in an application's database.

Countermeasures include using a list of acceptable characters to constrain input, using parameterized SQL for data access, and using a least privileged account that has restricted permissions in the database. Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe. Type-safe SQL parameters can also be used with dynamic SQL. In situations where parameterized SQL cannot be used, consider using character escaping techniques.

Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:
    * Weak input validation.
    * Dynamic construction of SQL statements without the use of type-safe parameters.
    * Use of over-privileged database logins.

SQL Injection Example

Consider what happens when a user types the following string in the SSN text box, which is expecting a Social Security number of the form nnn-nn-nnnn. Otherwise, a user put the same string in query string in url that is expecting a user account.

' ; DROP DATABASE pubs  --
Using the input, the application executes the following dynamic SQL statement or stored procedure, which internally executes a similar SQL statement.

// Use dynamic SQL
SqlDataAdapter myCommand = new SqlDataAdapter(
          "SELECT au_lname, au_fname FROM authors WHERE au_id = '" +
          SSN.Text + "'", myConnection);

// Use stored procedures
SqlDataAdapter myCommand = new SqlDataAdapter(
                                "LoginStoredProcedure '" +
                                 SSN.Text + "'", myConnection);
The developer's intention was that when the code runs, it inserts the user's input and generates a SQL the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'

However, the code inserts the user's malicious input and generates the following query.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''; DROP DATABASE pubs --'

In this case, the ' (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. It closes the current statement only if the following parsed token does not make sense as a continuation of the current statement but does make sense as the start of a new statement. As a result, the opening single quotation mark character of the rogue input results in the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''
The; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code.

 Note : The semicolon is not necessarily required to separate SQL statements. This is dependent on vendor or implementation, but Microsoft SQL Server does not require them. For example, SQL Server parses the following as two separate statements:


Finally, the -- (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ' (single quotation mark) character, which would otherwise cause a SQL parser error.


How's easy to be a hacker. Just kidding! Please keep in mind that you need to validate all untrusted input to your application and you should assume that any input from users is malicious including form fields, query strings, client-side cookies, and browser environment values such as user agent strings and IP addresses.

Reference :

1 comment: