SQL Injection Attack : The Silent Assassin

SQL injection attack is a type of cyber attack that targets databases through malicious SQL code injection. By exploiting vulnerabilities in web applications that interact with databases, attackers can manipulate the SQL queries executed by the application, thereby gaining unauthorized access to sensitive data or even taking control of the entire database.

In the digital age, where data reigns supreme, protecting sensitive information has become paramount. From personal credentials to financial records, databases hold a treasure trove of valuable data. However, with great treasure comes great risk, and one of the most insidious threats to database security is SQL injection.

Understanding SQL Injection Attack

At its core, SQL injection takes advantage of poor input validation mechanisms in web applications. When a user inputs data into a form field on a website, such as a login page or a search bar, the application typically constructs an SQL query based on this input to retrieve or manipulate data in the database.

For example, consider a simple login form where users enter their username and password. The application might construct an SQL query like this:

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';

If the application fails to properly validate and sanitize user input, an attacker can inject malicious SQL code into the input fields. For instance, by entering ' OR 1=1 -- as the username and leaving the password field blank, the attacker can modify the query to always return true:

SELECT * FROM users WHERE username = '' OR 1=1 --' AND password = '';

In this altered query, the 1=1 condition is always true, effectively bypassing the authentication check and granting unauthorized access to the system.

Types of SQL Injection Attack

SQL injection attacks can be broadly classified into three main categories:

1. In-band SQL Injection (Classic SQLi): This is the most common type of SQL injection, where the attacker uses the same communication channel to both launch the attack and collect the results. In-band SQL injection can further be divided into:

• Error-based SQL Injection: Exploits error messages generated by the database to extract information about its structure and content.
• Union-based SQL Injection: Utilizes the SQL UNION operator to combine the results of two or more SELECT queries into a single result set, thus retrieving additional information from the database.

2.Blind SQL Injection: In this scenario, the attacker cannot directly see the result of the injected SQL query. Instead, they infer the results based on the application’s behavior, such as differences in response times or the presence of specific error messages.

3.Out-of-band SQL Injection: This type of attack occurs when the attacker is unable to use the same channel to both launch the attack and collect the results. Instead, they exploit vulnerabilities to trigger the database to initiate communication with an external system controlled by the attacker, allowing data exfiltration.

Mitigating SQL Injection

Preventing SQL injection requires a multi-layered approach involving secure coding practices, proper input validation, and ongoing vulnerability assessments. Here are some key strategies to mitigate the risk of SQL injection attacks:

1. Parameterized Queries: Instead of dynamically building SQL queries by concatenating strings, use parameterized queries or prepared statements. Parameterization separates the SQL logic from the data, preventing attackers from injecting malicious code.
2. Input Validation and Sanitization: Validate and sanitize all user input to ensure it conforms to expected formats and does not contain malicious code. Use whitelisting or input validation libraries to filter out potentially harmful characters.
3. Least Privilege Principle: Restrict database permissions and privileges to the minimum necessary for each user or application. This limits the potential damage an attacker can inflict if they successfully exploit a vulnerability.
4. Web Application Firewalls (WAF): Implement WAF solutions that can detect and block suspicious SQL injection attempts in real-time. WAFs can provide an additional layer of defense against known attack patterns.
5. Regular Security Audits: Conduct regular security audits and penetration testing to identify and remediate vulnerabilities in web applications and databases. This proactive approach helps uncover potential weaknesses before they can be exploited by attackers.

How to avoid SQL Injection

Preventing SQL injection requires a proactive and multi-layered approach, encompassing secure coding practices, input validation, access controls, and ongoing security testing. By adopting strategies and maintaining a strong security posture, organizations can significantly reduce the risk of SQL injection attacks and safeguard their sensitive data assets. Remember, security is an ongoing process, and staying vigilant is crucial in the ever-evolving threat landscape of cybersecurity.

Avoiding SQL injection requires a combination of secure coding practices, proper input validation, and ongoing vigilance. Here are some effective strategies to prevent SQL injection attacks:

1. Parameterized Queries:

• Use parameterized queries or prepared statements provided by your database framework or ORM (Object-Relational Mapping) library. Parameterization separates SQL logic from data, preventing attackers from injecting malicious code into SQL queries.

