Prevent SQL Injection in Queries · Mesrai Marketplace

Why this matters

Using unsanitized user input in SQL queries can lead to SQL injection attacks. Use parameterized queries to protect against malicious inputs.

Bad vs. good

Side-by-side examples engineers can pattern-match during review.

Avoid

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = '" + user + "' AND pass = '" + pass + "'";

        var queryResults = Context
            .Database
            .FromSqlRaw(query);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

Prefer

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = {0} AND pass = {1}";

        var queryResults = Context
            .Database
            .FromSqlRaw(query, user, pass);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

More snippets

Bad

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = '" + user + "' AND pass = '" + pass + "'";

        var queryResults = Context
            .Database
            .FromSqlRaw(query);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

Good

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = {0} AND pass = {1}";

        var queryResults = Context
            .Database
            .FromSqlRaw(query, user, pass);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

Related rules

From the same buckets as this rule.

Critical

Always sanitize user inputs

Check if user inputs are sanitized before being used in rendering or database queries. Unsanitized inputs can lead to injection vulnerabilities like XSS or SQL injection.

security-hardening

Critical

Always Verify Server Certificates in SSL/TLS Connections

Ensure that SSL/TLS certificate validation is always enabled to prevent Man-in-the-Middle (MitM) attacks.

security-hardening

Critical

Avoid modifying built-in prototypes

Detect modifications to built-in prototypes. Modifying built-in objects can lead to compatibility issues and unexpected behavior. Recommend creating helper functions instead.

maintainabilitysecurity-hardening

Bad vs. good

Side-by-side examples engineers can pattern-match during review.

Avoid

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = '" + user + "' AND pass = '" + pass + "'";

        var queryResults = Context
            .Database
            .FromSqlRaw(query);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

Prefer

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = {0} AND pass = {1}";

        var queryResults = Context
            .Database
            .FromSqlRaw(query, user, pass);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

More snippets

Bad

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = '" + user + "' AND pass = '" + pass + "'";

        var queryResults = Context
            .Database
            .FromSqlRaw(query);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

Good

public class ExampleController : Controller
{
    private readonly UserAccountContext Context;

    public IActionResult Authenticate(string user, string pass)
    {
        var query = "SELECT * FROM users WHERE user = {0} AND pass = {1}";

        var queryResults = Context
            .Database
            .FromSqlRaw(query, user, pass);

        if (queryResults == 0)
        {
            return Unauthorized();
        }

        return Ok();
    }
}

Related rules

From the same buckets as this rule.

Critical

Always sanitize user inputs

Check if user inputs are sanitized before being used in rendering or database queries. Unsanitized inputs can lead to injection vulnerabilities like XSS or SQL injection.

security-hardening

Critical

Always Verify Server Certificates in SSL/TLS Connections

Ensure that SSL/TLS certificate validation is always enabled to prevent Man-in-the-Middle (MitM) attacks.

security-hardening

Critical

Avoid modifying built-in prototypes

Detect modifications to built-in prototypes. Modifying built-in objects can lead to compatibility issues and unexpected behavior. Recommend creating helper functions instead.

maintainabilitysecurity-hardening