An SQL injection is an attack on a web system with a database. The attack is done by injecting unexpected commands into parameters. Don’t worry, I’ll explain that later in detail.
After reading this article you will understand what the effect of being vulnerable to SQL injections can be, how to execute SQL injections yourself, and how to prevent them. Let’s get started!
Why it Matters
SQL Injection attacks are so common nowadays that I just give you this list with some of the biggest known attacks. Please note that SQL injections don’t necessarily break anything and thus they might not always be noticed. Most of the time, the attacker steals data.
- Injection flaws are part of the OWASP Top-10, meaning it's recognized as a common vulnerability
- 1998: Jeff Forristal explains the issue (source, original).
- 2009: 130 million credit card numbers were stolen from Heartland Payment Systems, 7-Eleven, and others (source). They claimed $130 million in losses (source).
- 2010: 110,000 credit card numbers were stolen from Twin America LLC (source).
- 2011: 50,000 emails and passwords were stolen from Sony (source). Sony claims that this created costs of $605,000 USD (source).
- 2015: The personal data of 157,000 people was stolen from TalkTalk (source).
- 2016: The data of 200,000 voters in Illinois were stolen (source).
- 2020: 8.3 million user names and password hashes were stolen from FreePik (source)
In the worst case, the attacker steals the data and sells it. After that, they corrupt your data in a way that you don’t fix it / use the backups.
How are SQL injection attacks executed?
Imagine you have a website with a login form. Leaving some important bells and whistles away, something like this happens:
SELECT *
FROM users
WHERE USER = '**[username]**'
AND password = '**[password]**'
The attacker can change the username to admin' OR '1'='1 which then gives the query:
SELECT *
FROM users
WHERE USER = '**admin' OR '1'='1**'
AND password = 'secret'
This means the query will look for two conditions:
- The username is equal to admin
- OR the password is equal to secret
Instead, it should have been looking for the combination (username is admin AND the password is equal to secret).
What happened here is that the attacker injected SQL into the query. This changed the logic of access control and thus let the attacker login as admin.
How can I prevent SQL injections?
Input validation and proper escaping are the keys to prevent SQL injections. Preventing SQL injections in this case also helps the poor users who actually wanted to have a ' within their username/password. Never blindly trust user input. Don’t use simple string concatenation to build SQL queries with parameters supplied by the user.
You might be tempted to think that removing the quotes is enough. This, however, might cause problems within your application. Then you could escape the quotes. This is certainly a good step, but you have to watch out that the escaping isn’t broken and that you don’t forget anything.
Instead of doing this manually, you should use parameter binding. In Python, it looks like this:
from sqlalchemy.sql import text
# Create a connection conn
stmt = text(
"""SELECT * FROM users
WHERE user = :username AND password = :password"""
)
conn.execute(stmt, prams={"user": "foo", "password": "bar"})
Another way to prevent SQL injections is to use an ORM which does the input sanitation for you:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = "users"
name = Column(String, primary_key=True)
password = Column(String)
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
user = session.query(User).filter_by(name="foo").filter_by(password="bar").first()
Here we pass the name and password parameters to the ORM (SQL Alchemy). It takes care of sanitizing the name and the password.
The third option is to create a prepared statement. This is also using parameter binding but on the side of the SQL server. I think those are typically harder to use from a developers' perspective.
Doing any of those three options is nice, but it is not enough. You want to be sure that you or anybody else doesn’t accidentally add code that is vulnerable to SQL injections. For this reason, static application security testing tools (SAST) like bandit check for potential SQL injection vulnerabilities (source). Insert that in your CI pipeline and stay safe!
The principles are the same for any programming language, but you might be interested in seeing more concrete advice in the language that is relevant to you. Have a look at bobby-tables.com.
Creative SQL Injections
There are some SQL Injections that are less trivial than the before mentioned examples.
Simplifying Queries
An attacker might not know exactly how a query continues. So the attacker
inserts --
at the end to make the rest of the query a comment.
Information Gathering
An attacker might not know the structure of the database. However, many
databases have a special table that contains the information. For MySQL,
MariaDB, and Postgres it is called information_schema
.
It’s possible to restrict access to that table (example). You should do it from a defense-in-depth perspective.
Order By
You might be tempted to think that the following SQL query is secure because the user input is just in the ORDER BY clause:
SELECT book_title FROM books ORDER BY {user_input}
where the developer expects user_input
to be either sales
or
average_review
. However, an attacker could change user_input
to this:
CASE WHEN
(SELECT 1 FROM users
WHERE username = "admin"
AND SUBSTRING(password, 1, 1) = "a"
) = 1
THEN sales
ELSE average_review
END ASC
This way the attacker can get the password hash of the admin user. Character by character. Just by looking at how the sorting changes.
See also
I love Tom Scott and Computerphile and they made a video about the topic!
What’s next?
In this series about application security (AppSec) we already explained some of the techniques of the attackers π and also techniques of the defenders π:
- Part 1: SQL Injections π
- Part 2: Don’t leak Secrets π
- Part 3: Cross-Site Scripting (XSS) π
- Part 4: Password Hashing π
- Part 5: ZIP Bombs π
- Part 6: CAPTCHA π
- Part 7: Email Spoofing π
- Part 8: Software Composition Analysis (SCA) π
- Part 9: XXE attacks π
- Part 10: Effective Access Control π
- Part 11: DOS via a Billion Laughs π
- Part 12: Full Disk Encryption π
- Part 13: Insecure Deserialization π
- Part 14: Docker Security π
And this is about to come:
- CSRF π
- DOS π
- ReDoS π
- Credential Stuffing π
- Cryptojacking π
- Single-Sign-On π
- Two-Factor Authentication π
- Backups π
Let me know if you are interested in more articles around AppSec / InfoSec!