Skip to main content

Understanding SQL Injection Through a Real-World Example

· 8 min read
Kristina Haynes
Kristina Haynes
DevOps Engineer & Full Stack Developer

SQL injection vulnerabilities can occur in unexpected places, including simple text inputs. This post examines how an apostrophe in a name exposed a SQL injection vulnerability and provides practical guidance on preventing these security issues through parameterized queries.

The Training Class Disaster​

Fresh out of college, I landed my first software engineering job. Like many companies hiring junior developers, they started us with a two-week training class designed to teach us basic programming skills and how to interact with a SQL database.

Our project seemed straightforward: build a C# application to manage a bookstore's inventory based on a detailed requirements document. The system needed to track books, authors, and most importantly, author royalties (payments) for books that were purchased. We worked hard, implemented all the features, and felt confident heading into our final demo.

Then came the moment that would horrify me.

During the demo in front of the whole company, someone in the audience asked us to demonstrate adding a new author to the system named "Conan O'Brien".

I watched in horror as our carefully crafted application crashed instantly in front of everyone.

The culprit? A single apostrophe.

History Repeats Itself​

That training class taught me a valuable lesson about SQL injection. Years later, I received an urgent support ticket about a user that was logging into our site and reported that they were seeing another user's account information.

After investigating, I discovered the root cause: the user's name contained an apostrophe (like O'Brian, O'Connor, or D'Angelo). The same SQL injection problem that I had witnessed during my training class also existed in this website's codebase.

Understanding the Vulnerability​

This section explains why apostrophes cause SQL injection vulnerabilities and why they pose a serious security risk.

The Basic SQL Query​

Here's what a typical login query looks like:

SELECT userID, userName, firstName, lastName
FROM Users
WHERE userName = 'inputted username'
  AND password = 'inputted password'

This seems harmless enough—until you understand how SQL handles apostrophes.

When Apostrophes Attack​

