In this article we focus on providing clear and functional advice on avoiding SQL Injection defects in your applications. Unfortunately, SQL Injection attacks are common and the reasons for it are the following two factors:
- Predominance of SQL Injection vulnerabilities, and
- The vulnerability of the target, as the database will have all the crucial and interesting data for your application.
It is disappointing that so many SQL Injection attacks are becoming successful, as it is really simple to avert these vulnerabilities in your code.
SQL Injection glitches are introduced when developers build effective database inquiries that incorporate user supplied input. It is quite simple to prevent SQL injection flaws. Developers can either:
a) Prevent input supplied by users, which contains malicious SQL that affects the executed query’s logic.
b) Stop writing queries that are dynamic.
This article provides techniques that can be used with any kind of programming language on any kind of database.
Parameterized Queries or Prepared Statements
All developers should be initially taught on how to use prepared statements (or parameterized queries) and write database queries, as they are easier to understand and even simpler to write than dynamic queries. Parameterized queries make the developer define SQL code and then pass every parameter of the query later.
Prepared statements guarantee that attackers are not able to alter the intent of a query, even if the attacker inserts SQL commands.
Stored procedures have similar effect to prepared statements when enforced safely. Here developers are first required to define the SQL code and after that pass in the parameters. The distinction between stored procedures and prepared statements is that for a stored procedure, the SQL code is defined as well as stored in the database itself and after that is called from the application.
For companies that are already using stored procedures exclusively, the chances of SQL injection flaws are low. But even then, you have to be careful about stored procedures, as it is possible to build a dynamic query inside a stored procedure, which has been subjected to SQL injection.
Escaping Every User Supplied Input
This technique involves escaping user input prior to putting it in a query. If you are worried that rewriting your dynamic queries like prepared statements or stored procedures may adversely affect the performance of your application, or may even break your application, then this approach may be the best for you. Still, this methodology is weak as compared to parameterized queries and it cannot be guaranteed that all SQL injections will be prevented, in all situations.
Beyond these primary defenses, additional defenses can also be adopted, in order to increase the defense in depth. These additional defenses are – Least Privilege and White List Input Validation.