Understanding SQL Injection: Prevention Best Practices

#Introduction

SQL injection remains one of the most common and dangerous web application vulnerabilities. Despite being well-understood for decades, it continues to appear in the OWASP Top 10 list. In this article, we’ll explore how SQL injection works and demonstrate proper prevention techniques.

#What is SQL Injection?

SQL injection occurs when an attacker manipulates SQL queries by injecting malicious input into application fields. This can lead to unauthorized data access, data modification, or even complete system compromise.

#Vulnerable Code Example

Here’s a classic example of vulnerable code in Python:

python
Copy code
import sqlite3

def get_user(username, password):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    # VULNERABLE: String concatenation
    query = f"SELECT * FROM users WHERE username='{username}' AND password='{password}'"
    cursor.execute(query)

    user = cursor.fetchone()
    conn.close()
    return user

## Attacker input
malicious_username = "admin' --"
result = get_user(malicious_username, "anything")

In this example, an attacker can input admin' -- as the username, which transforms the query into:

sql
Copy code
SELECT * FROM users WHERE username='admin' --' AND password='anything'

The -- comments out the rest of the query, bypassing password authentication entirely.

#Secure Implementation

The correct approach uses parameterized queries:

python
Copy code
import sqlite3

def get_user_secure(username, password):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    # SECURE: Parameterized query
    query = "SELECT * FROM users WHERE username=? AND password=?"
    cursor.execute(query, (username, password))

    user = cursor.fetchone()
    conn.close()
    return user

Here’s a similar example in Node.js with PostgreSQL:

javascript
Copy code
const { Pool } = require('pg');
const pool = new Pool();

// SECURE: Using parameterized queries
async function getUserSecure(username, password) {
    const query = 'SELECT * FROM users WHERE username=$1 AND password=$2';
    const result = await pool.query(query, [username, password]);
    return result.rows[0];
}

#Key Prevention Strategies

1. Always use parameterized queries - Never concatenate user input into SQL strings
2. Input validation - Validate and sanitize all user inputs
3. Least privilege - Database accounts should have minimal necessary permissions
4. Web Application Firewall (WAF) - Add an additional layer of protection
5. Regular security testing - Include SQL injection tests in your security assessment process

#Conclusion

SQL injection is preventable with proper coding practices. By consistently using parameterized queries and following secure development principles, you can eliminate this vulnerability from your applications. Remember: never trust user input, and always validate and parameterize database queries.

For more information on securing your applications, contact the Whitespots team for a comprehensive security assessment.