In SQL, single quotes (') are used to delimit string literals. When you have an apostrophe in user input like O'Brian, here's what actually gets sent to the database:

SELECT userID, userName, firstName, lastName
FROM Users
WHERE userName = 'O'Brian'
  AND password = 'password123'

From SQL's perspective, this query looks like:

  1. A string starting with 'O'
  2. Random text Brian (which it doesn't understand)
  3. Another string starting with 'Brian'

Result: Syntax error. Application crash.

The Real Danger: SQL Injection​

While our apostrophe crash was embarrassing, it was relatively harmless. The real threat is SQL injection attacks—and they can be devastating.

A Malicious Example​

Imagine a hacker enters these credentials:

  • Username: kristina
  • Password: x' DROP TABLE Users; --

The resulting SQL query becomes:

SELECT userID, userName, firstName, lastName
FROM Users
WHERE userName = 'kristina'
  AND password = 'x' DROP TABLE Users; --'

Let's parse what just happened:

  1. WHERE userName = 'kristina' - Valid condition
  2. AND password = 'x' - Valid condition (though it won't match)
  3. DROP TABLE Users; - Deletes the entire Users table
  4. -- - SQL comment that ignores everything after

In one login attempt, the attacker just deleted your entire user database.

More Sophisticated Attacks​

SQL injection can be used for:

Data Exfiltration:

' OR '1'='1' --

This bypasses authentication by making the WHERE clause always true.

Database Enumeration:

' UNION SELECT table_name, column_name FROM information_schema.columns --

Reveals your entire database structure.

Privilege Escalation:

'; UPDATE Users SET role='admin' WHERE userName='hacker'; --

Grants administrative access to any account.

The Fix: Parameterized Queries​

The solution is surprisingly simple: never concatenate user input directly into SQL queries. Instead, use parameterized queries (also called prepared statements).

C# Example (Unsafe vs Safe)​

❌ VULNERABLE CODE:

// DON'T DO THIS!
string query = $"SELECT * FROM Users WHERE userName = '{username}' AND password = '{password}'";
SqlCommand command = new SqlCommand(query, connection);

✅ SECURE CODE:

// DO THIS instead
string query = "SELECT * FROM Users WHERE userName = @username AND password = @password";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", password);

Other Languages​

Java (JDBC):

String query = "SELECT * FROM Users WHERE userName = ? AND password = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, username);
pstmt.setString(2, password);

Python (SQLite):

cursor.execute(
    "SELECT * FROM Users WHERE userName = ? AND password = ?",
    (username, password)
)

JavaScript (Node.js with MySQL):

connection.query(
    'SELECT * FROM Users WHERE userName = ? AND password = ?',
    [username, password],
    (error, results) => { /* ... */ }
);

PHP (PDO):

$stmt = $pdo->prepare('SELECT * FROM Users WHERE userName = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

Why Parameterized Queries Work​

When you use parameterized queries:

  1. Separation of Code and Data: The database knows which parts are commands and which are data
  2. Automatic Escaping: The database driver properly escapes special characters
  3. Type Safety: Parameters are strongly typed, preventing type confusion attacks
  4. Performance: Prepared statements can be cached and reused

Defense in Depth: Additional Security Layers​

While parameterized queries are essential, follow these additional best practices:

1. Input Validation​

// Validate username format
if (!Regex.IsMatch(username, @"^[a-zA-Z0-9_\-\.@]{3,50}$"))
{
    throw new ArgumentException("Invalid username format");
}

2. Principle of Least Privilege​

-- Database user should only have necessary permissions
GRANT SELECT, INSERT, UPDATE ON Users TO app_user;
-- Don't grant DROP, DELETE, or ALTER

3. Web Application Firewall (WAF)​

A Web Application Firewall (WAF) acts as a protective layer between your application and incoming traffic, analyzing HTTP requests to detect and block malicious patterns before they reach your application code.

What a WAF does:

  • Monitors and filters HTTP/HTTPS traffic
  • Detects common attack patterns including SQL injection, XSS, and CSRF
  • Blocks suspicious requests based on predefined rules and patterns
  • Provides logging and alerting for security incidents

Popular WAF solutions:

Example WAF rule to block SQL injection: A WAF can detect patterns like ' OR '1'='1, UNION SELECT, DROP TABLE, and other SQL keywords in request parameters, blocking the request before it reaches your database.

Important: While WAFs provide an additional security layer, they should complement—not replace—proper input validation and parameterized queries in your application code.

4. Regular Security Audits​

Use tools like:

5. Error Handling​

// DON'T expose database errors to users
try
{
    // Database operation
}
catch (SqlException ex)
{
    // Log detailed error internally
    logger.Error(ex);

    // Return generic error to user
    return "An error occurred. Please try again later.";
}

Real-World Impact​

SQL injection isn't just a theoretical threat. Major breaches include:

  • Heartland Payment Systems (2008): 130 million credit cards stolen
  • Sony Pictures (2011): 1 million accounts compromised
  • Yahoo (2012): 450,000 passwords leaked
  • British Airways (2018): £20 million GDPR fine

According to OWASP, injection attacks (including SQL injection) consistently rank in the Top 3 web application security risks.

Testing Your Application​

Here are some test inputs to verify your application handles special characters safely:

Names with Apostrophes:

  • O'Brian
  • D'Angelo
  • O'Connor
  • Mary's

SQL Injection Attempts:

  • ' OR '1'='1
  • '; DROP TABLE Users; --
  • admin' --
  • ' UNION SELECT NULL, NULL, NULL --

Edge Cases:

  • Empty strings
  • Very long inputs (>1000 characters)
  • Unicode characters (e.g., 测试用户)
  • Special SQL characters: %, _, \, ", ;

Lessons Learned​

Looking back on that training class disaster and the production incident, here are my key takeaways:

  1. Security isn't optional: It should be built in from day one, not added later
  2. Assume all input is malicious: Never trust user input, even from "trusted" sources
  3. Use ORMs carefully: While frameworks like Entity Framework help, you still need to understand SQL injection
  4. Test with real-world data: Names like O'Brian, José, or 北京 expose hidden bugs
  5. Security is everyone's responsibility: Not just the security team's job

Resources for Learning More​

Conclusion​

A simple apostrophe taught me one of the most valuable lessons in my career: never underestimate the importance of input validation and parameterized queries.

Whether you're building a simple bookstore application or a complex enterprise system, SQL injection vulnerabilities can have catastrophic consequences. The good news? The fix is straightforward: use parameterized queries, validate input, and follow security best practices.

Don't let an apostrophe bring down your production system. Learn from my mistakes, secure your code, and remember: if it can accept user input, it can be exploited.

Have you encountered SQL injection in the wild? Share your stories in the comments below!

Thanks to Little Bobby Tables for inspiring generations of developers to take SQL injection seriously.

Little Bobby Tables XKCD Source: XKCD #327