SQL Injections vs Elixir

SQL Injections. It's a monster that every programmer has heard of, but only a few have seen its devastating effects. Also, it's very likely that you've created at least one such vulnerability on your programming road and don't even know about it.

Here, come, I'm going to give you some weapons to fight the monster.

Actually, it’s not a monster. If you spend some time for education on the topic, there’s no unknown evil waiting to bite you from the darkness. I just wanted to create some dramatic background. Let’s begin.

So, what is SQL Injection?

Every web application with a relational database (PostgreSQL, MySQL, Microsoft SQL Server, OracleDB, etc...) needs to create SQL-based queries to communicate with it. If such a query is built with a simple concatenation of untrusted input, then there are big chances the vulnerability occurs. An attacker can manipulate the query in a way to get data he shouldn't be able to, or make modifications to records. And in the spiciest scenarios; get your data, drop your DB and happily run away to sell it or come back for the ransom. If you don't have a backup, then you're screwed up. Actually, if you have a backup, you're still screwed up because of the sensitive information leak.

There are two types of people:

People who do backup and people who will start doing backup.

How (not) to…

…code. We, programmers, love code examples. So here we go. Let's say we've got a blogging system in the application (That's of course a really hackneyed example, but still cool).

Any real blog has a search feature, which allows users to input their cravings - or incredibly dangerous phrases - and hope that the blog go to make their dreams come true. Here's an example of the (bad) implementation of such a feature (or at least part of it):

# Connect to the DB with Ecto Adapter. Nothing interesting
{:ok, conn} = Ecto.Adapters.SQL.Connection.start_link(config)
input = conn.params["search_input"]

# And here is the MEAT. Untrusted input interpolation.
query = "SELECT * FROM posts WHERE title LIKE '%#{input}%' AND published=1"

Ecto.Adapters.SQL.query!(conn, query)
|> ...

We can clearly see that the user's input is directly interpolated into the SQL query. That's the root cause of all problems that can easily build up.

Let's say Mattheo, the famous Black Hat, targeted the blog and put a classical asdf' OR 1=1 -- phrase into search input on the page. The generated query string, assigned to the query variable, is going to look like this:

"SELECT * FROM posts WHERE title LIKE '%asdf' OR 1=1 -- %' AND published=1"

After breaking down the sentence into prime factors, we can assume the system will return every post that:

• has a title, which ends with "asdf"
• OR for which 1 is equal to 1... soo, actually all of them - it is an independent condition and always true.
• ...that’s all; published=1 condition is no longer taken into account by the database, because of the -- single-line comment symbol, which invalidates the rest of the query.

If the vulnerability occurs, then all of the posts will be returned. Those which are not published yet, too. In another case only posts which end with "asdf' OR 1=1" will be returned, so probably the round zero.

We know the codebase, so we can assume that Mattheo will receive not only access to the unpublished posts but also information that the SQL Injection vulnerability is present and exploitable!

Another easy method to check if the vulnerability occurs is to put just a single quote mark into the input. If the system shows errors to the user, then the attacker might receive the SQL syntax error, as the number of quote marks would be uneven.

Okay... But why is it even a big deal?

Let's put it straight. The effects of SQL Injection can be both wide-ranging and catastrophic.

Attackers can employ stacked queries to execute multiple SQL statements in a single request, enabling them to drop entire databases, delete critical data, or modify sensitive information. This technique grants them complete control over the targeted system, leading to data breaches, unauthorized access, and severe damage to an organization's reputation.

Moreover, SQL Injection can enable code execution, allowing attackers to execute arbitrary commands on the underlying server, thereby compromising the entire system and potentially escalating their privileges. This level of intrusion opens the door for further exploitation, including lateral movement within the network, installation of backdoors, and the exfiltration of confidential information. Consequently, the fallout from SQL Injection can result in financial loss, legal ramifications, compromised customer data, and significant disruptions to business operations. Can you feel the weight of potential consequences?

Underestimate the power of the SQL injections, do not. Hmmmmmm.

~~ Yoda, Jedi and SQL Master

To safeguard against such threats, it is imperative to implement robust input validation, parameterized queries, and secure coding practices throughout the entire application stack. Let's begin with Ecto.

Ecto

Ecto, as a database wrapper (in simplification) for Elixir, provides Query DSL (Domain-Specific Language), which is from the start quite a good safeguard. Using it makes the code resistant to SQL Injections. It’s because of the usage of parametrized queries. Ecto Adapter prepares a statement that includes placeholders for parameters. These placeholders will be filled with specific values when the query is executed by the database.

Let’s see how it works with an exemplary query:

iex(1)> id = 5
iex(2)> q = from u in User, where: u.id==^id, select: u.password
#Ecto.Query<from a0 in MyApp.User, where: a0.id == ^5, select: a0.firstname>
iex(3)> Ecto.Adapters.SQL.to_sql(:all, MyApp.Repo, q)
{"SELECT u0.\"password\" FROM \"users\" AS u0 WHERE (u0.\"id\" = $1)", [5]}

Params will be escaped and sanitized before query execution, so no worries, any phrase being part of SQL syntax gonna be treated as a simple string and won’t be able to do any harm.

Ecto.Query.API.fragment/1

Again, parameterized queries play here a big role. fragment(...) provides ? operator, interpreted as a placeholder for parameters, given as additional arguments for the function. A later mechanism is similar to the previous example:

iex(1)> id = 5
iex(2)> q = from u in User, where: fragment("id=?", ^id), select: u.password
#Ecto.Query<from a0 in MyApp.User, where: a0.id == ^5, select: a0.firstname>
iex(3)> Ecto.Adapters.SQL.to_sql(:all, MyApp.Repo, q)
{"SELECT u0.\"password\" FROM \"users\" AS u0 WHERE (id=$1)", [5]}

Here comes the danger! An inattentive user could simply concatenate some values into the string passed to the fragment(...). Just like:

iex(1)> field_name = "id"
iex(2)> q = from u in User, where: fragment("#{field_name}=?", ^id), select: u.password
...

Go one step further and get the field name from the user’s input. A moment of carelessness and we’ve got a vulnerability on production.

Fortunately, Ecto developers closed this road and made sure no one will follow this path:

iex(1)> field_name = "id"
iex(2)> q = from u in User, where: fragment("#{field_name}=?", ^id), select: u.password

** (Ecto.Query.CompileError) to prevent SQL injection attacks, 
fragment(...) does not allow strings to be interpolated as 
the first argument via the `^` operator, got: `"#{"id"}=?"`
...

There are possibilities to work around this error using a bit of metaprogramming. I strongly advise against it, though.

Sobelow

The bigger the project, the more difficult it is to make sure there are always good practices applied during the development. It’s good to from time to time use a security-focused static analysis tool, like Sobelow to raise the application’s security.

It won’t fix problems for you, but you’re going to know about the potential vulnerabilities. And awareness is always a great place to start.

Let’s run Sobelow in our example project:

$ cd my_app
$ mix sobelow

##############################################
#                                            #
#          Running Sobelow - v0.11.1         #
#  Created by Griffin Byatt - @griffinbyatt  #
#     NCC Group - https://nccgroup.trust     #
#                                            #
##############################################

SQL.Query: SQL injection - Low Confidence
File: lib/my_app/blog/posts.ex
Line: 37
Function: get_by_title:35
Variable: search_input

-----------------------------------------------
...

The report is simple and self-descriptive - points you where exactly to look for each problem.

Don’t belittle green-indicated ones. It means just low confidence of the insecurity (due to not finding a connection between user input and sink by the analysis). It doesn’t mean they’re safe for sure or that some future code changes won’t by chance allow exploitation.

As in the report, we find the vulnerable part of the code in lib/my_app/blog/posts.ex on line 37 in function get_by_title/1.

35 | def get_by_title(conn) do
36 |   search_input = conn.params["search_input"]
37 |   query = "SELECT * FROM posts WHERE title LIKE '%#{search_input}%' AND published=1"
38 | 
39 |   Ecto.Adapters.SQL.query!(conn, query)
40 | end

Fixing it is still up to us, but thanks to the earlier section about secure Ecto Queries development, I believe it’s at your fingertips.

Besides the SQL Injection vulnerabilities, Sobelow detects a lot of other types of security issues:

• Insecure configuration
• Known-vulnerable Dependencies
• Cross-Site Scripting
• SQL injection
• Command injection
• Code execution
• Denial of Service
• Directory traversal
• Unsafe serialization

You know, he’s a really good ally.

If you’ve got a CI pipeline integrated with your project, then it’s a great idea to join Sobelow into the flow.

Final tips 🏁

• Always use query bindings or Ecto's query DSL to construct queries instead of concatenating user input directly into SQL strings.
• Avoid usage of raw SQL strings
• Validate and sanitize all user input before using it in queries.
• Use static code analysis tools configured to catch potential vulnerabilities
• Regularly update Elixir, Ecto, and related dependencies to benefit from security fixes and patches.

FAQ

What is SQL Injection and why is it a concern in web development?

SQL Injection is a type of security vulnerability that occurs when untrusted data is incorporated into a SQL query. It's a major concern because it can allow attackers to access and manipulate databases, leading to data breaches and loss of data integrity.

How does Elixir and its ecosystem combat SQL Injections?

Elixir, particularly through its Ecto library, mitigates SQL Injection risks by employing parameterized queries, where SQL code is separated from data. This approach prevents attackers from altering the intent of queries by injecting malicious SQL.

Can Ecto completely eliminate SQL Injection vulnerabilities?

While Ecto significantly reduces the risk of SQL Injections by using parameterized queries, developers still need to be cautious, especially when using raw SQL or constructing dynamic queries.

What are parameterized queries and how do they prevent SQL Injection?

Parameterized queries separate SQL code from data values, which ensures that user input is treated as data, not executable code, thus preventing SQL Injection.

What is the role of static analysis tools like Sobelow in Elixir projects?

Sobelow is a security-focused static analysis tool for Elixir that helps identify potential vulnerabilities, including SQL Injections, in your codebase, contributing to safer Elixir applications.

How should developers handle user input in Elixir applications to prevent SQL Injection?

Developers should validate and sanitize all user input, use Ecto's query DSL or query bindings, and avoid directly concatenating user input into SQL queries to safeguard against SQL Injection.

What are some best practices for securing Elixir applications against SQL Injections?

Best practices include using parameterized queries, validating and sanitizing user input, employing static analysis tools like Sobelow, and regularly updating dependencies to address security vulnerabilities.