C# | Use parameter in SqlCommand query

Question

I have this ASP.NET Core project and in my DAL I collect my data from my database, but I want the user to be able to type a type character (Karakter is character in Dutch) and the DAL should pick 2 random characters from the database with KarakterSoort = "Defensive".

This is my current code in my DAL:

public IEnumerable<IKarakter> GetSortedKarakters()
{
    using (SqlConnection connection = GetConnection())
    {
        connection.Open();
        var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = 'Defensive' ORDER BY NEWID();", connection);
        var reader = command.ExecuteReader();
        var sortedKarakters = new List<IKarakter>();

        while (reader.Read())
        {
            var karakter = new KarakterDTO
            {
                KarakterId = (int)reader["KarakterId"],
                KarakterSoort = reader["KarakterSoort"]?.ToString(),
                KarakterNaam = reader["KarakterNaam"]?.ToString()
            };

            sortedKarakters.Add(karakter);
        }

        return sortedKarakters;
    }
}

And this is what I think should be in the right direction of the solution:

public IEnumerable<IKarakter> GetSortedKarakters(string givenStringFromUser)
{
    using (SqlConnection connection = GetConnection())
    {
        connection.Open();
        var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = givenStringFromUser ORDER BY NEWID();", connection);
        var reader = command.ExecuteReader();
        var sortedKarakters = new List<IKarakter>();

        while (reader.Read())
        {
            var karakter = new KarakterDTO
            {
                KarakterId = (int)reader["KarakterId"],
                KarakterSoort = reader["KarakterSoort"]?.ToString(),
                KarakterNaam = reader["KarakterNaam"]?.ToString()
            };

            sortedKarakters.Add(karakter);
        }

        return sortedKarakters;
    }
}

I can't find how to make this work so I figured I ask my question here.

score 1 · Answer 1 · answered Nov 08 '20 at 13:17

You have to provide your filter value to the sql query. Also, don't put it directly to query string, but use SqlCommand parameters. That will save you for the unwanted sql injection.

Something like this should work:

public IEnumerable<IKarakter> GetSortedKarakters(string givenStringFromUser)
{
    using (SqlConnection connection = GetConnection())
    {
        connection.Open();
        var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = @filterValue ORDER BY NEWID();", connection);
        command.Parameters.Add("@filterValue", SqlDbType.VarChar).Value = givenStringFromUser;
        
        var reader = command.ExecuteReader();
        var sortedKarakters = new List<IKarakter>();
        while (reader.Read())
        {
            var karakter = new KarakterDTO
            {
                KarakterId = (int)reader["KarakterId"],
                KarakterSoort = reader["KarakterSoort"]?.ToString(),
                KarakterNaam = reader["KarakterNaam"]?.ToString()
            };
            sortedKarakters.Add(karakter);
        }
        return sortedKarakters;
    }
}

score 1 · Accepted Answer · answered Nov 08 '20 at 13:17

You need to add a SqlParameter and set its value:

public IEnumerable<IKarakter> GetSortedKarakters(string givenStringFromUser)
{
    using (SqlConnection connection = GetConnection())
    {
        var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = @UserInput ORDER BY NEWID();", connection);
        command.Parameters.Add("@UserInput", SqlDbType.VarChar, 100).Value = givenStringFromUser;

        connection.Open();
        var reader = command.ExecuteReader();
        var sortedKarakters = new List<IKarakter>();

        while (reader.Read())
        {
            var karakter = new KarakterDTO
            {
                KarakterId = (int)reader["KarakterId"],
                KarakterSoort = reader["KarakterSoort"]?.ToString(),
                KarakterNaam = reader["KarakterNaam"]?.ToString()
            };

            sortedKarakters.Add(karakter);
        }

        connection.Close();

        return sortedKarakters;
    }
}

C# | Use parameter in SqlCommand query

2 Answers2