SQL Injection

SQL Injection

SQL injection is a type of injection attack. Injection attacks occur when maliciously crafted inputs are entered by an attacker, causing an application to perform an unintentional action. Because of the omnipresence of SQL databases, SQL injection is one of the most general types of attacks on the internet.

Risks

• Prevalence: Occasional
• Exploitability: Easy
• Impact: Devastating

Effects SQL injection attack

SQL Injection can allow to side-step the login page: a huge security flaw for a banking site. More complicated attacks will allow an attacker to run random statements on the database. In the past, hackers have used injection attacks to:

• Extract restricted info, like Social Security numbers, or credit card details.
• List the authentication details of users, so these logins can be used in attacks on other sites.
• Erase data or drop tables, corrupting the database, and making the website unusable.
• Add further malicious code to be executed when users visit the site.

SQL injection attacks are astoundingly common. Major enterprises like Yahoo and Sony have had their products compromised. In other cases, hacker groups targeted specific functions or wrote scripts planned to gather authentication information.

Protection

Parameterized Statements

Programming languages talk to SQL databases using database drivers. A driver allows an application to construct and run SQL statements against a database, extracting and manipulating data as needed. Parameterized statements make sure that the parameters (i.e. inputs) passed into SQL statements are treated safely.

A secure way of running a SQL query in JDBC using a parameterized statement would be:

    // Connect to the database.
    Connection conn = DriverManager.getConnection(URL, USER, PASS);

    // Construct the SQL statement we want to run, specifying the parameter.
    String sql = "SELECT * FROM users WHERE email = ?";

    // Generate a prepared statement with the placeholder parameter.
    PreparedStatement stmt = conn.prepareStatement(sql);

    // Bind email value into the statement at parameter index 1.
    stmt.setString(1, email);

    // Run the query...
    ResultSet results = stmt.executeQuery(SQL);

    while (results.next())
    {
        // ...do something with the data returned.
    }

Contrast this to an explicit construction of the SQL string, which is very, very dangerous:

    // The user we want to find.
    String email = "suyash@debug.school.com";

    // Connect to the database.
    Connection conn = DriverManager.getConnection(URL, USER, PASS);
    Statement stmt = conn.createStatement();

    // Bad, bad news! Don't construct the query with string concatenation.
    String sql = "SELECT * FROM users WHERE email = '" + email + "'";

    // I have a bad feeling about this...
    ResultSet results = stmt.executeQuery(sql);

    while (results.next()) {
      // ...hacking complete.
    }

The key difference is the data being passed to the executeQuery(...) method. In the first case, the parameterized string and the parameters are passed to the database separately, which allows the driver to correctly interpret them. In the second case, the full SQL statement is constructed before the driver is invoked, meaning we are vulnerable to maliciously crafted parameters.

You should always use parameterized statements where available, they are your number one protection against SQL injection.

More examples below.

Object Relational Mapping

Many development teams prefer to use Object Relational Mapping (ORM) frameworks to translate SQL result sets into code objects more seamlessly. ORM tools often mean developers will rarely have to write SQL statements in their code – and these tools thankfully use parameterized statements under the hood.

The most well-known ORM is probably Ruby on Rails’ Active Record framework. Fetching data from the database using Active Record looks like this:

    def current_user(email)
      # The 'User' object is an Active Record object, that has found methods 
      # auto-magically generated by Rails.
      User.find_by_email(email)
    end

Code like this is safe from SQL Injection attacks.

Using an ORM does not automatically make you immune to SQL injection, however. Many ORM frameworks allow you to construct SQL statements, or fragments of SQL statements when more complex operations need to be performed on the database. For example, the following Ruby code is vulnerable to injection attacks:

    def current_user(email)
      # This code would be vulnerable to a maliciously crafted email parameter.
      User.where("email = '" + email + "'")
    end

Note: If you find yourself writing SQL statements by concatenating strings, think very carefully about what you are doing.

Escaping Inputs

If you are unable to use parameterized statements or a library that writes SQL for you, the next best approach is to ensure proper escaping of special string characters in input parameters.

Injection attacks often rely on the attacker being able to craft an input that will prematurely close the argument string in which they appear in the SQL statement. This is why you will often see ' or " characters in attempted SQL injection attacks.

Programming languages have standard ways to describe strings containing quotes within them – SQL is no different in this respect. Typically, doubling up the quote character – replacing ' with '' – means “treat this quote as part of the string, not the end of the string”.

Escaping symbol characters is a simple way to protect against most SQL injection attacks, and many languages have standard functions to achieve this. There are a couple of drawbacks to this approach, however:

• You need to be very careful to escape characters everywhere in your codebase where an SQL statement is constructed.
• Not all injection attacks rely on the abuse of quote characters. When a numeric ID is expected in a SQL statement, quote characters are not required. The following code is still vulnerable to injection attacks, no matter how much you play around with quote characters:

    def current_user(id)
      User.where("id = " + id)
    end

Sanitizing Inputs

Sanitizing inputs is a good practice for all applications. In the example, the user supplied a password as ' or 1=1--, which looks pretty suspicious as a password choice.

Developers should always make an effort to reject inputs that look suspicious out of hand, while taking care not to accidentally punish legitimate users. For instance, your application may clean parameters supplied in GET and POST requests in the following ways:

• Check that supplied fields like email addresses match a regular expression.
• Ensure that numeric or alphanumeric fields do not contain symbol characters.
• Reject (or strip) out whitespace and newline characters where they are not appropriate.

Client-side validation (i.e. in JavaScript) is useful for giving the user immediate feedback when filling out a form, but is no defense against a serious hacker. Most hack attempts are performed using scripts, rather than the browser itself.

More considerations

Principle of Least Privilege

Applications should ensure that each process or software component can access and affect only the resources it needs. Apply “levels of clearance” as appropriate, in the same way that only certain bank employees have access to the vault. Applying restricted privileges can help mitigate a lot of the risk around injection attacks.

It is rarely necessary for applications to change the structure of the database at run-time – typically tables are created, dropped, and modified during release windows, with temporarily elevated permissions. Therefore, it is good practice to reduce the permissions of the application at runtime, so it can at most edit data, but not change table structures. In a SQL database, this means making sure your production accounts can only execute DML statements, not DDL statements.

With complex database designs, it can be worth making these permissions even more fine-grained. Many processes can be permissioned to perform data edits only through stored procedures or to execute with read-only permissions.

Sensibly designing access management in this way can provide a vital second line of defense. No matter how the attacker gets access to your system, it can mitigate the type of damage they can do.

Password Hashing

The example above relied on the fact that the password was stored as plain text in the database. Storing unencrypted passwords is a major security flaw in itself. Applications should store user passwords as strong, one-way hashes, preferably salted. This mitigates the risk of malicious users stealing credentials, or impersonating other users.

Third Party Authentication

As a final note, it is often worth considering outsourcing the authentication workflow of your application entirely. Facebook, Twitter, and Google all provide mature OAuth APIs, which can be used to let users log into your website using their existing accounts on those systems. This saves you as an application developer from rolling your authentication and assures your users that their passwords are only stored in a single location.

Ref: https://www.hacksplaining.com/prevention/sql-injection