Lua SQL Injection
Guide: Examples
and Prevention

Lua is a language that has possible applications in almost every task. Lua is a multiplatform scripting language while also being a compiled language; it's also relatively fast and lightweight. This helps with its growing roots in embedded systems development. As Lua is fast, small, and low-power consuming, it's a ticket you must have in this area. However, the growing popularity of Lua makes it a target for potential cyberattacks, especially when attackers target embedded devices.

This post will explore how vulnerable Lua applications are to a web attack called SQL injection. But don't worry! We can continue developing in Lua because we'll show the various methods of SQL injection to look out for and specific ways that help with avoiding this attack in Lua code.

Let's take a step back before we go through the various possibilities of preventing SQL injections with Lua code. Why don't we first take a look at what SQL injections are overall?

What Is a SQL Injection?

The very first concept every developer exposing software in a network should know about is to never trust users' input. The whole concept of a SQL injection attack is based on the idea of bad input that hasn't been properly sanitized. When an attacker performs a SQL injection attack successfully, they are able to inject new SQL commands that allow access to the database behind a certain application. The consequences for this are very dangerous. The attacker has the ability to access private data, log in as a certain user, or even log in as the administrator. With this attack, hackers have the ability to delete all your data rather than just steal it.

Examples of SQL Injections

The most common way to perform a SQL injection is to put the malicious input containing SQL commands as user input. Examples of where this can happen are anywhere the user can write something, like forms. The fields in a form, where the user has to write usernames, emails, personal data, and so on, can be used to perform a SQL statement. As we talked about just before, the whole concept of SQL injection is valid if the input is not sanitized before. Once you filter out malicious input, the attacker might have their hands tied.

Let's take, for example, a form where the user must insert a username and password to log in to a web application.

Input form.

With this input form, the developer would just perform the simple SQL query in order to check whether a user with this username and password is present or not.

In this way, the SQL query will run just fine, and the malicious intent is present in the user input.

Consider instead this type of form filling.

SQL injection.

The query for checking login information will execute without anyone controlling the input parameters. And it will drastically change the intended behavior.

As you can see, this query gets executed, and the second part of the injected OR is always true given that the remaining original query gets commented out. The resulting query will return all rows, which will, of course, be at least one: the attacker will successfully access the system.

If you sanitize your input through some simple allowlisting techniques, this attack won't happen. The article "What is SQL Injection?" explores SQL injections more in depth, showing the different types of SQL injections, including the ways to not just gain access to but also change the content of the victim's database.

Avoiding SQL Injections in Lua

In Lua, there are two main ways to access and interact with databases:

• By using the common LuaSQL library, which enables you to connect to and perform SQL statements with various databases such as Oracle, MySQL, SQLite, PostgreSQL, and so on

• With mod_lua, which has its own built-in database API for running commands on MySQL, PostgreSQL, SQLite, Oracle, and other databases

However, we're not here to discuss the best method you can use to connect to a database. Whichever you choose, the most important thing about preventing SQL injection will always be to validate and sanitize user input.

In order to validate and sanitize user input, the first big recommendation for both Lua and all the other programming languages is to make use of prepared statements. And you should use prepared statements wherever you can in order to drastically reduce the possibility of SQL injection attacks.

In the below code example, we see where there are vulnerabilities for potential SQL injection attacks.

local driver = require "luasql.postgres"
env = assert (driver.postgres())
con = assert (env:connect("luasql-test"))

--no validation of 'username' input: SQL INJECTION VULNERABILITY
--r contains the http post request parameters
cur = assert (con:execute(string.format([[SELECT * FROM Users WHERE username = '%s' and password = '%s']], r.username, r.password)
-- login code
-- ...

cur:close()
con:close()
env:close()

The attacker can add any kind of input even if we're string formatting the input. The SQL query isn't safe against malicious input present in the username field where the user writes their input. The returned cursor will contain various results, which will enable the attacker to get access.

One way to prevent such a condition is the use of prepared statements.

local driver = require "luasql.postgres"
env = assert (driver.postgres())
con = assert (env:connect("luasql-test"))

stmt = con:prepare"SELECT * FROM Users WHERE username = ? and password = ?"
cur = stmt:execute(r.username, r.password) --r contains the http post request parameters

-- login code
-- ...

cur:close()
stmt:close()
con:close()
env:close()

When using prepared statements, we're exploiting a two-phase sending technique. We're sending the first query request without the data, as shown below:

After that, we'll send a second request involving the actual data to be placed for the requested fields. In this way, we're avoiding any issues regarding data literals containing potential SQL injection attacks. By doing this, we've secured the DBMS against malicious input inserted in the input data.

Unfortunately, prepared statements don't solve all the problems.

There are SQL injections where the input has to go through an allowlist process before you can use it for executing a SQL statement. Without going off topic, you can find more about what prepared statements can cover here.

Allowlisting in Lua

Here, we'll show how to allowlist user input in Lua in order to prevent further SQL injection attacks. This is because prepared statements can protect only data literals, but they can't protect against attacks in any other query parts.

That happens when your query is dynamic with respect to an identifier in the query or with a syntax keyword. In such cases, allowlisting together with prepared statements offer the maximum protection against SQL injections.

We can see an example below of a query where an identifier, which is not a data literal, is in a more dynamic query:

In such a case, the ORDER BY clause is filled with an identifier. This is exploitable for further misuses. One example can be the further insertion of data:

When you don't allowlist the dynamic identifier, a hacker can exploit this security issue in order to perform other operations on the database. That's just an insertion. What if someone decides to drop an entire table? The risks associated with not allowlisting your user input are pretty high.

Let's see an example on how to perform allowlisting in Lua. Here we'll be using regex. It will enable you to allowlist the user input with regards to a login field for an email address.

local driver = require "luasql.postgres"
env = assert (driver.postgres())
con = assert (env:connect("luasql-test"))
local regex = require "regex"
result, err = regex.test(p.email, "^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$")
if result then
    stmt = con:prepare"SELECT * FROM Users WHERE email = ? and password = ?"
    cur = stmt:execute(r.email, r.password) --r contains the http post request parameters
    -- login code 
    -- ... 
else
    -- error
end

cur:close()
stmt:close()
con:close()
env:close()

Are We Secure Now?

We have arrived at the conclusion of our journey on how to shield against SQL injection attacks in Lua. Are we really secure now? Against direct SQL injection attacks, yes, we are. But there are other potential vulnerabilities you have to cover in order to develop a secure software. The main lesson here is that security must always be a part of the software engineering process. And that security must be independent from the language that you'll use and where you're developing.

Anything can be a target for potential attacks if you don't pay the necessary attention to preventing such things from happening. That's exactly the reason for this article! To provide support for Lua developers in creating more secure code. Especially when the main usage of Lua is in embedded systems, which can contain quite sensitive information. You must do whatever you can to protect yourself from SQL injections.

This post was written by Daniele Comi. Daniele graduated with an M.Sc. in Computer Science Engineering, specializing in artificial intelligence and machine learning. His research interest is privacy-preserving deep learning models using homomorphic encryption schemes. He's currently developing new skills in the deep learning field while developing software for AI-related projects.