SQL Injection via SQLite's %s Format Specifier in LR2_statlong.cpp ReadPlayerScore()

Introduction

The LR2/LR2_statlong.cpp file handles player score retrieval from a SQLite database, but a subtle flaw in the ReadPlayerScore() function at line 42 created a critical SQL injection vulnerability. The issue? A single character: %s instead of %q in a call to sqlite3_snprintf.

This is a deceptively dangerous pattern because developers familiar with standard C printf naturally reach for %s when formatting strings. However, SQLite's sqlite3_snprintf provides a special %q specifier designed specifically for SQL string escaping — and using %s instead means user-controlled input flows directly into the query without any protection against SQL metacharacters.

The Vulnerability Explained

The Vulnerable Code

Here's the problematic line from LR2_statlong.cpp at line 42:

sqlite3_snprintf(256, str, "SELECT * FROM player WHERE id = \'%s\'", id.body);

At first glance, this looks reasonable — it constructs a SQL query by formatting the player ID into a SELECT statement. The developer even used sqlite3_snprintf (SQLite's own formatting function) rather than raw sprintf, which suggests security awareness. But there's a critical misunderstanding at play.

Why %s Is Dangerous in sqlite3_snprintf

In SQLite's snprintf implementation:

• %s — Inserts the string verbatim, exactly like standard printf. No escaping whatsoever.
• %q — Escapes single quotes by doubling them (' becomes ''), which is the SQLite standard for escaping within string literals.

When %s is used, a player ID containing a single quote (') will terminate the SQL string literal prematurely, allowing everything after it to be interpreted as SQL commands.

Attack Scenario

Consider an attacker who controls the player ID value — through a crafted replay file, manipulated network data, or a modified configuration file. If they set the player ID to:

'; DROP TABLE player; --

The resulting query becomes:

SELECT * FROM player WHERE id = ''; DROP TABLE player; --'

This would:
1. Execute a valid (empty) SELECT statement
2. Drop the entire player table
3. Comment out the trailing quote

Even more insidious, an attacker could use:

' OR '1'='1

Which produces:

SELECT * FROM player WHERE id = '' OR '1'='1'

This would return all player records, potentially leaking sensitive statistics for every player in the database.

Real-World Impact

In the context of LR2 (a rhythm game client), the ReadPlayerScore function loads player statistics from a local SQLite database. An attacker who can influence the id parameter — whether through crafted replay files shared online, network interception, or configuration manipulation — could:

• Extract all player data from the database
• Modify scores and statistics
• Corrupt or destroy the database entirely
• Potentially execute other SQLite commands depending on database permissions

The Fix

The fix is elegant in its simplicity — a single character change that activates SQLite's built-in SQL escaping:

Before (Vulnerable)

sqlite3_snprintf(256, str, "SELECT * FROM player WHERE id = \'%s\'", id.body);

After (Fixed)

sqlite3_snprintf(256, str, "SELECT * FROM player WHERE id = \'%q\'", id.body);

How %q Solves the Problem

With %q, the same malicious input '; DROP TABLE player; -- now produces:

SELECT * FROM player WHERE id = '''; DROP TABLE player; --'

Wait — that still looks wrong. Let me be precise. The %q specifier doubles every single quote in the input, so ' becomes ''. The actual output would be:

SELECT * FROM player WHERE id = '''; DROP TABLE player; --'

Actually, let me trace through this carefully. The input '; DROP TABLE player; -- contains one single quote at position 0. With %q, that quote is doubled:

SELECT * FROM player WHERE id = ''''; DROP TABLE player; --'

No — the correct behavior is:

• Input: '; DROP TABLE player; --
• After %q escaping: ''; DROP TABLE player; --
• Full query: SELECT * FROM player WHERE id = '''; DROP TABLE player; --'

The key insight is that within the SQL string literal, '' is interpreted as a literal single quote character, not as a string terminator. So the entire malicious payload remains trapped inside the string literal and is treated as a (weird) player ID value, never as SQL commands.

Verification Through Testing

The PR includes a comprehensive regression test that validates this behavior:

static std::string build_query_safe(const char* id) {
    char str[256];
    sqlite3_snprintf(256, str, "SELECT * FROM player WHERE id = '%q'", id);
    return std::string(str);
}

The test suite verifies that adversarial inputs like '; DROP TABLE player; -- and ' OR '1'='1 produce valid, non-injectable SQL when using %q.

Prevention & Best Practices

1. Prefer Parameterized Queries (Prepared Statements)

While %q fixes this specific vulnerability, the gold standard is parameterized queries:

sqlite3_stmt *stmt;
sqlite3_prepare_v2(scoreDB, "SELECT * FROM player WHERE id = ?", -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, id.body, -1, SQLITE_STATIC);

This approach completely separates SQL logic from data, making injection impossible regardless of input content.

2. Know Your Format Specifiers

When using sqlite3_snprintf, always use:
- %q — for values inside single quotes (escapes ' to '')
- %Q — like %q but wraps in quotes and handles NULL (produces NULL literal for null pointers)
- Never %s — for any user-controlled data in SQL context

3. Input Validation at Boundaries

Validate player IDs at the point of entry. A legitimate player ID likely follows a predictable format (alphanumeric, specific length). Reject inputs that don't match before they ever reach the database layer.

4. Static Analysis Rules

Configure your static analysis tools to flag:
- Any sqlite3_snprintf call containing %s where the format string also contains SQL keywords
- Any string concatenation used to build SQL queries
- Missing parameterization in database query construction

5. Security Standards Reference

• CWE-89: Improper Neutralization of Special Elements used in an SQL Command
• OWASP A03:2021: Injection
• CERT C: STR02-C — Sanitize data passed to complex subsystems

Key Takeaways

• SQLite's %s is NOT %q: Unlike some database libraries that auto-escape, sqlite3_snprintf's %s behaves identically to standard printf — it provides zero SQL protection.
• The ReadPlayerScore() function accepted id.body from an external source: Any data path that influences this value (replay files, network, config) becomes an attack vector.
• A one-character fix (s → q) eliminated a critical vulnerability: This demonstrates how security often hinges on subtle API choices that are easy to overlook in code review.
• sqlite3_snprintf is NOT inherently safe: Using SQLite's own formatting function creates a false sense of security if you don't use the SQL-specific format specifiers.
• Regression tests should encode security invariants: The test suite explicitly verifies that adversarial inputs cannot break out of SQL string literals, guarding against future regressions.

Conclusion

This vulnerability in LR2_statlong.cpp is a textbook example of how a single-character mistake can create a critical security flaw. The developer was already using SQLite's own formatting function — they were so close to the right solution — but %s and %q have fundamentally different security properties that aren't obvious without deep SQLite API knowledge.

For any developer working with SQLite in C/C++: burn this rule into memory — never use %s in sqlite3_snprintf for values that will appear in SQL queries. Use %q, %Q, or better yet, switch to parameterized queries with sqlite3_prepare_v2 and sqlite3_bind_* functions. Your future self (and your users' data) will thank you.