CODEWITHSUNDEEP
c#sql.netsql-server
YosiFZ
YosiFZ

Reputation: 7900

SqlCommand with Parameters

I make a selection from a SQL Server table with this code:

using (SqlConnection con = new SqlConnection(SqlConnectionString))
{
    string sql = @"SELECT * FROM movies WHERE title like '%' + '" + searchQuery + "' + '%'";

    using (var command = new SqlCommand(sql, con))
    {
        con.Open();

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ....
            }
        }
    }
}

And it works perfectly, but I want to prevent SQL Injections, so I try to use:

using (SqlConnection con = new SqlConnection(SqlConnectionString))
{
    string sql = @"SELECT * FROM movies WHERE title like '%' '@Search' + '%'";

    using (var command = new SqlCommand(sql, con))
    {
        command.Parameters.AddWithValue("@Search", searchQuery);
        con.Open();

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ..........
            }
        }
    }
}

And when I try to execute this I get no results from SQL Server.

Any idea why?

Upvotes: 1

Views: 1541

Answers (3)

Mohini Mhetre
Mohini Mhetre

Reputation: 919

Don't use single quotes '@Search' as it works like variable here.

Upvotes: 0

Indian
Indian

Reputation: 527

Try this:

using (SqlConnection con = new SqlConnection(SqlConnectionString))
{
    string sql = @"SELECT * FROM movies WHERE title like '%' + @Search + '%'";

    using (var command = new SqlCommand(sql, con))
    {
        command.Parameters.AddWithValue("@Search", searchQuery);
        con.Open();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {

            }
        }
    }
}

I changed string sql, I think that it can help.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1064114

The "why?" is because very few movies have the word "@Search" in their name - i.e. "Indiana Jones and the Last @Search". Maybe "Star Trek III: The @Search For Spock". By enclosing it in single quotes, you are looking for the literal string @Search, rather than the value of the parameter called @Search.

string sql = @"SELECT * FROM movies WHERE title like '%' + @Search + '%'";

Or (preferably, IMO):

string sql = @"SELECT * FROM movies WHERE title like @Search";

and add the % at the call-site:

command.Parameters.AddWithValue("Search", "%" + searchQuery + "%");

Upvotes: 6

Related Questions