An Introduction to TQL Injections

Introduction

One of the oldest and most successful methods of extracting confidential data from applications and websites is the method known as SQL injection. For the unfamiliar, SQL injections are SQL statements which can be executed by malicious actors in vulnerable websites to extract personal/valuable info of their choosing.

In this blog, we will be taking a look at TQL injections — what they are and how to protect against them. Because TQL is rather similar to SQL, perhaps the lessons and techniques learned here can serve as a general guideline for other code injection attacks.

SQL/TQL Injection

To demonstrate how a TQL injection attack may look, take a look at the following example:

tql="select * where id='"+user_input+"'"
    col.query(tql)

This seems innocent enough — the website/app takes user input to query the DB to dig up the user’s username. But let’s say a malicious actor enters a user input that is equal to “12”, the executed query would look like:

select * where id='12'

Nothing broken yet. But because the user is allowed to enter anything into the input box, he can also enter something like 12' OR id LIKE '%':

select * where id='12' OR id LIKE '%'

Because this full query is allowed to run without any sort of defense from the developer, the malicious attacker would then fetch tons of information from the database that they would normally not be able to access.

Possible Solutions

There are any number of ways to defend against these sorts of injection attacks — for this specific blog we will discuss parameterizing, containerizing, sanitizing inputs, and encoding of URL data.

Parameterize

To parameterize something is to set a “limit or boundary that defines the scope of a particular process or activity”. For this method, we will be utilizing JDBC and its GridDB JDBC connector. The idea here is to protect precious data by using JDBC’s Prepared Statement.

query = "select * from data where id = '"+user_input+"';
    rs = stmt.executeQuery(query);

query = "select * from data where id = ?"';
    PreparedStatement stmt = con.prepareStatement()
    updateSales.setString(1, user_input);
    rs = stmt.executeQuery();

In this example, if a user tried entering a wildcard as shown above, the executed statement would return an error instead of precious data. This works because the developer sets a given type instead of an arbitrary SQL statement.

Containerizing

In this example, we can use GridDB’s containers architecture to properly protect our data.

col = gridstore.get_container("sensor_12") 

This, for example, would return the proper data.

col = gridstore.get_container("sensor_12' OR id LIKE '%")

But if the user tried the above example with a wildcard, the container would simply return an error as that container does not exist. Simple and extremely effective, though admittedly a tad limited.

Sanitizing Input

The method most-often seen used to counter-act injection attacks are escaping or sanitizing inputs. This works by escaping characters which have special meanings.

sanitized_input=re.sub("\"", "\\\"", re.sub( "\'", "\\\'",user_input))
    sanitized_tql="select * where id='"+sanitized_input+"'"

In the example here, all user inputs will be sanitized and cleaned to make sure that unwanted inputs or statements.

URL Encoding

The last possible solution we will take a peek at is URL Encoding. Typically this sort of protection method is for data forms which go through HTTP POST methods/requests. The idea is that all inputs will be encoded before sent to the database which will remove all special characters which can be used for basic injection attacks (like the wildcard we saw earlier)

tql="select * where id='"+user_input+"'"
    sanitized_input = urllib.parse.quote(user_input)
    sanitized_tql="select * where id='"+sanitized_input+"'"

You can see in the example above that before we run the user’s input to be queried, we encode the data first.

Conclusion

Though we discussed quite a few mitigation tactics for TQL/SQL Injection attacks, there are many more ways out there to be attacked and to prevent said attacks. Always develop your applications with security in mind!

If you have any questions about the blog, please create a Stack Overflow post here https://stackoverflow.com/questions/ask?tags=griddb .
Make sure that you use the “griddb” tag so our engineers can quickly reply to your questions.