SQL Injection — Introduction with Ballerina
What are injection attacks and SQL injection?
Injection attacks can be considered as one of the oldest and most dangerous attacks aimed at web applications. In an injection attack, untrusted input is provided to the program by the attacker and when that input is processed by the application it can alter the execution of the program.
SQL injection is one of the widely spread attacks of this type and it injects/inserts malicious SQL statements via the input data to an application. Then these statements can control the database server which is running behind the application.
A successful SQL injection can result in,
- Unauthorized access to sensitive data
- Modification (Insert/Update/Delete) of database data
- Execution of administrator operations to the database server
- Execution of OS commands which can lead to full system compromise
To illustrate the samples I am using a sample written in Ballerina programming language. The full vulnerable sample is as follows. This sample is based on an H2 database and the H2 database driver JAR is included by default in the Ballerina distribution. So you can try out the sample easily by installing Ballerina (without installing any database). Here I am using Ballerina language version — Ballerina Swan Lake Preview 2 — for these samples.
To run the above sample use ‘ballerina run sql_injection.bal’ command and the output is as follows.
Common SQL injection techniques
Let’s look at some common SQL injection techniques used in the above sample. The query statement used in above vulnerable application is as follows. Since the query is constructed dynamically by concatenating a constant base query string and a user input string, the application is vulnerable to SQL injection attacks.
Modify SQL query using double dash sequence:
Refer to the ‘Case 3 ‘ in the sample. The key thing here is that the double-dash sequence — is a comment indicator in SQL, and means that the rest of the query is interpreted as a comment. This effectively removes the remainder of the query, so it no longer includes the AND part of the query and if a valid user name is known, the attacker can find the password by this. The generated query is as follows.
Boolean based SQL injection:
Refer to the ‘Case 4’ in the sample. The attacker sends a SQL query to the database prompting the application to return a result. The result will vary depending on whether the query is true or false. Because of the OR 1=1 statement, the WHERE clause returns the data from the Users table no matter what the username and password are.
Union-based SQL injection:
The UNION keyword can be used to retrieve data from other tables within the database. It combines the results of two or more SELECT statements into a single result which is then returned as part of the query response. The following requirements should be met to do a union-based SQL injection.
- The individual queries must return the same number of columns.
- The data types in each column must be compatible with the returned column types of individual queries.
Refer to the ‘Case 5.1’ in the sample.To find the number of columns, we can inject a series of ORDER BY clauses and incrementing the specified column index until an error occurs as follows. Since the column is specified by the index, we don’t need to know the names of any columns. When the column index exceeds the number of actual columns in the result set, the database returns an error.
Since the below query gives an error we can assume that the number of columns returned by the original query is 3.
Refer to the ‘Case 5.2’ in the sample. Now we need to identify the column types of the returned columns. Since we know the column count already, this can be done by submitting a union select payloads that place string value into each column. This is a simple test to check whether each column can hold string data. If it cannot hold string data it will return an error.
The following is the query generated by the above statement.
Refer to the ‘Case 5’ in the sample. Now we can use UNION to select data from another table in the database by matching the selected column count and their data type as follows.
The generated query which returns the data in the Products table is as follows.
How to prevent SQL injection
- Validate the user input — The application code should never use the input directly. The developer must sanitize all input coming to the application. During the sanitizing process, all the potential malicious code elements such as single quotes should be removed from the input.
- Use parameterized queries — Instead of generating the query statement by concatenating the user input, use SQL parameters. SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.
- Turn off the visibility of database errors — Database errors can be used with SQL Injection to gain information about the database. So instead of displaying the SQL statements that caused the error and SQL error messages to the user, provide a custom generic error message for all errors.
- Control the database user access rights — To reduce what SQL statements the application can perform on the database, only required access rights should be given to the accounts used by the application to connect to the database.
How we can use parameterized queries in Ballerina to prevent SQL injection is as follows.
