Parameterized Queries: The Primary Defense

Parameterized Queries: The Primary Defense

Parameterized queries, also known as prepared statements, separate SQL logic from data, making injection impossible. Instead of building queries through string concatenation, parameterized queries use placeholders for user input:

# Secure Python implementation with parameterized queries
def get_user_secure(username, password):
    query = "SELECT * FROM users WHERE username = %s AND password = %s"
    cursor.execute(query, (username, password))
    return cursor.fetchone()

# Even with malicious input like "admin' OR '1'='1", the query remains safe
# The database treats the entire input as a string value, not SQL code

Different languages implement parameterized queries differently:

// Java with PreparedStatement
String sql = "SELECT * FROM users WHERE email = ? AND status = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, userEmail);
pstmt.setInt(2, statusCode);
ResultSet rs = pstmt.executeQuery();

// C# with SqlCommand parameters
using (SqlCommand cmd = new SqlCommand("SELECT * FROM products WHERE category = @category AND price < @price", conn))
{
    cmd.Parameters.AddWithValue("@category", userCategory);
    cmd.Parameters.AddWithValue("@price", maxPrice);
    SqlDataReader reader = cmd.ExecuteReader();
}