sqlinjection

[sqlinjection]

Navigation

Introduction

Description of method

Explanation of the technological principles (techniques) Examples Detailed description of possible security approaches and solutions Examples of two real-life cases and technical/financial/etc. damages Statistical information, comparison of data from the last few years on the use of technology Demonstration/simulation using a virtual machine Choice of a blogging tool, explanation and evaluation Conclusions/Suggestions/Guidelines/Trends/Future work more specific ones on the different types of injection: #sql #sqlinjection #ldap #oscommandinjection #commandinjection #xss

[sqlinjection]

Conclusion & future trends

drawing the conclusion on the topic of injection attacks, it is crucial to say that although this type of the attack had a decrease in percentage terms of web application critical vulnerabilities, it is still important to spread awareness, defense and prevention techniques as it is still a thing.

Future trends and work:

Improved security awareness and practices

Advanced security tools like web-application firewalls (WAFs), AI-monitoring tools

More strict regulation requirements for data protection

due to that factors, the trend of SQL injection attacks may continue to decrease in percentage terms for 2024, following the decline from 33% in 2022 to 23% in 2023, but it is important to remember that crackers also do not stand still

this demands developers to wield the power of prepared statements, sanitize user input and follow the prevention measures. (more can be found on #prevention)

keep your SQL queries clean and tidy and keep injections where they belong — in medicine

[sqlinjection]

why tumblr?

Section on the choice of blogging tool, explanation and evaluation

having a little experience with it before, tumblr was chosen as a popular blogging tool which has simple yet pleasing to the eye design, moreover easy, convenient and pretty post creation.

pros:

simple yet attractive design (font, items etc.)

many options to highlight important elements/edit post to make it more convenient to read (different font, font size and colors, bulleted/numbered lists etc.)

customisable options in blog design (background, custom theme etc.)

many tools to make post prettier and more interesting: image, video, gif, link embedding, polls and so on.

dates on posts

stylish text

communication with followers through comment sections etc.

hashtags

cons:

no possiibility to create sub-menus (though can be done through hashtags)

no filtering (i.e. from latest to newest)

at first post creation may seem tricky and some features are working better from mobile app

custom theme may not have so many free options in comparison to other blogging tools, but I find beauty in simplicity and tumblr is a great example of it! it completely fulfills the idea of a blog itself: place for the content sharing, where ideas, texts, explanations, content itself is a crucial component. it feels free to speak here!

so i guess tumblr has a solid 9 at this point and i have not regretted choosing it not on a minute.

[sqlinjection]

SQLi simulation using a virtual machine

Demonstration/simulation of SQL Injection attacks (In-band, Union-based, Blind SQLi) using a Kali Linux virtual machine and a Damn Vulnerable Web Application (DVWA) on a low difficulty level

Blind SQL provided in the video can be used also for gaining other sensitive information: length of the name of the database, database name itself etc.

the common attacks are shown and described shortly in the video, but of course for better learning you can try it yourself.

more resources where you can try out exploiting SQLi vulnerability:

- Try Hack Me SQLi Lab

- W3Schools SQL Injection

- Hacksplaining SQL Injection

more advanced pokemons can try:

- Try Hack Me SQli Advanced Lab

and of course DVWA is a great tool!

[sqlinjection]

Statistics

source: statista 2022 In 2023, SQL injection attacks were responsible for 23% of web application critical vulnerabilities discovered globally compared to 2022, where they were 33%. They remain one of the most prevalent security risks for web applications, despite a decline in its share of critical vulnerabilities from 33% in 2022 to 23% in 2023. This decrease indicates an improvement in secure coding practices and database security, but SQLi still poses a significant risk. ! this is why it it so important to share awareness and mitigation/defense measures on injection attacks

[sqlinjection]

Impact of successful SQLi, examples

Three common ways SQL injection attacks can impact web apps: - unauthorized access to sensitive data (user lists, personally identifiable information (PII), credit card numbers) - data modification/deletion - administrative access to the system (-> unauthorized access to specific areas of the system or malicious actions performance) examples as always speak louder than explanations! there are going to be two of them 1. Equifax data breach (2017) - 1st way Hackers exploited a SQL injection flaw in the company’s system, breaching the personal records of 143 million users, making it one of the largest cybercrimes related to identity theft. Damages: The total cost of the settlement included $300 million to a fund for victim compensation, $175 million to the states and territories in the agreement, and $100 million to the CFPB in fines. 2. Play Station Network Outage or PSN Hack (2011) - 2nd way Tthe result of an "external intrusion" on Sony's PlayStation Network and Qriocity services, in which personal details from approximately 77 million accounts were compromised and prevented users of PlayStation 3 and PlayStation Portable consoles from accessing the service. Damages: Sony stated that the outage costs were $171 million. more recent CVEs: CVE-2023-32530. SQL injection in security product dashboard using crafted certificate fields CVE-2020-12271. SQL injection in firewall product's admin interface or user portal, as exploited in the wild per CISA KEV. ! this vulnerability has critical severity with a score 10. Description: A SQL injection issue was found in SFOS 17.0, 17.1, 17.5, and 18.0 before 2020-04-25 on Sophos XG Firewall devices, as exploited in the wild in April 2020. This affected devices configured with either the administration (HTTPS) service or the User Portal exposed on the WAN zone. A successful attack may have caused remote code execution that exfiltrated usernames and hashed passwords for the local device admin(s), portal admins, and user accounts used for remote access (but not external Active Directory or LDAP passwords) CVE-2019-3792. An automation system written in Go contains an API that is vulnerable to SQL injection allowing the attacker to read privileged data. ! this vulnerability has medium severity with a score 6.8.

[sqlinjection]

SQLi Potential Mitigation Measures

Phase: Architecture and Design

Strategy: Libraries or Frameworks

Use a vetted library or framework that prevents this weakness or makes it easier to avoid. For example, persistence layers like Hibernate or Enterprise Java Beans can offer protection against SQL injection when used correctly.

Phase: Architecture and Design

Strategy: Parameterization

Use structured mechanisms that enforce separation between data and code, such as prepared statements, parameterized queries, or stored procedures. Avoid constructing and executing query strings with "exec" to prevent SQL injection [REF-867].

Phases: Architecture and Design; Operation

Strategy: Environment Hardening

Run your code with the minimum privileges necessary for the task [REF-76]. Limit user privileges to prevent unauthorized access if an attack occurs, such as by ensuring database applications don’t run as an administrator.

Phase: Architecture and Design

Duplicate client-side security checks on the server to avoid CWE-602. Attackers can bypass client checks by altering values or removing checks entirely, making server-side validation essential.

Phase: Implementation

Strategy: Output Encoding

Avoid dynamically generating query strings, code, or commands that mix control and data. If unavoidable, use strict allowlists, escape/filter characters, and quote arguments to mitigate risks like SQL injection (CWE-88).

Phase: Implementation

Strategy: Input Validation

Assume all input is malicious. Use strict input validation with allowlists for specifications and reject non-conforming inputs. For SQL queries, limit characters based on parameter expectations for attack prevention.

Phase: Architecture and Design

Strategy: Enforcement by Conversion

For limited sets of acceptable inputs, map fixed values like numeric IDs to filenames or URLs, rejecting anything outside the known set.

Phase: Implementation

Ensure error messages reveal only necessary details, avoiding cryptic language or excessive information. Store sensitive error details in logs but be cautious with content visible to users to prevent revealing internal states.

Phase: Operation

Strategy: Firewall

Use an application firewall to detect attacks against weaknesses in cases where the code can’t be fixed. Firewalls offer defense in depth, though they may require customization and won’t cover all input vectors.

Phases: Operation; Implementation

Strategy: Environment Hardening

In PHP, avoid using register_globals to prevent weaknesses like CWE-95 and CWE-621. Avoid emulating this feature to reduce risks. source

[sqlinjection]

OS commands injection testing & defense

OS command injection is a technique used via a web interface in order to execute OS commands on a web server.

How to test for the issue

During code review

Check if any command execute methods are called and in unvalidated user input are taken as data for that command.

Besides, appending a semicolon to the end of a URL query parameter followed by an operating system command, will execute the command. %3B is URL encoded and decodes to semicolon. This is because the ; is interpreted as a command separator.

Example: 

http://sensitive/something.php?dir=%3Bcat%20/etc/passwd

If the application responds with the output of the /etc/passwd file then you know the attack has been successful. Many web application scanners can be used to test for this attack as they inject variations of command injections and test the response.

Equally Static Code Analysis tools check the data flow of untrusted user input into a web application and check if the data is then entered into a dangerous method which executes the user input as a command.

Remediation

If it is considered unavoidable the call to a system command incorporated with user-supplied, the following two layers of defense should be used within software in order to prevent attacks

Parameterization - If available, use structured mechanisms that automatically enforce the separation between data and command. These mechanisms can help to provide the relevant quoting, encoding.

Input validation - the values for commands and the relevant arguments should be both validated. There are different degrees of validation for the actual command and its arguments:

When it comes to the commands used, these must be validated against a list of allowed commands.

In regards to the arguments used for these commands, they should be validated using the following options:

Positive or allowlist input validation - where are the arguments allowed explicitly defined

Allow-list Regular Expression - where is explicitly defined a list of good characters allowed and the maximum length of the string. Ensure that metacharacters like & | ; $ > < \ \ !` and whitespaces are not part of the Regular Expression. For example, the following regular expression only allows lowercase letters and numbers, and does not contain metacharacters. The length is also being limited to 3-10 characters:

^[a-z0-9]{3,10}$

Example code - Java

Incorrect Usage

ProcessBuilder b = new ProcessBuilder("C:\DoStuff.exe -arg1 -arg2");

the command together with the arguments are passed as a one string, making easy to manipulate that expression and inject malicious strings.

Correct Usage

ProcessBuilder pb = new ProcessBuilder("TrustedCmd", "TrustedArg1", "TrustedArg2"); Map<String, String> env = pb.environment(); pb.directory(new File("TrustedDir")); Process p = pb.start();

starts a process with a modified working directory

The command and each of the arguments are passed separately which makes it easy to validate each term and reduces the risk to insert malicious strings

[sqlinjection]

LDAP testing & defense

LDAP Injection is an attack used to exploit web based applications that construct LDAP statements based on user input. When an application fails to properly sanitize user input, it's possible to modify LDAP statements through techniques similar to SQL Injection. 

LDAP injection attacks are common due to two factors:

The lack of safer, parameterized LDAP query interfaces

The widespread use of LDAP to authenticate users to systems.

How to test for the issue

During code review

Please check for any queries to the LDAP escape special characters, see here.

Automated Exploitation

Scanner module of tool like OWASP ZAP have module to detect LDAP injection issue.

Remediation

Escape all variables using the right LDAP encoding function

The main way LDAP stores names is based on DN (distinguished name). You can think of this like a unique identifier. These are sometimes used to access resources, like a username.

A DN might look like this

cn=Richard Feynman, ou=Physics Department, dc=Caltech, dc=edu

or

uid=inewton, ou=Mathematics Department, dc=Cambridge, dc=com

There are certain characters that are considered special characters in a DN. The exhaustive list is the following: \ # + < > , ; " = and leading or trailing spaces

Each DN points to exactly 1 entry, which can be thought of sort of like a row in a RDBMS. For each entry, there will be 1 or more attributes which are analogous to RDBMS columns. If you are interested in searching through LDAP for users will certain attributes, you may do so with search filters. In a search filter, you can use standard boolean logic to get a list of users matching an arbitrary constraint. Search filters are written in Polish notation AKA prefix notation.

Example:

(&(ou=Physics)(| (manager=cn=Freeman Dyson,ou=Physics,dc=Caltech,dc=edu) (manager=cn=Albert Einstein,ou=Physics,dc=Princeton,dc=edu) ))

When building LDAP queries in application code, you MUST escape any untrusted data that is added to any LDAP query. There are two forms of LDAP escaping. Encoding for LDAP Search and Encoding for LDAP DN (distinguished name). The proper escaping depends on whether you are sanitising input for a search filter, or you are using a DN as a username-like credential for accessing some resource.

Safe Java for LDAP escaping Example:

public String escapeDN (String name) {

//From RFC 2253 and the / character for JNDI

final char[] META_CHARS = {'+', '"', '<', '>', ';', '/'};

String escapedStr = new String(name);

//Backslash is both a Java and an LDAP escape character,

//so escape it first escapedStr = escapedStr.replaceAll("\\\\\\\\","\\\\\\\\");

//Positional characters - see RFC 2253

escapedStr = escapedStr.replaceAll("\^#","\\\\\\\\#");

escapedStr = escapedStr.replaceAll("\^ | $","\\\\\\\\ ");

for (int i=0 ; i < META_CHARS.length ; i++) {

escapedStr = escapedStr.replaceAll("\\\\" + META_CHARS[i],"\\\\\\\\" + META_CHARS[i]);

}

return escapedStr;

}

[sqlinjection]

Remediation of SQLi

Defense Option 1: Prepared Statements (with Parameterized Queries)

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. In the safe example below, if an attacker were to enter the userID of tom' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string tom' or '1'='1.

Defense Option 2: Stored Procedures

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.

Both of these techniques have the same effectiveness in preventing SQL injection so it is reasonable to choose which approach makes the most sense for you. Stored procedures are not always safe from SQL injection. However, certain standard stored procedure programming constructs have the same effect as the use of parameterized queries when implemented safely (the stored procedure does not include any unsafe dynamic SQL generation) which is the norm for most stored procedure languages.

Defense Option 3: Allow-List Input Validation

Various parts of SQL queries aren't legal locations for the use of bind variables, such as the names of tables or columns, and the sort order indicator (ASC or DESC). In such situations, input validation or query redesign is the most appropriate defense. For the names of tables or columns, ideally those values come from the code, and not from user parameters.

But if user parameter values are used to make different for table names and column names, then the parameter values should be mapped to the legal/expected table or column names to make sure unvalidated user input doesn't end up in the query. Please note, this is a symptom of poor design and a full rewrite should be considered if time allows.

Defense Option 4: Escaping All User-Supplied Input

This technique should only be used as a last resort, when none of the above are feasible. Input validation is probably a better choice as this methodology is frail compared to other defenses and we cannot guarantee it will prevent all SQL Injection in all situations.

This technique is to escape user input before putting it in a query and usually only recommended to retrofit legacy code when implementing input validation isn't cost effective.

[sqlinjection]

How to test app for the SQL injection

During code review

Check for any queries to the database are not done via prepared statements.

If dynamic statements are being made please check if the data is sanitized before used as part of the statement.

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.

Automated Exploitation

Most of the situation and techniques on testing an app for SQLi can be performed in a automated way using some tools (e.g. perform an automated auditing using SQLMap)

Equally Static Code Analysis Data flow rules can detect of unsanitised user controlled input can change the SQL query.

Stored Procedure Injection

When using dynamic SQL within a stored procedure, the application must properly sanitise the user input to eliminate the risk of code injection. If not sanitised, the user could enter malicious SQL that will be executed within the stored procedure.

Time delay Exploitation technique

The time delay exploitation technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. This technique consists in sending an injected query and in case the conditional is true, the tester can monitor the time taken to for the server to respond. If there is a delay, the tester can assume the result of the conditional query is true. This exploitation technique can be different from DBMS to DBMS.

http://www.example.com/product.php?id=10 AND IF(version() like '5%', sleep(10), 'false'))--

In this example the tester is checking whether the MySql version is 5.x or not, making the server delay the answer by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn't need to wait for the response. Sometimes they can set a very high value (e.g. 100) and cancel the request after some seconds.

Out-of-band Exploitation technique

This technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. The technique consists of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester's server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

[sqlinjection]

How to Prevent

Preventing injection requires keeping data separate from commands and queries:

The preferred option is to use a safe API, which avoids using the interpreter entirely, provides a parameterized interface, or migrates to Object Relational Mapping Tools (ORMs). Note: Even when parameterized, stored procedures can still introduce SQL injection if PL/SQL or T-SQL concatenates queries and data or executes hostile data with EXECUTE IMMEDIATE or exec().

Use positive server-side input validation. This is not a complete defense as many applications require special characters, such as text areas or APIs for mobile applications.

For any residual dynamic queries, escape special characters using the specific escape syntax for that interpreter. (escaping technique) Note: SQL structures such as table names, column names, and so on cannot be escaped, and thus user-supplied structure names are dangerous. This is a common issue in report-writing software.

Use LIMIT and other SQL controls within queries to prevent mass disclosure of records in case of SQL injection.

bonus question: think about how query on the image above should look like? answer will be in the comment section

[sqlinjection]

SQL injection

we will recall SQLi types once again because examples speak louder than explanations!

In-band SQL Injection

This technique is considered the most common and straightforward type of SQL injection attack. In this technique, the attacker uses the same communication channel for both the injection and the retrieval of data. There are two primary types of in-band SQL injection:

Error-Based SQL Injection: The attacker manipulates the SQL query to produce error messages from the database. These error messages often contain information about the database structure, which can be used to exploit the database further. Example:  SELECT * FROM users WHERE id = 1 AND 1=CONVERT(int, (SELECT @@version)). If the database version is returned in the error message, it reveals information about the database.

Union-Based SQL Injection: The attacker uses the UNION SQL operator to combine the results of two or more SELECT statements into a single result, thereby retrieving data from other tables. Example:  SELECT name, email FROM users WHERE id = 1 UNION ALL SELECT username, password FROM admin.

Inferential (Blind) SQL Injection

Inferential SQL injection does not transfer data directly through the web application, making exploiting it more challenging. Instead, the attacker sends payloads and observes the application’s behaviour and response times to infer information about the database. There are two primary types of inferential SQL injection:

Boolean-Based Blind SQL Injection: The attacker sends an SQL query to the database, forcing the application to return a different result based on a true or false condition. By analysing the application’s response, the attacker can infer whether the payload was true or false. Example:  SELECT * FROM users WHERE id = 1 AND 1=1 (true condition) versus SELECT * FROM users WHERE id = 1 AND 1=2 (false condition).  The attacker can infer the result if the page content or behaviour changes based on the condition.

Time-Based Blind SQL Injection: The attacker sends an SQL query to the database, which delays the response for a specified time if the condition is true. By measuring the response time, the attacker can infer whether the condition is true or false. Example:  SELECT * FROM users WHERE id = 1; IF (1=1) WAITFOR DELAY '00:00:05'--. If the response is delayed by 5 seconds, the attacker can infer that the condition was true.

Out-of-band SQL Injection

Out-of-band SQL injection is used when the attacker cannot use the same channel to launch the attack and gather results or when the server responses are unstable. This technique relies on the database server making an out-of-band request (e.g., HTTP or DNS) to send the query result to the attacker. HTTP is normally used in out-of-band SQL injection to send the query result to the attacker's server. We will discuss it in detail in this room.

Each type of SQL injection technique has its advantages and challenges.

[sqlinjection]

SQL Injection

perhaps, the direct association with the SQLi is:

' OR 1=1 -- -

but what does it mean?

Imagine, you have a login form with a username and a password. Of course, it has a database connected to it. When you wish a login and submit your credentials, the app sends a request to the database in order to check whether your data is correct and is it possible to let you in.

the following PHP code demonstrates a dynamic SQL query in a login from. The user and password variables from the POST request is concatenated directly into the SQL statement.

$query ="SELECT * FROM users WHERE username='" +$_POST["user"] + "' AND password= '" + $_POST["password"]$ + '";"

"In a world of locked rooms, the man with the key is king",

and there is definitely one key as a SQL statement:

' OR 1=1-- -

supplying this value  inside the name parameter, the query might return more than one user.

most applications will process the first user returned, meaning that the attacker can exploit this and log in as the first user the query returned

the double-dash (--) sequence is a comment indicator in SQL and causes the rest of the query to be commented out

in SQL, a string is enclosed within either a single quote (') or a double quote ("). The single quote (') in the input is used to close the string literal.

If the attacker enters ' OR 1=1-- - in the name parameter and leaves the password blank, the query above will result in the following SQL statement:

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

executing the SQL statement above, all the users in the users table are returned -> the attacker bypasses the application's authentication mechanism and is logged in as the first user returned by the query. 

The reason for using  -- - instead of -- is primarily because of how MySQL handles the double-dash comment style: comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). The safest solution for inline SQL comment is to use --<space><any character> such as -- - because if it is URL-encoded into  --%20- it will still be decoded as -- -.

[sqlinjection]

SQL Injection

behold, the grandparent of all injection attacks, the one and only, the most popular, well-known and common: SQL Injection — or as we affectionately call it, SQLi. This mischievous little query manipulator has been causing trouble in databases since the dawn of the web, sneaking past input fields with its VIP badge, breaking down the doors of login pages, and leaving developers shaking in their boots. now more serious: SQLi is used to attack data-driven applications constructing all or partly of an SQL command using externally-influenced input from an upstream component, but it does not neutralise (incorrectly neutralise) special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data. now simpler: SQLi attacks applications that do not properly validate or sanitize user input, causing the application to misinterpret it as part of an SQL command. hope the pretty picture will also help to understand SQLi technique better

[sqlinjection]

Command (shell) injection example

examples speak louder than explanations!

e.g., a web-based CGI program allows users to change their passwords -> this program has a command to rebuild some password records by running the make command in the /var/yp directory:

system("cd /var/yp && make &> /dev/null");

unlike the previous examples, the command is hardcoded, so an attacker cannot control the argument passed to system(). seems safe, right? nuh-uh :)

key vulnerabilities:

program does not specify an absolute path for make, and does not scrub any environment variables prior to invoking the command

->

attacker can modify their $PATH to point to a malicious version of make, therefore their malicious version of make is executed instead of the intended /usr/bin/make

setuid root runs with root privileges, even if a regular user runs it. so, malicious version of make now runs with root privileges

btw, using Java at this point is more safe

Runtime.getRuntime().exec("cd /var/yp && make");

and here is why:

Runtime.exec does NOT try to invoke the shell at any point: it tries to split the string into an array of words, then executes the first word in the array with the rest of the words as parameters

therefore, it does not go through chaining commands using “&”, “&&”, “|”, “||”, etc, redirecting input and output and any mischief would simply end up as a parameter being passed to the first command, and likely causing a syntax error, or being thrown out as an invalid parameter

[sqlinjection]

Command (shell) injection

involves execution of arbitrary commands on the host operating system via a vulnerable application

applies to most systems which allow software to o programmatically execute a command line

examples speak louder than explanations!

vulnerable tcsh script looks as follows:

# !/bin/tcsh

# check arg outputs it matches if arg is one

if ($1 == 1) echo it matches

the script does not properly validate input argument: tsch interprets everything within the parentheses () as part of the if statement

so it is possible to run the following script:

./check "1 ) evil "

if statement now is:

if (1 ) evil == 1)

which is invalid syntax in normal scenario, however! because of shell parsing quirks it will execute evil command:

tcsh stops processing the if condition at the first syntax it recognises as complete (in our case, if (1 ))

then it sees evil as a standalone command (because it is separated from the if statement by parethesis) and interprets is as an executable command

! any function that can be used to compose and run a shell command is a potential vehicle for launching a shell injection attack e. g. :

system(), StartProcess(), System.Diagnostics.Process.Start().