SQL Injection: How it Works and How to Prevent it

SQL injection (or SQLi for short) is one of the oldest tricks in the hacker handbook, but it's still incredibly common and incredibly dangerous. In short, it’s about tricking a database into revealing things it shouldn’t.

In this article, I’ll walk you through what SQL injection actually is, the different ways attackers use it, some real-world examples that caused serious damage, and perhaps most importantly, how you can prevent it. Whether you’re a developer or just curious about how things break on the internet, you’ll come away with a solid understanding of SQLi (without falling asleep halfway through, I promise).

What Is SQL Injection? 

SQL injection is a type of attack that happens when someone finds a way to mess with the SQL queries your app sends to the database. Normally, those queries are supposed to do things like fetch a user’s profile or update a product listing. But with SQLi, an attacker can inject malicious bits of SQL code into your input fields (like search bars or login forms), and suddenly the database is doing exactly what they want instead.

Why does this work? 

Because somewhere along the line, the application is trusting user input a little too much, and treating it like harmless text rather than potential executable code. It’s like letting someone fill out a form, and then pasting what they wrote straight into a command console.

Why is it bad?

SQL injection is dangerous because it can be used to view or steal private data (like usernames, passwords, or credit card information), bypass login screens, delete or modify data, and even take full control of the database server in worst-case scenarios.

So yes, SQLi is bad, and it is not even a recent security concern, but you would be surprised to see how many apps aren’t properly protected against it.

Types of SQL Injection

Depending on how the attacker interacts with your application and what kind of feedback they get, SQLi comes in a few different flavours. There are 3 main types you might run into:

In-band SQLi

This is the most straightforward type. The attacker sends a malicious SQL query and gets the results right back through the application. It’s fast, and often very effective.

• Error-based SQLi: This technique relies on the database helpfully returning error messages. These errors can reveal a ton of useful info, like table names or the structure of queries, which makes it easier for the attacker to plan their next move.

• Union-based SQLi: Here, the attacker uses the UNION operator to combine their own query with the one your app is running. It’s a clever way to pull extra data out of the database and sneak it into the response.

Inferential SQLi (aka Blind SQLi)

This one’s sneakier. Instead of seeing the results of their query directly, the attacker watches how the application behaves to figure out what’s happening under the hood.

• Boolean-based (content-based) SQLi: The attacker tweaks the query with conditions that are either true or false (like 1=1 or 1=2) and observes how the page changes. Does it load normally? Show an error? Act weird?
• Time-based SQLi: This technique adds time delays into the query (e.g. WAITFOR DELAY '00:00:05') and uses the response time to infer whether a condition is true.

Out-of-band SQLi (aka when things get fancy)

This one’s less common, but still worth knowing about. Out-of-band SQLi doesn’t rely on immediate responses from the app. Instead, the attacker uses alternate channels like DNS or HTTP requests to extract data. It's usually reserved for situations where direct feedback isn’t possible but the database server has internet access (and in 90% of cases, it probably shouldn't).

Common SQL Injection Techniques

Okay, so we’ve learned about 3 types of SQL injection. But how do attackers pull this off in practice?

OR 1=1 attack

This one’s a classic. Imagine a login form where you’re supposed to enter your username and password. An attacker might enter something like this:

' OR 1=1 --

The SQL query ends up looking like:

SELECT * FROM users WHERE username = '' OR 1=1 --' AND password = '';

Since 1=1 is always true, the database returns all users, and the -- turns the rest of the query into a comment. If there’s no limit in place, the attacker might get logged in without even knowing a valid username.

Source: vmware

Comment injection

As seen just above, the -- or /* */ characters are used to comment out parts of a SQL statement. Attackers use this to remove any extra clauses that might break their injected payload. For example, if they don’t know the full structure of the original query, they can just chop it off and make it syntactically valid again.

Batch SQL statements

Some databases allow multiple SQL statements to be run in a single request, separated by a semicolon. This feature can be used by hackers to do all sorts of damage, like modifying data or even dropping tables if the application doesn’t restrict it.

'; DROP TABLE users; --

UNION-based attacks

By injecting a UNION SELECT statement, attackers can combine their malicious query with the legit one and retrieve results from other tables like sensitive user data, passwords, or credit card info. It’s essentially using an existing query to get bonus data.

Blind SQL injection

We’ve mentioned this before. Even when the app doesn’t show errors or return query results, attackers can still extract data bit by bit by observing behavior. It’s slower and more tedious, but it works. This is often automated using tools that can try hundreds of queries and time-based responses.

Stored SQL injection

The malicious SQL is saved to the database, like in a user profile or comment, and executed later when someone views that data.

Real-World SQL Injection Attacks

SQL injection might sound like some niche hacker stuff, but it's caused some very real damage over the years. And by damage, I mean millions of user records exposed, huge fines, and headlines that didn’t do marketing departments any favours.