2. Input Validation and Sanitization:

• Validate and sanitize all user input to ensure it adheres to expected formats and does not contain malicious code. Employ whitelisting to only accept known safe characters or blacklisting to filter out potentially harmful characters.
• Avoid using dynamic SQL queries constructed by concatenating strings, as this can inadvertently create injection vulnerabilities.

3. Least Privilege Principle:

• Limit database permissions and privileges to the minimum necessary for each user or application. Avoid using privileged accounts for routine tasks, and restrict access to sensitive database operations and tables.

4. Secure Coding Practices:

• Follow secure coding guidelines and best practices recommended by reputable sources such as OWASP (Open Web Application Security Project). This includes validating input data, encoding output data, and using appropriate error handling mechanisms.
• Use stored procedures or parameterized functions whenever possible to encapsulate SQL logic and mitigate injection risks.

5. Web Application Firewalls (WAF):

• Implement a WAF solution that can detect and block suspicious SQL injection attempts in real-time. WAFs can provide an additional layer of defense against known attack patterns and help mitigate the impact of zero-day vulnerabilities.

6. Regular Security Audits and Testing:

• Conduct regular security audits, code reviews, and penetration testing to identify and remediate vulnerabilities in web applications and databases. Automated tools can help detect common SQL injection vulnerabilities, but manual testing is often necessary to uncover more complex issues.

7. Input and Output Encoding:

• Encode user-supplied input data before using it in SQL queries to prevent interpretation of special characters as SQL syntax. Output encoding is also important to prevent cross-site scripting (XSS) attacks that could be used to inject malicious scripts into web pages.

8. Update and Patch Management:

• Keep database management systems (DBMS), web servers, and application frameworks up to date with the latest security patches and updates. Vulnerabilities in software components can be exploited by attackers to launch SQL injection attacks.

Breach by Injection: Notorious SQL Injection Attacks Unveiled

SQL injection attacks have been responsible for numerous high-profile security breaches over the years. Here are a few notable examples:

1. TJX Companies Data Breach (2005-2007):

• In one of the largest data breaches in history, hackers exploited SQL injection vulnerabilities in the wireless networks of TJX Companies, which owns several major retail chains, including TJ Maxx and Marshalls. The attackers gained unauthorized access to the company’s systems and stole millions of credit and debit card numbers, as well as other personal information, affecting over 45 million customers.

2. Heartland Payment Systems Data Breach (2008-2009):

• Heartland Payment Systems, a major payment processing company, fell victim to a sophisticated SQL injection attack that resulted in the theft of payment card data from millions of transactions. The attackers installed malware on the company’s systems, allowing them to siphon off card data as it was being processed. The breach compromised data from over 100 million cards, making it one of the largest breaches of its kind.

3. Sony Pictures Entertainment Data Breach (2014):

• In 2014, hackers targeted Sony Pictures Entertainment’s network, exploiting SQL injection vulnerabilities,among other weaknesses. The attackers stole vast amounts of sensitive data, including employee personal information, internal emails, unreleased films, and confidential business documents. The breach caused significant disruption to Sony’s operations and led to widespread public embarrassment and financial losses for the company.

4. Equifax Data Breach (2017):

• Equifax, one of the largest credit reporting agencies in the United States, suffered a massive data breach in 2017 due to a vulnerability in Apache Struts, a popular open-source web application framework. The attackers exploited this vulnerability to gain unauthorized access to Equifax’s systems and execute SQL injection attacks. The breach exposed the personal information of approximately 147 million individuals, including names, social security numbers, birth dates, and addresses.

5. Adult FriendFinder Data Breach (2016):

• Adult FriendFinder, a popular adult dating and entertainment company, experienced a significant data breach in 2016. Hackers exploited a SQL injection vulnerability in the company’s website to gain access to a database containing sensitive information about millions of users, including usernames, email addresses, sexual preferences, and even plaintext passwords. The breach raised concerns about privacy and security on online dating platforms.

SQL Injection Prevention Cheat Sheet

This cheat sheet will help you prevent SQL injection flaws in your applications. It will define what SQL injection is, explain where those flaws occur, and provide four options for defending against SQL injection attacks. SQL Injection attacks are common because:

