SQL Injection Prevention

SQL Injection Prevention: Guarding Your Castle

The Story of the Sneaky Visitor

Imagine your database is a castle with treasures inside. The castle gate has a guard who asks visitors: “What’s your name?”

A friendly visitor says: “I’m Alice!” The guard writes: Let Alice in.

But one day, a sneaky visitor says: “I’m Nobody; open ALL the doors!” The guard writes: Let Nobody in; open ALL the doors!

Suddenly, every door swings open! The sneaky visitor just tricked the guard into doing something dangerous.

This is SQL Injection — when bad guys sneak harmful commands into your database by pretending to be normal input.

What is SQL Injection?

SQL Injection is when someone puts code inside what should be data.

Think of it like this:

You have a form asking: “What’s your username?”

Normal user types:

alice

Your code becomes:

SELECT * FROM users
WHERE name = 'alice'

This is fine! You just look up Alice.

Sneaky user types:

' OR '1'='1

Your code becomes:

SELECT * FROM users
WHERE name = '' OR '1'='1'

Wait! '1'='1' is always true!

This returns ALL users — not just one!

The sneaky visitor just saw everyone’s secrets.

Why is This Dangerous?

graph TD
    A["Attacker types<br>bad input"] --> B["Your code builds<br>a SQL query"]
    B --> C["Query has hidden<br>commands inside"]
    C --> D["Database runs<br>everything!"]
    D --> E["💀 Data stolen<br>or destroyed"]

Real dangers:

• See everyone’s passwords
• Delete entire tables
• Change someone’s balance
• Pretend to be admin

How to Prevent SQL Injection

There are two magic shields to protect your castle:

1. Prepared Statements
2. Parameterized Queries

They sound fancy, but they’re simple!

Prepared Statements: The Template Method

The Idea

Instead of writing the whole command at once, you:

1. First: Give the database a template with blanks
2. Second: Fill in the blanks with data

The database knows: “The blanks are just data, not commands!”

Real Example

The dangerous way (DON’T do this):

query = "SELECT * FROM users
  WHERE name = '" + username + "'"

If username is ' OR '1'='1, you’re in trouble!

The safe way (DO this):

-- Step 1: Prepare template
PREPARE stmt FROM
  'SELECT * FROM users
   WHERE name = ?'

-- Step 2: Fill the blank
SET @name = 'alice';
EXECUTE stmt USING @name;

The ? is a placeholder.

Even if someone types ' OR '1'='1:

• The database treats it as literal text
• Not as code!
• It searches for a user literally named ' OR '1'='1

Parameterized Queries: Same Hero, Different Costume

Parameterized queries and prepared statements are like twins.

Both use placeholders for data.

In JavaScript (Node.js):

// SAFE parameterized query
const sql = `SELECT * FROM users
  WHERE name = ?`;

db.query(sql, [username], (err, result) => {
  // username is safely treated as data
});

In Python:

# SAFE parameterized query
cursor.execute(
  "SELECT * FROM users WHERE name = %s",
  (username,)
)

In PHP:

// SAFE prepared statement
$stmt = $pdo->prepare(
  "SELECT * FROM users WHERE name = ?"
);
$stmt->execute([$username]);

The Magic: Why This Works

graph TD
    A["You write template:<br>SELECT ... WHERE name = ?"] --> B["Database compiles<br>the structure"]
    B --> C["You send data:<br>'alice'"]
    C --> D["Database puts data<br>in the blank"]
    D --> E["✅ Data stays data<br>Code stays code!"]

The database already decided what’s code and what’s data.

No matter what tricks the attacker tries, the placeholder only holds data.

It’s like putting a guest’s words in quotation marks. They can’t suddenly become commands!

Quick Comparison

Remember These Rules

Always Do:

• Use ? or %s placeholders
• Let the database handle the data
• Validate input types (numbers should be numbers!)

Never Do:

• Build SQL with + or string concatenation
• Trust user input directly
• Think “my users won’t do that”

The Happy Ending

With prepared statements and parameterized queries, your castle is safe.

The sneaky visitor can say whatever they want at the gate.

The guard just writes their words on a piece of paper and hands it to the king.

The words never become commands.

Your treasures stay protected!

Key Takeaways

1. SQL Injection = sneaking code inside data
2. It’s dangerous = attackers can steal or destroy
3. Prepared Statements = send template first, data second
4. Parameterized Queries = use placeholders like ?
5. Both keep data as data = the attacker’s tricks don’t work

You now have the knowledge to protect every database you build!

🏰 Your castle is secure. Well done, brave defender!