Problem with WHERE columnName = Data in MySQL query in C #

Question

I have a C # web service on a Windows Server that I interact with on a Linux server with PHP. PHP captures information from the database, and then the page offers a "more information" button, which then calls the web service and passes it into the record name field as a parameter. Therefore, I use the WHERE clause in my query, so I just retrieve the extra fields for this record. I get an error message:

System.Data.SqlClient.SqlException: Invalid column name '42'

Where 42 is the value from the name field from the database.

my request

string selectStr = "SELECT name, castNotes, triviaNotes FROM tableName WHERE name =\"" + show + "\"";

I don't know if this is a problem with my query or something is wrong with the database, but here is the rest of my code for reference.

NOTE: all of this works fine when I grab all the records, but I only want to grab the record that I am asking for my web service about.

public class ktvService  : System.Web.Services.WebService {

[WebMethod]
public string moreInfo(string show) {

    string connectionStr = "MyConnectionString";
    string selectStr = "SELECT name, castNotes, triviaNotes FROM tableName WHERE name =\"" + show + "\"";

    SqlConnection conn = new SqlConnection(connectionStr);
    SqlDataAdapter da = new SqlDataAdapter(selectStr, conn);
    DataSet ds = new DataSet();
    da.Fill(ds, "tableName");
    DataTable dt = ds.Tables["tableName"];

    DataRow theShow = dt.Rows[0];
    string response = "Name: " + theShow["name"].ToString() + "Cast: " + theShow["castNotes"].ToString() + " Trivia: " + theShow["triviaNotes"].ToString();

    return response;

} 

}

Answer 1

Fast decision:

I believe you need single quotes in selectStr:

string selectStr = 
"SELECT name, castNotes, triviaNotes FROM tableName WHERE name = '" + show + "'";

Additional Information:

In .NET, you'll want to make sure that you explicitly close any connections when you no longer need them. The easiest way to do this is to wrap statements usingaround any types that implement IDisposable, for example, SqlConnectionin this case:

using(SqlConnection conn = new SqlConnection(connectionStr))
{
    SqlDataAdapter da = new SqlDataAdapter(selectStr, conn);
    DataSet ds = new DataSet();
    da.Fill(ds, "tableName");
    DataTable dt = ds.Tables["tableName"];

    DataRow theShow = dt.Rows[0];
    string response = "Name: " + theShow["name"].ToString() + "Cast: " + theShow["castNotes"].ToString() + " Trivia: " + theShow["triviaNotes"].ToString();

    return response;
}

Also, it looks like your code could easily be subject to SQL injection. What if someone submits a form with the value fake name' OR 1=1;DROP DATABASE someDbName;--:?

You will want to use SQL parameters, for example:

SqlCommand cmd = new SqlCommand(
  "SELECT name, castNotes, triviaNotes FROM tableName WHERE name = @show", conn);

cmd.Parameters.AddWithValue("@show", show);

Answer 2

WHERE WHERE name = '" + show + "'"; , SQL.

System.Data.SqlClient SQL Server, MySQL. . MySQL MySQL #.