SQL Injection (SQLi) is a security vulnerability that allows attackers to interfere with the queries that an application makes to its database. This can allow attackers to view data that they are not normally able to retrieve, and in some cases, modify or delete this data.
EXPLANATION
HOW IT WORKS
LOGIN PAGE SCENARIO
SQL Injection occurs when an attacker is able to insert a series of SQL statements into a query by manipulating user input. This can result in unauthorized access to data or even the complete compromise of the database.
1. User Input: A web application accepts input from a user.
<input type="text" name="username"> 2. Improper Sanitization: this input is used directly in a SQL query without sanitization, it becomes vulnerable.
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";3. Attacker Input: Attacker can predict the code style to ascertain that qwerty’ OR ‘1’=’1 in the password field with a known username like “admin“.
4. Login Bypass: The server executes the malicious SQL code injected through user input. Since the condition ‘1’=’1′ is always true, the attacker bypasses authentication. Regardless of the actual password, the database validates the statement, allowing unauthorized access.
$query = "SELECT * FROM users WHERE username = 'admin' AND password = 'qwerty' OR '1'='1'";VARIATIONS OF SQL INJECTIONS
1. IN-BAND SQL INJECTIONS
- Error-based SQL Injection:
- Definition: The attacker causes the database to generate an error message, which may contain useful information about the database structure.
- Example: An attacker inputs ‘ OR 1=1 – – and observes the error message to infer database details.
- Explanation: The attacker uses error messages to gather information about the database.
- Union-based SQL Injection:
- Definition: The attacker uses the UNION SQL operator to combine the results of two or more SELECT statements into a single result.
- Example: An attacker inputs ‘ UNION SELECT username, password FROM users – – to extract data from the users’ table.
- Explanation: The attacker combines the original query with additional queries to extract more data.
2. OUT-OF-BAND SQL INJECTION
- Definition: The attacker sends SQL commands that are executed by the database and then retrieved by the attacker through a different communication channel.
- Example: An attacker triggers a database to make an HTTP request to a server controlled by the attacker.
- Explanation: This method relies on the database’s ability to make network connections to deliver results to the attacker through a different channel.
3. BLIND SQL INJECTIONS
- Boolean-based Blind SQL Injection:
- Definition: The attacker sends SQL queries that return different results based on a true or false condition and infers the data based on the application’s response.
- Example: An attacker inputs 1′ AND 1=1 – – and 1′ AND 1=2 – – and observes the differences in responses to infer if the condition is true or false.
- Explanation: The attacker deduces information from the database based on the application’s true/false responses.
- Time-based Blind SQL Injection:
- Definition: The attacker sends SQL queries that cause a delay in the database’s response, allowing the attacker to infer information based on the time taken to respond.
- Example: An attacker inputs 1′ AND IF(1=1, SLEEP(5), 0) – – to cause a delay if the condition is true.
- Explanation: The attacker infers information from the database based on the time delay in the response.
ALL THE STEPS IN CHECKING FOR SQL INJECTION
A typical login page asks for a username and password from the user and sends it to the backend. In the backend, a script is programmed to query the database to check if the provided credentials are valid. If the credentials are valid, the database sends an affirmative response (OK). Based on the database response, the backend decides which page to display, either the dashboard or the login page with a “wrong credential” alert.
But as we understood before, here’s how the vulnerable query looks:
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";An attacker can perform the following steps:
1. Attacker Submits Data: The attacker sends two sets of credentials: “test”:”test” and “admin’ – – -“:”test” through a web form or input field on a vulnerable website, in our case, the login page.
2. Data Submission: The web application processes the input data without proper sanitization or validation.
3. SQL Query Construction: The application constructs a SQL query incorporating the non-sanitized input:
$query = "SELECT * FROM users WHERE username = 'test' AND password = 'test'";AND
$query = "SELECT * FROM users WHERE username = 'admin' -- -' AND password = 'test'";4. Database Response: The database executes the query and returns data:
- For the first case, it will just return with invalid credentials (NO)
- For the second set, the database gets confused because the input practically only contains one input due to the comment “– -”, which ignores the rest of the query. This makes no sense to the database, and it sends an error.
5. Response Generation: Based on the DB response, the backend decides which page to display next:
- For the first case, it will bring back the login page with an error message “Wrong Username”.
- For the second case, since there was no proper response from the database, the backend does not know what to load on the next page, thus it gives a 500 server error.
This difference in output is called error-based SQL injection, and it confirms for the attacker that the input parameter is VULNERABLE TO SQL INJECTION.
IMPACT
Generic Consequences
- Data Breach: Attackers can gain unauthorized access to sensitive data, including customer information, financial records, or proprietary business data. This could lead to exposure of confidential information and put users at risk of identity theft or fraud.
- Data Manipulation: Through SQL injection, attackers can alter, insert, or delete data within the database. This manipulation can distort records, impact data integrity, and disrupt business operations that rely on accurate information.
- System Compromise: A successful SQL injection attack can sometimes provide attackers with administrator-level access, allowing them to take full control over the affected system. This access may enable them to execute commands, install malicious software, and potentially pivot to other systems within the network.
Business Impact
- Financial Loss: SQL injection attacks can lead to significant financial losses, either through direct theft of funds, costs associated with recovery and remediation, or lost revenue due to downtime. Additionally, attackers may leverage stolen information for financial gain.
- Legal Consequences: SQL injection incidents often violate data protection and compliance regulations, such as GDPR or HIPAA. This can lead to fines, legal actions, and mandatory reporting to regulatory bodies, further escalating costs.
- Reputational Damage: A breach can severely damage customer trust and the organization’s reputation. Customers may lose confidence in the company’s ability to protect their data, leading to churn, reduced customer acquisition, and long-term damage to brand reputation.
REMEDIATION
STEP 1: User Submits Data:
- Prevention: Frontend Input Validation
- Description: Ensure that all user inputs are validated against a strict set of rules to filter out potentially malicious input.
- Protection: Prevents attackers from submitting SQL commands as input by checking for and rejecting any input that does not conform to expected patterns.
STEP 2: Data Submission:
- Prevention: Backend Input Sanitization
- Description: Sanitize user inputs before any kind of processing is done on the input.
- Protection: Removes or escapes characters that can be used in SQL commands, ensuring stored data cannot execute SQL when retrieved.
STEP 3: SQL Query Construction:
- Prevention: Use of Prepared Statements and Parameterized Queries
- Description: Construct SQL queries using prepared statements and parameterized queries.
- Protection: Separates SQL code from data, preventing the execution of injected SQL code.
- Example: Secure Code for a Login Page:
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();STEP 4: Query Execution:
- Prevention: Least Privilege Principle
- Description: Ensure that database interactions are performed by users with the least privilege necessary.
- Protection: Limits the potential damage from SQL injection attacks.
STEP 6: Response Generation:
- Prevention: Using a Self-made 404 Error Page
- Description: Configure the application to redirect all 5XX errors to a custom 404 error page.
- Protection: Ensures that attackers do not receive detailed error messages that could reveal information about the database or application structure. This makes it harder for attackers to gain insights from server errors.
CONCLUSION
SQL Injection (SQLi) is a critical vulnerability that can lead to severe consequences if not properly addressed. By understanding how SQLi works and implementing robust prevention measures at each step of data processing, developers can significantly reduce the risk of such attacks. Input validation, sanitization, prepared statements, and secure coding practices are essential components of a strong defense strategy against SQLi. Regular security assessments and developer training are also crucial in maintaining a secure application environment. By taking these proactive steps, we can protect our users and systems from the potentially devastating effects of SQL injection attacks.