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

Question

I have a C# webservice on a Windows Server that I am interfacing with on a linux server with PHP. The PHP grabs information from the database and then the page offers a "more information" button which then calls the webservice and passes in the name field of the record as a parameter. So i am using a WHERE statement in my query so I only pull the extra fields for that record. I am getting the error:

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

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

my query is

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

I do not know if it is a problem with my query or something is wrong with the database, but here is the rest of my code for reference.

NOTE: this all works perfectly when I grab all of the records, but I only want to grab the record that I ask my webservice for.

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

Shouldn't the WHERE clause be WHERE name = '" + show + "'"; Strings should be enclosed in single quotes and not double quotes for SQL statements.

Also the System.Data.SqlClient namespace is for SQL Server and not MySQL. See MySQL official docs for connecting to MySQL via C#.

Answer 2

Quick solution:

I believe you need single quotes in your selectStr:

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

More information:

In .NET, you'll want to be sure you close out any connections explicitly when you no longer need them. The easiest way to do this is to wrap using statements around any types that implement IDisposable, such as SqlConnection in 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;
}

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

You'll want to take advantage of SQL parameters, something like:

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

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