1. SQL Injection vulnerabilities are very common, and
2. The application’s database is a frequent target for attackers because it typically contains interesting/critical data.

Anatomy of A Typical SQL Injection Vulnerability¶

A common SQL injection flaw in Java is below. Because its unvalidated “customerName” parameter is simply appended to the query, an attacker can enter SQL code into that query and the application would take the attacker’s code and execute it on the database.

String query = "SELECT account_balance FROM user_data WHERE user_name = "
             + request.getParameter("customerName");
try {
    Statement statement = connection.createStatement( ... );
    ResultSet results = statement.executeQuery( query );
}

Primary Defenses

• Option 1: Use of Prepared Statements (with Parameterized Queries)
• Option 2: Use of Properly Constructed Stored Procedures
• Option 3: Allow-list Input Validation
• Option 4: STRONGLY DISCOURAGED: Escaping All User Supplied Input

Defense Option 1: Prepared Statements (with Parameterized Queries)¶

When developers are taught how to write database queries, they should be told to use prepared statements with variable binding (aka parameterized queries). Prepared statements are simple to write and easier to understand than dynamic queries and parameterized queries force the developer to define all SQL code first and pass in each parameter to the query later.

If database queries use this coding style, the database will always distinguish between code and data, regardless of what user input is supplied. Also, prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker.

Safe Java Prepared Statement Example¶

In the safe Java example below, if an attacker were to enter the userID of tom' or '1'='1, the parameterized query would look for a username which literally matched the entire string tom' or '1'='1. Thus, the database would be protected against injections of malicious SQL code.

The following code example uses a PreparedStatement, Java’s implementation of a parameterized query, to execute the same database query.

// 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( );

Safe C# .NET Prepared Statement Example¶

In .NET, the creation and execution of the query doesn’t change. Just pass the parameters to the query using the Parameters.Add() call as shown below.

String query = "SELECT account_balance FROM user_data WHERE user_name = ?";
try {
  OleDbCommand command = new OleDbCommand(query, connection);
  command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text));
  OleDbDataReader reader = command.ExecuteReader();
  // …
} catch (OleDbException se) {
  // error handling
}

While we have shown examples in Java and .NET, practically all other languages (including Cold Fusion and Classic ASP) support parameterized query interfaces. Even SQL abstraction layers, like the Hibernate Query Language (HQL) with the same type of injection problems (called HQL Injection) supports parameterized queries as well:

Hibernate Query Language (HQL) Prepared Statement (Named Parameters) Example¶

// This is an unsafe HQL statement
Query unsafeHQLQuery = session.createQuery("from Inventory where productID='"+userSuppliedParameter+"'");
// Here is a safe version of the same query using named parameters
Query safeHQLQuery = session.createQuery("from Inventory where productID=:productid");
safeHQLQuery.setParameter("productid", userSuppliedParameter);

Other Examples of Safe Prepared Statements

If you need examples of prepared queries/parameterized languages, including Ruby, PHP, Cold Fusion, Perl, and Rust, see the Query Parameterization Cheat Sheet or this site.

Generally, developers like prepared statements because all the SQL code stays within the application, which makes your application relatively database independent.

Defense Option 2: Stored Procedures

Though stored procedures are not always safe from SQL injection, developers can use certain standard stored procedure programming constructs. This approach has the same effect as the use of parameterized queries as long as the stored procedures are implemented safely (which is the norm for most stored procedure languages).

Safe Approach to Stored Procedures

If stored procedures are needed, the safest approach to using them requires the developer to build SQL statements with parameters that are automatically parameterized, unless the developer does something largely out of the norm. The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself, and then called from the application. Since prepared statements and safe stored procedures are equally effective in preventing SQL injection, your organization should choose the approach that makes the most sense for you.

When Stored Procedures Can Increase Risk

Occasionally, stored procedures can increase risk when a system is attacked. For example, on MS SQL Server, you have three main default roles: db_datareader, db_datawriter and db_owner. Before stored procedures came into use, DBAs would give db_datareader or db_datawriter rights to the webservice’s user, depending on the requirements.

