SQL Injections ๐Ÿ˜ˆ

What they are, how to use them, and how to prevent them

Martin Thoma
Oct 12, 2020 ยท 6 min read
Image for post
Image for post
An SQL injection attack. Yes, they are that easy. The attacker chose the user โ€œadminโ€™ OR โ€˜1โ€™=โ€™1โ€ to change the original query. Image by Author.

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.

  • 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={"username": "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, user_input could now be:

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!

Thank you!

To my colleague Mats at Netlight who found an error in one of the code snippets and let me know about it ๐Ÿค—

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 ๐Ÿ˜‡. We also covered parts of the OWASP Top 10 ๐Ÿ:

And this is about to come:

  • CSRF ๐Ÿ˜ˆ
  • DOS ๐Ÿ˜ˆ
  • Credential Stuffing ๐Ÿ˜ˆ
  • Cryptojacking ๐Ÿ˜ˆ
  • Single-Sign-On ๐Ÿ˜‡
  • Two-Factor Authentication ๐Ÿ˜‡
  • Backups ๐Ÿ˜‡
  • Disk Encryption ๐Ÿ˜‡

Let me know if you are interested in more articles around AppSec / InfoSec!

Image for post
Image for post

๐Ÿ‘‹ Join FAUN today and receive similar stories each week in your inbox! ๏ธ Get your weekly dose of the must-read tech stories, news, and tutorials.

Follow us on Twitter ๐Ÿฆ and Facebook ๐Ÿ‘ฅ and Instagram ๐Ÿ“ท and join our Facebook and Linkedin Groups ๐Ÿ’ฌ

Image for post
Image for post

If this post was helpful, please click the clap ๐Ÿ‘ button below a few times to show your support for the author! โฌ‡

FAUN

The Must-Read Publication for Creative Developers & DevOps Enthusiasts

Sign up for FAUN

By FAUN

Mediumโ€™s largest and most followed independent DevOps publication. Join thousands of aspiring developers and DevOps enthusiastsย Take a look

By signing up, you will create a Medium account if you donโ€™t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Martin Thoma

Written by

Iโ€™m a Software Engineer with focus on Data Science, Machine Learning. I have over 10 years of experience with Python. https://www.linkedin.com/in/martin-thoma/

FAUN

FAUN

The Must-Read Publication for Creative Developers & DevOps Enthusiasts. Mediumโ€™s largest DevOps publication.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store