SQL Attacks

Crime Continues — key idea

Many (if not most) computer crimes are digital versions of old crimes.

Examples of "old crimes" that went digital

Pyramid schemes, Vandalism, Theft, Junk mail/spam, False advertising, Fraud ("This is the IRS…").

What changed regarding access?

You don't need to physically raid a location to get info; you don't need to be in the same country; governments have stepped up hacking each other.

SQL Injection historical context (Side Note)

SQL injection has always been a possibility; what changed was the ability to do that injecting online.

What changed regarding rules?

Before: the country you worked in made the laws. Now: with content available anywhere, countries (including US) attempt to enforce laws on content from elsewhere.

SQL Injection Source (Citation)

Clarke, J. (2012). SQL Injection Attacks and Defense (2nd ed.). Syngress.

Why are most queries not static strings?

We build queries dynamically based on input.

Examples of inputs driving dynamic queries

Searching for a name in contacts; adding a user; removing a user; Authentication (are you really you?).

SQL Injection definition

Using input to make a dynamic SQL statement do something it shouldn't.

SQL Injection "shape" (concept)

Take something that should be simple and make it complex.

E-commerce search box example query

SELECT * FROM PRODUCTS WHERE Name LIKE '%%';

E-commerce expected input example

"iPhone"

E-commerce manipulated input example

"iPhone' OR Name LIKE '"

Resulting injected query (Search)

SELECT * FROM PRODUCTS WHERE Name LIKE '%iPhone' OR Name LIKE '%';

Effect of 'OR' in the search injection

OR means that either clause matching keeps the row.

Effect of "Name LIKE '%'"

It matches anything.

Search injection advanced example input

"iPhone' OR 1=1 OR Name LIKE '"

Example Product Table Columns

Name, Price, Available, Availability_date, Quantity

Example Product Table Data (Rows)

iPhone 17 ($799), iPad Pro M5 (No stock), Macbook Air M5 (No stock), Pixel 10 Pro ($1099), Pixel 10 Fold ($1799).

Result of search injection attack (Slide 14)

1 query shows everything (including unreleased products); A lot of data returned (slows the server down).

Authentication Injection — How it works

The web page sends username/password to the server to validate.

Authentication intended query

SELECT COUNT(*) FROM USERS WHERE User ='' AND Password ='';

Authentication logic check

We look for a COUNT > 0.

Why use COUNT for authentication?

COUNT is all we need; we shouldn't need the actual results because we supplied them.

Authentication manipulated input example

Put "me' OR '1'='1" for the password.

Resulting injected query (Authentication)

SELECT COUNT(*) FROM USERS WHERE User='me' AND Password='me' OR '1'='1';

Why '1'='1' is dangerous

It is always true.

Why OR breaks authentication logic

OR makes the whole clause true.

Operator Precedence in SQL (Footnote)

AND has precedence over OR, so OR is the last thing evaluated.

Result of Authentication Injection

COUNT(*) is > 0; the attacker authenticates without knowing a username or password.

Password storage warning

Passwords shouldn't be stored as "plain text" (the way you typed them).

How passwords should be stored

Using a 1-way hash (a value you can't easily turn back into the original password).

Linux password storage note

On Linux systems you can say "sudo /etc/shadow" and see all of the (hashed) passwords.

Can you log in with a retrieved hashed password?

No, because the hashed value would be hashed again into something else before checking it.

Breach consequence note

This is why most breaches are of the "change your password soon" variety rather than full-on access.

Real-life Attack: Sony (2011)

Compromised 77 million PlayStation accounts; Cost Sony about $170 million.

Real-life Attack: Heartland Payments (2008)

Compromised 130 million credit/debit card numbers; Cost $200 million and 50% of stock price.

Real-life Attack: Yahoo! (2012)

Leaked 500 million email addresses and passwords.

Yahoo! breach specific vulnerability

The leaked passwords were unencrypted.

Web Request Step 1: Browser

The public part; where you fill out fields, click buttons, etc.

Web Request Step 2: Server

The hidden parts; where user data turns into database actions (often dynamically building SQL).

Web Request Step 3: Database

The part we're familiar with; returns results to server, then to user.

Vulnerable Java Code Example

String query = "SELECT * … LIKE '%" + text + "%'"; // Blindly trusting the text field.

Where to guard against SQL Injection?

The database will run any valid query, so it's up to us (the code) to sanitize the input.

Sanitizing Code Example (Java)

if (query.contains("'")) { // Error: invalid query }

Responsibility for sanitation (Complexity)

As architectures get complex, it's easy to think "the layer before/after me will do it," leading to no validation.

Two parts of the solution

1. Detection, 2. Avoidance.

Oracle "escape" characters (Detection)

' (string/data), | (pipe/run function), , (comma), . (dot), * (star), / (slash), " (quote).

Whitelist strategy

"Only these characters are allowed."

Whitelist vs Blacklist

Blacklists require knowing all characters that could cause problems; Whitelists allow only known safe ones.

Regex […] meaning

Match any ONE of those characters.

Regex example: a[bc]d

Matches: abd, acd. Does NOT match: abcd, add.

Regex [^…] meaning

Match anything BUT those characters.

Regex example: a[^bc]d

Matches: add, a7d. Does NOT match: abd, acd.

Regex ranges meaning

[a-d] matches a, b, c, or d.

Regex example: 9[0-9]

Matches: 99, 90, 92. Does NOT match: 9Q, 9a.

Whitelist regex example: [^0-9A-Za-z ]

Matches anything that is NOT a space, a letter, or a number.

Interpretation of Whitelist Regex match

If the input matches the regex (meaning it found a bad character), the input is INVALID.

Avoidance Principle

Write code that assumes less about the input.

Avoidance Example (Authentication Count)

0 means unauthorized; 1 means authorized; Anything else is an error!

Attacker trick: Bad Queries

Intentionally creating a bad query (e.g., invalid attribute name) to see error results.

What raw error messages reveal

Which database is used (Oracle, MySQL), table names, and attribute names.

Error Handling Solution

Turn error messages into something less useful (hide stack traces).

Four ways to Audit Code

Code reviews, Static code analysis, Penetration (pen) testing, Automated tests.

Code Reviews

Experienced developers look at code; spot issues/best practices.

Static Code Analysis

Tools look for known issues (security, memory errors); beyond IDE checks; now uses AI.

Penetration (pen) testing

Developers try to break into your system.

Automated tests

Avoids introducing issues later (regressions).