However, stored procedures require execute rights, a role that is not available by default. In some setups where user management has been centralized, but is limited to those 3 roles, web apps would have to run as db_owner so stored procedures could work. Naturally, that means that if a server is breached the attacker has full rights to the database, where previously they might only have had read-access.

Safe Java Stored Procedure Example¶

The following code example uses Java’s implementation of the stored procedure interface (CallableStatement) to execute the same database query. The sp_getAccountBalance stored procedure has to be predefined in the database and use the same functionality as the query above.

// This should REALLY be validated
String custname = request.getParameter("customerName");
try {
  CallableStatement cs = connection.prepareCall("{call sp_getAccountBalance(?)}");
  cs.setString(1, custname);
  ResultSet results = cs.executeQuery();
  // … result set handling
} catch (SQLException se) {
  // … logging and error handling
}

Safe VB .NET Stored Procedure Example

The following code example uses a SqlCommand, .NET’s implementation of the stored procedure interface, to execute the same database query. The sp_getAccountBalance stored procedure must be predefined in the database and use the same functionality as the query defined above.

 Try
   Dim command As SqlCommand = new SqlCommand("sp_getAccountBalance", connection)
   command.CommandType = CommandType.StoredProcedure
   command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text))
   Dim reader As SqlDataReader = command.ExecuteReader()
   '...
 Catch se As SqlException
   'error handling
 End Try

Defense Option 3: Allow-list Input Validation

If you are faced with parts of SQL queries that can’t use bind variables, such as the names of tables or columns as well as the sort order indicator (ASC or DESC), input validation or query redesign is the most appropriate defense. When names of tables or columns are needed, ideally those values come from the code and not from user parameters.

Sample Of Safe Table Name Validation

WARNING: If user parameter values are used for targeting different table names and column names, this is a symptom of poor design and a full rewrite should be considered if time allows. If that is not possible, developers should map the parameter values to the legal/expected table or column names to make sure unvalidated user input doesn’t end up in the query.

In the example below, since tableName is identified as one of the legal and expected values for a table name in this query, it can be directly appended to the SQL query. Keep in mind that generic table validation functions can lead to data loss as table names are used in queries where they are not expected.

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

Safest Use Of Dynamic SQL Generation (DISCOURAGED)¶

When we say a stored procedure is “implemented safely,” that means it does not include any unsafe dynamic SQL generation. Developers do not usually generate dynamic SQL inside stored procedures. However, it can be done, but should be avoided.

If it can’t be avoided, the stored procedure must use input validation or proper escaping as described in this article to make sure that all user supplied input to the stored procedure can’t be used to inject SQL code into the dynamically generated query. Auditors should always look for uses of sp_execute, execute or exec within SQL Server stored procedures. Similar audit guidelines are necessary for similar functions for other vendors.

Sample of Safer Dynamic Query Generation (DISCOURAGED)¶

For something simple like a sort order, it would be best if the user supplied input is converted to a boolean, and then that boolean is used to select the safe value to append to the query. This is a very standard need in dynamic query creation.

For example:

