Parameters & SQL Injection Prevention

Level: Beginner

ℹ️ Where This Fits

SQL injection is the #1 database security vulnerability. Using SqlParameter properly prevents attackers from manipulating queries. This is essential for any production application handling user input.

ℹ️ What You'll Learn

• SqlParameter purpose: Safely bind values to SQL placeholders (prevents SQL injection)
• Parameter syntax: @paramName in SQL, add with .AddWithValue("@paramName", value)
• AddWithValue shorthand: cmd.Parameters.AddWithValue("@name", "Ravi") (auto-detects type)
• Add with explicit type: cmd.Parameters.Add("@id", SqlDbType.Int).Value = 101 (preferred)
• Multiple parameters: Each @placeholder gets one parameter
• Parameter names: Must start with @ in SQL, use @name1, @name2, @className
• SQL injection definition: Attacker inserts SQL code into input field (bypasses authentication/authorization)
• Injection example (DANGEROUS): "SELECT * FROM Students WHERE Name = '" + name + "'" - attacker can input ' OR '1'='1
• Safe parameterized query: "SELECT * FROM Students WHERE Name = @name" with parameter binding (no concatenation)
• Type safety: Specify SqlDbType.String, SqlDbType.Int, SqlDbType.DateTime (prevents type coercion attacks)
• Nullable handling: .AllowDBNull = true for nullable columns
• Output parameters: Capture return values from stored procedures (@outParam)
• Table-valued parameters: Pass array/list to stored procedure (advanced)
• School Management examples: Get student by name, filter by class/section, update fee status safely
• Common mistakes: String concatenation with user input (injection!), wrong SqlDbType, not setting parameter size
• Best practice: ALWAYS use parameters, never concatenate user input into SQL strings

SQL Injection Attack - DANGEROUS Example

⚠️ This is a VULNERABILITY

Never write code like this:

string name = getUserInput();
string query = "SELECT * FROM Students WHERE Name = '" + name + "'";
// If user enters: ' OR '1'='1
// Executed query becomes: SELECT * FROM Students WHERE Name = '' OR '1'='1'
// Result: Returns ALL students (authentication bypass!)

Parameterized Query - SAFE

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM Students WHERE Name = @name";
        cmd.Parameters.AddWithValue("@name", userName);  // User input is safe

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                Console.WriteLine((string)reader["Name"]);
            }
        }
    }
}

Multiple Parameters

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = @"
        SELECT * FROM Students 
        WHERE ClassName = @className 
        AND Status = @status 
        AND DateOfBirth > @dob";
    
    cmd.Parameters.AddWithValue("@className", "10-A");
    cmd.Parameters.AddWithValue("@status", "Active");
    cmd.Parameters.AddWithValue("@dob", DateTime.Parse("2010-01-01"));

    var reader = await cmd.ExecuteReaderAsync();
}

Explicit Type Declaration

Better approach than AddWithValue (more explicit):

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "INSERT INTO Students (Name, ClassName, DateOfBirth, Status) VALUES (@name, @className, @dob, @status)";
    
    cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = "Ravi Kumar";
    cmd.Parameters.Add("@className", SqlDbType.NVarChar, 10).Value = "10-A";
    cmd.Parameters.Add("@dob", SqlDbType.Date).Value = new DateTime(2010, 5, 15);
    cmd.Parameters.Add("@status", SqlDbType.NVarChar, 20).Value = "Active";

    var rowsAffected = await cmd.ExecuteNonQueryAsync();
}

Output Parameters from Stored Procedure

using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sp_CreateStudent";
    
    cmd.Parameters.AddWithValue("@name", "Priya Sharma");
    cmd.Parameters.AddWithValue("@className", "10-A");
    
    var idParam = cmd.Parameters.Add("@newId", SqlDbType.Int);
    idParam.Direction = ParameterDirection.Output;
    
    await cmd.ExecuteNonQueryAsync();
    
    var newStudentId = (int)idParam.Value;  // Retrieve output value
}

School Management Example - Safe Fee Update

public async Task UpdateFeeStatusAsync(int studentId, string status)
{
    using (var conn = new SqlConnection(connString))
    {
        await conn.OpenAsync();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = @"
                UPDATE FeeAccount 
                SET Status = @status, UpdatedAt = GETDATE()
                WHERE StudentId = @studentId";
            
            cmd.Parameters.AddWithValue("@studentId", studentId);
            cmd.Parameters.AddWithValue("@status", status);  // "Paid", "Pending", "Overdue"
            
            var rowsAffected = await cmd.ExecuteNonQueryAsync();
            
            if (rowsAffected == 0)
                throw new Exception("Fee account not found");
        }
    }
}

Best Practices

✓ Always use parameters for user input ✓ Never concatenate strings into SQL ✓ Specify SqlDbType explicitly ✓ Check ExecuteNonQuery return value ✓ Use async methods

💡 The Golden Rule

If the value comes from a user, API request, or form input - it MUST use a parameter. No exceptions.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Parameters & SQL Injection Prevention. Try these prompts:

• "What is SQL injection and why is it dangerous?"
• "Show me the difference between parameterized vs non-parameterized queries."
• "How do you declare multiple parameters in one query?"
• "What's the difference between AddWithValue and explicit Add with SqlDbType?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 05. SqlDataReader & Result Processing