dynamically added sqlcommand string fails while static string works properly

[Austin326]

Hi Everyone,

I have a problem that is quite frusturating.

I am passing in an image from a database, which is to be accessed in an image button. When I dynamically add the string for an sql command, the image is not found, and a red X appears in place of the image. However, when I use a static string, everything works and the image is there.

I have tested my code, and special characters is not the problem.

I posted my code below.

Thanks,

Austin

public void ProcessRequest(HttpContext context) 
{
string image_name = context.Request.QueryString["image_name"]; 

string connString = @"Data Source=WOLVERINE;user id=sa;password=Osprey22;database=FileUpload";SqlConnection connection = new SqlConnection(connString); 
 
connection.Open();

StringBuilder sb = new StringBuilder();sb.AppendFormat("SELECT image_data FROM Available_Pics where image_name = '{0}'", image_name); 
 
[B]// SqlCommand command = new SqlCommand(sb.ToString(), connection);
// The above SqlCommand fails even though sb.ToString() has an identical string to the one shown below that is
// passed into the function.
// Why would a string created dynamically not work, yet a static string like the one below work successfully ?[/B]

SqlCommand command = new SqlCommand("SELECT image_data FROM Available_Pics where image_name = 'test'", connection);SqlDataReader dr = command.ExecuteReader(); 
dr.Read();

context.Response.BinaryWrite((Byte[])dr[0]);
context.Response.ContentType = "image/png"; 
connection.Close();
context.Response.End();

}

[tlhintoq]

Somewhere there is a difference between the two that you just aren't/can't see. Maybe a null on the end for example. Check them both via breakpoints. Heck, use both at the same time and do a comparrison of the two variables to see if they are the same and so you can see them side by side.

if (string1 == string2) console.writeline("same");

Have you tried it NOT using a stringbuilder, but just a string?

string sb = string.format("SELECT image_data FROM Available_Pics where image_name = '{0}'", image_name); 
SqlCommand command = new SqlCommand(sb, connection);

You may also want to either trim or add a null at the end as needed by your query (I don't know which is right for query strings)

string sb = string.format("SELECT image_data FROM Available_Pics where image_name = '{0}'", image_name).trimend('\0');

[Frinavale]

When you build an SQL statement directly from user input as you are doing you leave your self open to a SQL Injection Attack.

Instead of doing what you're doing to create the SQL statement, consider using Parameters instead.

I believe this will fix your problem and it will protect you against a SQL Injection Attack.

See this article on how to use a database in your program for an example of how to use parameters.

-Frinny