SQL Injection

11/22/2020

What Year is this?

xkcd

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. By todays standards and with the many readily available options for communicating with a database it is simply astounding why application are still falling prey to the most basic of SQL Injection attacks. The simplest way to avoid SQL Injection attacks is to NEVER TRUST USER INPUT.

String Concatenation is the enemy never should developers allow any form of code or request like the statement below

function FindById(id){
  // 😱🤢🤮
  let query = "SELECT * FROM users WHERE id = " + id
  db.execute(query)
}

Allowing a query to be executed as above allows a user to provide an id which the SQL Injection Attacker could submit an id of 0; DROP DATABASE; or any other number of attacks… Developers, testers, security professionals should all know the user is the real enemy and nothing sent by the user should ever be trusted blindly. All queries must be sanitized properly an never blindly executed.

SQL Injection attack prevention

There are number of methods for reducing the risk of a data breach due to SQL injection. As a best practice, several of the strategies below can and should be used in conjunction with one another.

• Use an Object Relational Mapper (ORM) 💯
  • Most ORM libraries will sanitize database inputs automatically.
  • There is an ORM for every language and database.
  • Pay attention to your ORM and database vulnerability notifications and update as needed.
• Use of Prepared Statements (with Parameterized Queries)
  • This method of sanitizing database inputs involves forcing the developers to first define all the SQL code, and then to pass only specific parameters to the SQL query;
  • data entered is explicitly given a limited scope that it can not expand beyond.
• Escape All User Supplied Input
  • When writing SQL, specific characters or words have particular meaning. For example, the * character means any and the words OR is a conditional.
  • Either accidentally or maliciously entered there are unsafe characters so always explicitly escape user input to prevent the database from processing it as a command.
• Enforce Least Privilege
  • As a general rule, in all instances where a website needs to use dynamic SQL, do not allow your application to connect to the database as the Database Administrator
  • Use a limited database user account to the narrowest scope required to execute relevant queries.