Low-Code platforms like Intrexx are usually secure and really well tested. However,they are basically just a toolbox that help you create your own applications. To make sure these are secure as well, there are several things you need to consider. One of them is preventing SQL injections inside your scripts.
What is an SQL-Injection?
SQL-Injection basically means, that a hacker injects an SQL-statement with malicious code somewhere in your application which then gets executed directly on your database and may cause severe damage to your company. How does he do that? Of course, he cannot access your application code in order to change that. However, if you offer an input form, he can put in any text and submit that. If you don’t do any scripting with Groovy, Velocity or any other scripting language, you’re safe.
If you use scripts with SQL statements, you should take a close look at those.
How to prevent SQL-Injections?
The first step to prevent this stuff from happening is, to accept that it’s there. In general you can assume that every part of data which gets transferred from the Browser to your Server may contain some sort of SQL or malicious code and may therefor be dangerous.
Now there are a few rules you need to comply with:
- Never add dangerous Content to an SQL-String directly.
- Always use the concept of prepared statements if available.
- Don’t connect to your database using an account with administrative permissions.
The first rule is best explained by just showing you how not do it. I use a Groovy script for this because for this kind of stuff Groovy is used most of the time in an Intrexx environment.
def strSearchString = g_request.get("rq_SearchString") def strSql = "select * from myTable where searchColumn like '%$strSearchString%'"
This snippet writes the request-value directly into the SQL statement. Where this actually would work, it’s also a major security risk. If the request-value contained SQL itself, your query would be changed completely and may even result in a hacker taking over your whole portal-server.
In order to prevent this, prepared Statements were introduced a long time ago. In these Statements you usually replace the search-term with an exclamation-mark. After the statement is prepared, you tell it which of the parameters should be replace with which values. The logic behind the prepared statements then makes sure, that even if the search-term contained any malicious code, it doesn’t get executed.
This is how the SQL statement looks, if you want to prevent an SQL injection:
def strSearchString = g_request.get("rq_SearchString") def strSql = "select * from myTable where searchColumn like ?" def stmt = g_dbQuery.prepare(g_dbConnections.systemConnection, strSql) stmt.setString(1, "%$strSearchString%")
The third rule is for the case when attackers manage to inject one of your SQL statements anyway. If that happens, you want to make sure the hacker doesn’t take control over the whole server. For this it is absolutely necessary to connect to the database as a user who has only the rights he needs. For example, admins usually have permissions to execute binary files on the database server. If an attacker manages to upload and execute such a file via an SQL injection, your simply doomed. So take care of that problem by removing these permissions from the user you use.
SQL injections are still a real scenario. Low-Code platforms are usually prepared for this, but since you are able to code yourself, it’s up to you to secure your SQL.