public String someMethod(boolean sortOrder) {
 String SQLquery = "some SQL ... order by Salary " + (sortOrder ? "ASC" : "DESC");`
 ...

Any time user input can be converted to a non-String, like a date, numeric, boolean, enumerated type, etc. before it is appended to a query, or used to select a value to append to the query, this ensures it is safe to do so.

Input validation is also recommended as a secondary defense in ALL cases, even when using bind variables as discussed earlier in this article. More techniques on how to implement strong input validation is described in the Input Validation Cheat Sheet.

Defense Option 4: STRONGLY DISCOURAGED: Escaping All User-Supplied Input¶

In this approach, the developer will escape all user input before putting it in a query. It is very database specific in its implementation. This methodology is frail compared to other defenses and we CANNOT guarantee that this option will prevent all SQL injections in all situations.

If an application is built from scratch or requires low risk tolerance, it should be built or re-written using parameterized queries, stored procedures, or some kind of Object Relational Mapper (ORM) that builds your queries for you.

Additional Defenses

Beyond adopting one of the four primary defenses, we also recommend adopting all of these additional defenses in order to provide defense in depth. These additional defenses are:

• Least Privilege
• Allow-list Input Validation

Least Privilege¶

To minimize the potential damage of a successful SQL injection attack, you should minimize the privileges assigned to every database account in your environment. Start from the ground up to determine what access rights your application accounts require, rather than trying to figure out what access rights you need to take away.

Make sure that accounts that only need read access are only granted read access to the tables they need access to. DO NOT ASSIGN DBA OR ADMIN TYPE ACCESS TO YOUR APPLICATION ACCOUNTS. We understand that this is easy, and everything just “works” when you do it this way, but it is very dangerous.

Minimizing Application and OS Privileges¶

SQL injection is not the only threat to your database data. Attackers can simply change the parameter values from one of the legal values they are presented with, to a value that is unauthorized for them, but the application itself might be authorized to access. As such, minimizing the privileges granted to your application will reduce the likelihood of such unauthorized access attempts, even when an attacker is not trying to use SQL injection as part of their exploit.

While you are at it, you should minimize the privileges of the operating system account that the DBMS runs under. Don’t run your DBMS as root or system! Most DBMSs run out of the box with a very powerful system account. For example, MySQL runs as system on Windows by default! Change the DBMS’s OS account to something more appropriate, with restricted privileges.

Details Of Least Privilege When Developing¶

If an account only needs access to portions of a table, consider creating a view that limits access to that portion of the data and assigning the account access to the view instead, rather than the underlying table. Rarely, if ever, grant create or delete access to database accounts.

If you adopt a policy where you use stored procedures everywhere, and don’t allow application accounts to directly execute their own queries, then restrict those accounts to only be able to execute the stored procedures they need. Don’t grant them any rights directly to the tables in the database.

Least Admin Privileges For Multiple DBs¶

The designers of web applications should avoid using the same owner/admin account in the web applications to connect to the database. Different DB users should be used for different web applications.

In general, each separate web application that requires access to the database should have a designated database user account that the application will use to connect to the DB. That way, the designer of the application can have good granularity in the access control, thus reducing the privileges as much as possible. Each DB user will then have select access to what it needs only, and write-access as needed.

As an example, a login page requires read access to the username and password fields of a table, but no write access of any form (no insert, update, or delete). However, the sign-up page certainly requires insert privilege to that table; this restriction can only be enforced if these web apps use different DB users to connect to the database.

Enhancing Least Privilege with SQL Views¶

You can use SQL views to further increase the granularity of access by limiting the read access to specific fields of a table or joins of tables. It could have additional benefits.

For example, if the system is required (perhaps due to some specific legal requirements) to store the passwords of the users, instead of salted-hashed passwords, the designer could use views to compensate for this limitation. They could revoke all access to the table (from all DB users except the owner/admin) and create a view that outputs the hash of the password field and not the field itself.

Any SQL injection attack that succeeds in stealing DB information will be restricted to stealing the hash of the passwords (could even be a keyed hash), since no DB user for any of the web applications has access to the table itself.

Allow-list Input Validation¶

In addition to being a primary defense when nothing else is possible (e.g., when a bind variable isn’t legal), input validation can also be a secondary defense used to detect unauthorized input before it is passed to the SQL query. For more information please see the Input Validation Cheat Sheet. Proceed with caution here. Validated data is not necessarily safe to insert into SQL queries via string building.

Conclusion

SQL injection attacks remain a prevalent and dangerous threat to database security, capable of causing significant damage to organizations and individuals alike. By understanding how SQL injection attacks work and implementing effective countermeasures, businesses can better protect their valuable data assets from malicious exploitation.

However, it’s essential to recognize that cybersecurity is an ongoing battle, and vigilance is key. As attackers continually evolve their tactics, defenders must remain one step ahead by adopting best practices, staying informed about emerging threats, and investing in robust security measures.

In the ever-changing landscape of cybersecurity, knowledge and preparedness are our greatest weapons against the silent assassin that is SQL injection.

Thank you for taking the time to read my thoughts. Your engagement means the world to me. Until next time, keep exploring and stay curious!

Also read Best Cyber Security Practices And Precautions

Follow the official LinkedIn profile for more future updates, Nikhil Singh.