Guess.com (2002)

This is an early example that made people start paying attention. A security researcher exploited a SQL injection flaw and gained access to over 200,000 customer records, including credit card details. The good news is that it was found and reported by an ethical hacker. 

Heartland Payment Systems (2009)

This was a massive breach affecting a payment processor and 130+ million credit card numbers were stolen. The attackers used SQL injection to get a foothold, then escalated from there. This one is often cited as one of the largest data breaches ever.

Yahoo! Voices (2012)

Attackers exploited a UNION-based SQL injection flaw and exposed 450,000 usernames and plain-text passwords. Yes, you read this right, just raw credentials sitting there in plain-text. That breach was especially embarrassing because Yahoo! was a major tech company, and storing unencrypted passwords is a serious “don’t”, even by 2012 standards.

TalkTalk (2015)

TalkTalk is a high-profile UK telecom provider that fell victim to a fairly basic SQL injection attack. Around 157,000 customer records were compromised, and the company was fined £400,000. One of the attackers was only 17-year-old.

Gab (2021)

Hacktivists used SQL injection to exfiltrate 70GB of data, including private posts and hashed passwords. The breach was politically charged, and the fallout sparked even more scrutiny into Gab’s overall security posture.

How to Prevent SQL Injection

Okay, so now that we’ve scared ourselves a little with all those real-world breaches, let’s talk solutions. The good news here is that preventing SQL injection isn’t rocket science. Most of the time, it’s about not trusting user input and sticking to best practices. Here's what you can do:

Use parameterized queries

This is the number one rule. Never build SQL queries by gluing strings together. Instead, use placeholders and pass user inputs as parameters. Most frameworks and libraries make this super easy.

For example in Node.js with pg (PostgreSQL):

client.query('SELECT * FROM users WHERE id = $1', [userId]);

This essentially tells the database, “Here’s the query structure, and here’s some data, please don’t mix them up.”

Use stored procedures smartly

Stored procedures can help, but only if they also avoid dynamic SQL. The idea is that SQL logic lives in the database, and your app just calls those safe, predefined chunks of logic.

If you want to learn to create, update, and execute functions and stored procedures, have a look at our Writing Functions and Stored Procedures in SQL Server course.

Validate and sanitize input

If your app expects a number, make sure it’s a number. Don’t just blindly accept everything a user throws at you. Use type checks, length limits, and allowlists (e.g., only allowing known good values) where possible.

Escaping characters (like quotes) can help too, but it’s not a replacement for parameterized queries.

Use a Web Application Firewall (WAF)

A WAF can act as a first line of defense, especially against known attack patterns. It can help block some malicious traffic before it even hits your app. Not foolproof but useful, it’s a little bit like a spam filter for your SQL.

Apply the least privilege principle

Your web app shouldn’t be connecting to the database with admin rights. Limit what each user or service can do. For example, if your app only needs to read data, don’t give it permission to delete tables. The less power it has, the less damage an injection can do.

SQL Injection Testing & Detection

Even if you think your code is safe, it’s worth testing it like an attacker would. SQL injection has a way of slipping through the cracks, especially in large codebases or legacy systems. There are tools and techniques that make detection a lot easier and that are quite fun to play with.

Manual testing

Sometimes the fastest way to spot a hole is to just poke at it. Try entering ' OR 1=1 -- or '; DROP TABLE users; -- into form fields, URLs, or any other input areas and see how the app reacts. If you get weird errors, unexpected data, or mysterious success messages, you may have found something sketchy.

Pro tip: Always test in a dev or staging environment. 

Automated scanners

There are plenty of tools that’ll do the poking for you. Some good ones:

• sqlmap: An open-source beast that automates detection and exploitation (in ethical testing contexts).
• Burp Suite: Great for web app security in general, with extensions for SQLi detection.
• OWASP ZAP: Free and beginner-friendly, built for finding all sorts of web vulnerabilities.

These tools simulate attacks, flag potential injection points, and can sometimes suggest fixes.

Log analysis

Another sneaky trick: monitor your database logs. Repeated failed queries, weird syntax patterns, or a surge of requests with ', --, or UNION SELECT are all red flags.

Conclusion

SQL injection isn’t going anywhere anytime soon, and it’s one of those threats that never gets old. It’s simple, it’s powerful, and if you’re not careful, it can cause some serious damage. The thing is, SQLi is totally preventable. By using parameterized queries, validating inputs, applying the principle of least privilege, and testing regularly, you can protect your applications and your users from the devastating impact of an SQL injection attack. 

Remember, no one expects perfection, but with a bit of proactive effort, you can keep your database safe and cross that off of your security checklist. And if you’re serious about SQL and want to showcase your skills to prospective employers, have a look at our SQL Associate Certification! It will show that you are capable of using SQL to extract appropriate data from a database, and use it to answer common data questions.