Exercise: SQL Injection Review

ch03-02-sql-injection-review
⭐⭐ intermediate ⏱️ 25 min

You've been asked to review a database query tool before it goes to production. The developer is new to security and made several classic mistakes. SQL injection vulnerabilities can lead to data breaches, data loss, and complete system compromise.

This exercise builds on your code review skills from Chapter 2, now with a security focus. SQL injection is consistently in the OWASP Top 10 - it's one of the most common and dangerous vulnerabilities in web applications.

Your task: Identify ALL security vulnerabilities, categorize them by severity, and propose secure alternatives using parameterized queries.

🎯 Learning Objectives

Thinking

Doing

💬 Discussion

  • How does SQL injection work? What allows it to happen?
  • Why is checking for "DROP" and "DELETE" not sufficient protection?
  • What's the fundamental problem with string concatenation in SQL?
  • How do parameterized queries prevent injection?
src/main.rs

💡 Hints

Hint 1

Look for string concatenation patterns like format!() or push_str() that include user input directly in SQL queries.

Hint 2

The blocklist approach (checking for "DROP" and "DELETE") can be bypassed. Consider: '; SELECT * FROM users WHERE role='admin' --

Hint 3

Issues to find:

  1. Name filter: SQL injection via string concatenation
  2. Email domain filter: SQL injection (no validation)
  3. Sort column: SQL injection (arbitrary column/expression)
  4. Sort order: Injection possible (only checks exact match)
  5. get_user: user_id is String, concatenated without validation
  6. update_nickname: Direct string concatenation
  7. Architecture: UPDATE tool on "read-only" server
⚠️ Try the exercise first! Show Solution 
#![allow(unused)]
fn main() {
// Secure implementation of search_users using parameterized queries
async fn search_users(pool: &DbPool, input: SearchUsersInput) -> anyhow::Result<Vec<User>> {
    let mut conditions = vec!["1=1".to_string()];
    let mut params: Vec<String> = vec![];

if let Some(name) = &amp;input.name {
    conditions.push(&quot;name LIKE ?&quot;.to_string());
    params.push(format!(&quot;%{}%&quot;, name));
}

if let Some(domain) = &amp;input.email_domain {
    conditions.push(&quot;email LIKE ?&quot;.to_string());
    params.push(format!(&quot;%@{}&quot;, domain));
}

// For ORDER BY, use an allowlist - can&#x27;t parameterize column names
let allowed_columns = [&quot;id&quot;, &quot;name&quot;, &quot;email&quot;];
let order_clause = match &amp;input.sort_by {
    Some(col) if allowed_columns.contains(&amp;col.as_str()) =&gt; {
        let direction = match &amp;input.sort_order {
            Some(o) if o.to_lowercase() == &quot;desc&quot; =&gt; &quot;DESC&quot;,
            _ =&gt; &quot;ASC&quot;,
        };
        format!(&quot; ORDER BY {} {}&quot;, col, direction)
    }
    _ =&gt; String::new(),
};

let query = format!(
    &quot;SELECT id, name, email, role FROM users WHERE {} LIMIT 100{}&quot;,
    conditions.join(&quot; AND &quot;),
    order_clause
);

// Build query with dynamic binding
let mut query_builder = sqlx::query_as::&lt;_, (i64, String, String, String)&gt;(&amp;query);
for param in &amp;params {
    query_builder = query_builder.bind(param);
}

let rows = query_builder.fetch_all(pool.as_ref()).await?;

Ok(rows.into_iter().map(|(id, name, email, role)| {
    User { id, name, email, role }
}).collect())
}


}


// Key security principles:
// - Never use string concatenation for SQL with user input
// - Blocklists can always be bypassed - use allowlists instead
// - Parameterized queries separate SQL structure from data
// - Defense in depth: read-only connections, least privilege, audit logging
// - Code comments don't enforce security - "read-only server" with UPDATE tool

🤔 Reflection

  • Why can't you parameterize ORDER BY column names?
  • What's the difference between escaping quotes and parameterized queries?
  • If the database user only has SELECT permission, is SQL injection still dangerous?
  • How would you test for SQL injection in an automated way?