SQL and SQL Injection
Structured Query Language (SQL) is the language used to interact with databases that are used in the back end of web applications. With the use of queries, relevant data are retrieved, processed and stored in databases by programmers, database administrators etc. What is SQL Injection (SQL injection)? And how can it be prevented in the year 2020? SQLs are constructed to manipulate data within a database that includes a number of tables. Below is a simple query statement.
Basic format of syntax:
SELECT < Column List > FROM < table > WHERE < Condition >;
SELECT name, description FROM Products WHERE id=9;
Here, the statement is going to pick up the values for name and description records in a given table which meets the condition defined. Further, the record also has an ID that is 9. Moreover, it is also possible to select unique values from the database as shown below:
SELECT 22, 'string', 0x12, 'any other string';
However, to perform any function on databases, the web application should fulfill the following requisites:
- Connection to database - This is handled by connectors (Middleware between application and database).
- Submission of SQL statements to database.
- Retrieve results.
- User Credentials
- Credit card information
- Data stored by the web application.
- Transaction data.
- In-Band - Attacker tries to retrieve information from the respective web application.
- Error-Based - Attacker uses error messages to retrieve information such as the type of the database engine.
- Blind SQL - As the name suggests, this attack does not reflect any results, the attacker needs to find out different methods to exploit. These methods are mostly carried out by considering True or False conditions.
Attack Types
SQL injection is one of the attack types used in accessing these databases through a malicious party by inputting data to take control of SQL queries made by web applications. Attackers with sound knowledge in querying have the advantage of accessing data or executing commands within a short time when creating attacks on target databases. Attackers who are able to gain access to these databases have access to plenty of data elements such as:
Though various security measures were introduced for SQL injection attacks that were initiated by attackers decades ago, SQL injection still remains amongst top 10 vulnerabilities in the OWASP top 10 report due to its extensible crimes nature. SQL Injection (SQL injection) attackers use different tools to automate the process of executing commands. This consumes lesser time when compared with executing queries individually.
There are plenty of SQL injection types being discussed in the industry. These different types of SQL injections are used by attackers depending on their requirements (target achievements). These attack types vary from bypassing authentication forms to commanding operating systems by execution. Some of the major attack types include:
The following code snippet shows connection to a MySQL database and execution of a query.
Below code snippet shows the static query example in a PHP page.
$Connection – Defines object referencing the connection to the database.
$Query – SQL query that is to be executed
MySQL injection_query () – Defines a function that is going to submit query to the DB
Display_results () – Retrieve results
It should be noted in most cases that queries are dynamic and not static and that static queries can be changed by attackers. Below is how the above static query changed into a dynamic query.
Figure 2 - Vulnerable dynamic queriesAs per the above example, the user is trying to build a dynamic query by submitting user input to the ‘ID’ parameter. In the attackers’ perspective this is an advantageous task as he is able to construct an advanced query to exploit the backend database of the web application as shown below.
Input - ‘’ OR ‘a’ = ‘a’;
Query constructed
Figure 3 - Boolean SQL injection
By submitting the above query to the web application, record selection would be done on two conditions from the database. The ID must be empty (‘’) OR an always “True” condition (‘a’ = ‘a’). When the first condition is not fulfilled, the database would look out for the second condition, which is the OR Statement. The condition here makes the database to display all the records as this submitted condition always gives ‘True’ values.
When looking for SQL injection points, one of the most widely used techniques is to investigate the particular web application with different input parameters that can cause errors. These input parameters are carried by few method types such as GET, POST requests, HEADERS and COOKIES.
Below example depicts how a parameter ID that is equal to one is sent with GET request. This makes the application behave correctly and provides a relevant record.
However, submitting the parameter with a comma makes the application behave in an unexpected manner. Therefore, returning as an error as shown below.
DBMS Responses generated for various incorrect SQL statements are different from one another depending on the error. In the above example mysql_fetch_assoc() functions’ output error occurred due to invalid input. Moreover, the ‘UNION’ statement in SQL combines results of two or more selected statements. Below is the format of a simple UNION statement:
Figure 6 - : UNION statement formatThe highlighted Section ID is a vulnerable point prone to an injection. Here, the attacker can use a UNION statement to craft a payload to retrieve information associated with credit cards. Below is the elaboration:
Above shown payload will make a query in the web application to transform as shown below:
Moreover, payload can be submitted to a web application using GET request method with the use of a browser or via a third party application.
Furthermore, attackers use comments in SQL queries to exploit injection flaws. Additionally, attackers use database enumeration to conduct advanced SQL injections.
The Impacts of SQL Injection
As this is one of the broader vulnerability categories, the impacts vary from one to another. With the above facts taken into consideration, attackers generate SQLs for various purposes with different targets. The impacts of these types of attacks on businesses are extensive. Leakage of sensitive information, unauthorized data modification, losing data, losing control of servers, Issues on confidentiality, and data integrity are some of the major negative impacts that a company could go through after a SQL injection. In the worst case scenario, the entire system can be taken control of by the attackers through this attack.
Due to the high negative impacts that SQL injections could cause as discussed above, it has remained one of the top security vulnerabilities in software development culture. Most of the companies have experienced SQL injection attacks over the past couple of years (65% to be precise), based on the survey conducted by Ponemon in 2018.
Countermeasures for SQL Injection
As elaborated above, SQL injection vulnerabilities are input validation vulnerabilities which can be prevented by enforcing input validations. Use of parameterized queries to construct prepared statements, use of stored procedures, escaping user-supplied inputs, whitelisting input validations and enforcing least privileges are some of them. Further use of the latest development platforms provides a stronger foundation than the older versions in the development environments. Additionally, frequent scans are recommended with the help of verified protection mechanisms that are inbuilt.
When it comes to OWASP vulnerability mitigation, there are several measures that could be taken by programmers especially when coding. Going through the various risks identified in the OWASP top 10 vulnerabilities, the very first risk that is listed is injections. Injections can simply be prevented by avoiding access to external interpreters; where predefined language specific libraries could be used to shell commands and system calls. Furthermore, another strong mechanism that can be implemented for these type of attacks is by providing only the required privileges needed absolutely to perform tasks within the running environment.
What Can Be Done To Stop OWASP Top 10 Vulnerabilities?
Based on the OWASP top 10 security vulnerabilities report (OWASP Top 10 Vulnerabilities, 2020), the top 10 identified risks are Injections (SQL, LDAP and NoSQL), Broken Authentication, Exposure of Sensitive Data, XML External Entities, Broken Access Control, Security misconfiguration, Cross-site Scripting (XSS), Insecure Deserialization, Using Components with Known Vulnerabilities, and Insufficient Logging & Monitoring (OWASP Top Ten Web Application Security Risks | OWASP, 2020). Various countermeasures are taken to prevent the exploitation of mentioned vulnerabilities. These countermeasures include, detective, preventive and corrective countermeasures.
Preventive measures are taken earlier in order to thwart incidents that could possibly happen. Firewalls can be implemented as a technical preventive measure that would prevent malicious traffic from entering a network while access permission reviews and regular audits are taken as administrative preventive measures.
Detective controls are effective when a security violation has occurred. Detective controls can also be implemented on technical aspects via an intrusion detection system or on physical aspects via motion detectors. Further review of logs after an incident can also serve as a detective countermeasure. On the other hand, corrective measures are deployed within an affected computing environment. These measures are carried out to rectify and modify the affected environment and achieve normalcy.
Additionally, session management is vital in web applications, in combination with proper authentication mechanisms (shelf authentication), and the absence of it could welcome malicious activities within web applications.
All three countermeasures can be coupled with proper policy implementations that are defined and documented in a secure manner, such as password policies, access control management policies, etc. In terms of SQL injections, prevention mechanisms vary from the subtype of SQL injection, the type of the database engine used, as well as the utilized programming language. The above measures can be utilized and additionally, training and awareness programs can be conducted on developers, quality assurers, and database administrators to be aware of associated risks.
Further implementation of proper validations on headers, queries and parameters, etc. can protect applications from XSS attacks which should not attempt in identifying content. Use of ‘positive’ security policies discourage attackers and encoding user-supplied data also has a great benefit on web applications. Also, programmers have to give more attention to using direct object references. Where insecure direct object references are used, the way is paved for attackers to conduct attacks effortlessly. User authorization can be made mandatory where a direct object reference is used. Likewise, a standard way to refer to application objects is always preferred, as it allows to identify any malicious coding patterns.
Moreover, having a well-documented hardening guideline helps developers to code applications with necessary configurations (for both application servers and web servers). General hardening guidelines include steps to configure all security mechanisms, unused devices recognition and auto turn off, access control, and logs. This also includes the use of cryptographic keys to protect sensitive data such as passwords etc. Encryption is a mandatory concept here. Regular encapsulation of cryptographic codes helps here as well, and the use of security wrappers is also suggested to secure vulnerable components.
Software programmers also have to consider redirecting validations, as this is another loophole that attackers are looking forward to exploiting. Avoiding the use of such redirects and implementing tight security controls in places where redirects and forwarding are used as a deterrence against attackers.
Staying on alert always helps in preventing SQL injection breaches. All applications and components need to be kept up to date, especially as additional features are adopted. Security teams and software coders should have proper training to understand all of the SQL injection attack methodologies and the countermeasure to these attacks. RangeForce offers extensive hands-on based simulation training to teach IT professionals how to exploit SQL injection and then how to build in each type of countermeasure described to prevent these common attacks. Interested in learning more? Request a demo with our team.
References
- What Is SQL Injection (SQL injection) And How To Prevent It. [online] Available at: <https://www.acunetix.com/websitesecurity/sql-injection/>
- Owasp.org. 2020. OWASP Top Ten Web Application Security Risks | OWASP. [online] Available at: <https://owasp.org/www-project-top-ten/>
- Veracode. 2020. OWASP Top 10 Vulnerabilities. [online] Available at: <https://www.veracode.com/directory/owasp-top-10>
- Netsparker.com. 2020. What Is The SQL Injection Vulnerability & How To Prevent It?. [online] Available at: <https://www.netsparker.com/blog/web-security/sql-injection-vulnerability/>
- Academy, W. and injection, S., 2020. What Is SQL Injection? Tutorial & Examples | Web Security Academy. [online] Portswigger.net. Available at: <https://portswigger.net/web-security/sql-injection>
