CODEWITHSUNDEEP
sql-serverparameterssqlcommand
xola
xola

Reputation: 495

SQL Server: Parameter is not replaced

I've got a fully functionable (secure) session to a SQL Server database (version 10.50.4000). This is stored in a public variable:

SqlConnection conn = new SqlConnection();

I only want to run SELECT queries. For anything else, the user account got no rights.

The queries are built with only one user entry, which is inserted into a simple Text Box.

Unfortunately I must not tell you the original command text. So I make it simple for you:

function print_users(string filtervalue)
{
    SqlCommand cmd = null;
    cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE '%@fv%'", this.conn)

    cmd.Parameters.Add("@fv", SqlDbType.NVarChar);
    cmd.Parameters["@fv"].Value=filtervalue;

    rdr = cmd.ExecuteReader();

    while(rdr.Read())
    {
        //Do something with the answer from the DB
    }
}

But this does not do the trick. I also tried AddWithValue, but I got no luck.

When creating a stop-point on the line, where @fv should be replaced, I can go through the code line-by-line. And I can see that the command, where @fv should be replaced, is processed with no error. But @fv is not replaced (or at least I cannot see the replacement in the debug console).

What am I doing wrong?

EDIT:

thank you for your replies. Leaving out the single quotes ( ' ) did the trick.

And I also learned that this is not a string replacement. Thank you.

Just one word: The connection is not left open all the time. It's immediately closed, when it's not needed any more; and re-established when needed again - I just forgot to write that into my sample code.

Again: Thank you for your help!

Upvotes: 1

Views: 2377

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

The parameter is not working because it is inside a string literal. You want to build the string like this:

cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE '%' + @fv + '%'");

While we're at it, keeping a global connection like that is bad. It can cause strange side effects, especially in web apps. Instead, keep a global connection string, and then use that string to create a new connection on each request.

Also, "replace" is the wrong word here. Sql parameters are never replaced, even when they work. That's the whole point. There is no string replacement into your query at any point, ever. It's more like you declared an @fv variable at the server level in a stored procedure, and assigned your data directly to that variable. In this way, there is no possibility for a vulnerability in parameter replacement code, because the data portion of your query remains separate throughout the execution process. In same way, don't think in terms of "sanitizing" a parameter for a query; instead, think in terms of quarantining the data.

Upvotes: 1

Mr47
Mr47

Reputation: 2655

You can't see it being replaced in your debug session; the replacement occurs in the SQL server code itself...

The client (your code) send both the SQL-string and the value of the parameter as seperate things to the server. There the SQL Engine 'replaces' the parameter with its value while executing it.

You should also put the 'wildcards' inside your parametervalue, not inside the query.

cmd = new SqlCommand("SELECT users From groups WHERE group_name LIKE @fv ", this.conn)
cmd.Parameters.Add("@fv", SqlDbType.NVarChar);
cmd.Parameters["@fv"].Value= "%" + filtervalue + "%";

Upvotes: 2

Related Questions