SqlCommand with Parameters

Question

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?

The title is off- You never set a parameter on SqlConnection - just in case you are not aware there is a difference between a SqlConnection and a SqlCommand. — TomTom
– TomTom, Commented Mar 26, 2014 at 12:18

Marc Gravell · Accepted Answer · 2014-03-26 12:11:18Z

6

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 + "%");

answered Mar 26, 2014 at 12:11

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marc Gravell Over a year ago

Gotta love the internet: Films with Search in the title - note, however, that none have @Search in the title.

TomTom Over a year ago

Which I think is a damn shame and shows how little education our film makers have. The @Search for Spock would be a MUCH better title than without the @.

Indian · Accepted Answer · 2014-03-26 12:12:02Z

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.

Mohini Mhetre · Accepted Answer · 2014-03-26 12:14:39Z

0

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

answered Mar 26, 2014 at 12:14

Mohini Mhetre

92910 silver badges29 bronze badges

Collectives™ on Stack Overflow

SqlCommand with Parameters

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related