Injection Attacks: The Complete 2020 Guide

SQLi Defense - Application Layer

Since SQL injections happen when user-supplied input is injected in dynamic database queries, preventing them comes down to 2 rules:

  1. Stop writing dynamic queries
     
  2. Prevent user-supplied input containing malicious SQL from affecting the logic of the executed query

Use Prepared Statements (with parameterized queries)

 

  1. This coding style helps distinguish between code and data
     
  2. Prepared statements define the intent of the query, preventing injected SQL commands from changing that intent

SELECT * FROM Users WHERE email ='' AND password='';



SELECT * FROM Users WHERE email ='' AND password='';



' or 1=1; --




SELECT * FROM Users WHERE email ='\' or 1=1; --' AND password = 'dfgdfkjgn';




// This should REALLY be validated too
String custname = request.getParameter("customerName");

// Perform input validation to detect attacks
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";

PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custname);

ResultSet results = pstmt.executeQuery( );

Example Java Prepared Statement:

Source: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

Use Stored Procedures

Stored procedures are pre-prepared SQL queries that are saved in the database, and the application calls them when needed.

Use Stored Procedures

If you need to use dynamic input in stored procs, make sure you properly validate and escape the input.

Use Stored Procedures

One of the issues with stored procedures is that they require execute rights to run. If there's a compromise, the attacker could have full rights to the database instead of just having read access.

Example of Stored Procedures



DELIMITER //

CREATE PROCEDURE GetAllProducts()
BEGIN
	SELECT *  FROM products;
END //

DELIMITER ;

Use Whitelist input validation

Whitelists define what inputs are authorized.

 

While not always possible to use, they can help define boundaries of what inputs can be.

Example of Whitelisting




String tableName;
switch(PARAM):
  case "Value1": tableName = "fooTable";
                 break;
  case "Value2": tableName = "barTable";
                 break;
  ...
  default      : throw new InputValidationException("unexpected value provided"
                                                  + " for table name");

Use Whitelist input validation

Whitelists are preferred to blacklists because blacklists can be circumvented but they can also block legitimate values.

 

Even if not used as a primary line of defense, whitelists can be used as a secondary line of defense (ie: in addition to prepared statements).

Escape all user-supplied input

Regarded as a last resort because it cannot guarantee to prevent all SQL injections in all situations.

 

Can be useful for legacy applications where implement input validation wouldn't be cost-effective.

 

Input validation

Regardless of the methods used to secure database queries, we should always aim to implement proper input validation.

 

Example: if you are expecting an email address as an input, validate that it looks like one.

 

Don't forget to validate in the back-end.

 

Input validation

More on input validation here:

https://cheatsheetseries.owasp.org/cheatsheets/Input_Validation_Cheat_Sheet.html

Alternative prepared statement for the OWASP Juice Shop



const email = req.body.email,
      password = insecurity.hash(req.body.password)
let preparedStatement = new sql.PreparedStatement(),
    sqlQuery = "SELECT * FROM Users WHERE (email = @email and password = @password) AND deletedAt IS NULL"
  
    preparedStatement.input('email', sqlVarChar(50))
    preparedStatement.input('password', sqlVarChar(50))
    preparedStatement.prepare(sqlQuery)
    .then(function() { ... }

How to test your apps

  1. Understand and document where the application interacts with a database server to access data, ie:
    1. Authentication form
       
    2. Search form
       
    3. Profile pages
       
    4. Etc...

How to test your apps

2. Make a list of all input fields whose values could be used to craft an SQL injection

  1. Remember hidden fields
     
  2. Look at HTTP headers
     
  3. Consider Cookies

How to test your apps

3. Test, test, test:

  1. Manually
     
  2. With fuzzers
     
  3. With automated tools

Abuse Cases

Abuse Cases

Should be:

  • Specific
  • Actionable

Abuse Cases

In an Agile project, define the Abuse Cases after your User Stories are included in a Sprint

Abuse Cases

In a Waterfall project, define the Abuse Cases after the business features to implement are identified and known by the business

Abuse Cases

Then, the Abuse Cases become security requirements in each feature specification section or User Story acceptance criteria

Example Abuse Case

As an attacker, I will perform an injection attack (SQL, LDAP, XPath, or NoSQL queries, OS commands, XML parsers, SMTP headers, expression languages, and ORM queries) against input fields of the User or API interfaces

For more on Abuse Cases:

https://cheatsheetseries.owasp.org/cheatsheets/Abuse_Case_Cheat_Sheet.html

SQLi Defense - Application Layer

By Christophe Limpalair

Made with Slides.com

SQLi Defense - Application Layer

  • 535

More from Christophe Limpalair