-4

Here is my code... It works fine for input data that does not have a single quote, but when I enter a single quote and try to Post it crashes. I've tried many code samples and this should work. Any help would be appreciated.

string SQLcommand = "UPDATE FirearmTracking SET LastName='"
+ @custData[2] + // lastName
"', CitationNum='" + custData[1] + // citation
"', FirstName='" + custData[3] + // firstname
"', MiddleInitial='" + custData[4] + // mi
"' WHERE EventNum = '" + @custData[0] +  // eventNum
            "' ";
        // Create a new table
        DataTable FirearmTracking = new DataTable();

        // Grab the Connection String
        SqlConnection conn = Connections.GetDataFromDB();

        // Create a SqlCommand object
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = SQLcommand;

        cmd.Parameters.AddWithValue("@LastName", custData[2]);

        try 
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            throw new Exception("Data Layer - Customer Error...");
        }
        finally
        {
            conn.Close();
        }
        return null;
John Saunders
  • 160,644
  • 26
  • 247
  • 397
John Crispin
  • 7
  • 1
  • 3
    use only parameters. Not string concatenation. Period. – Mitch Wheat Nov 06 '14 at 00:54
  • Either use Parameters or convert that into a Stored Procedure it's quite messy in it's current state and prone to SQL Injection – MethodMan Nov 06 '14 at 01:00
  • 1
    Also, this isn't a "C# parameterized query". It may be a "SQL Server Parameterized Query", but the query itself has nothing to do with C#. The way you're doing it is a bad idea in _any_ language. – John Saunders Nov 06 '14 at 01:07

2 Answers2

3

You are setting up a parameter, but the query doesn't use it.

Put parameter names in the query instead of concatenating in the values:

string SQLcommand =
  "UPDATE FirearmTracking SET " +
  "LastName = @LastName, " +
  "CitationNum = @CitationNum, " +
  "FirstName = @FirstName, " +
  "MiddleInitial = @MiddleInitial " +
  "WHERE EventNum = @EventNum";

Now add parameters for them:

cmd.Parameters.AddWithValue("@LastName", custData[2]);
cmd.Parameters.AddWithValue("@CitationNum", custData[1]);
cmd.Parameters.AddWithValue("@FirstName", custData[3]);
cmd.Parameters.AddWithValue("@MiddleInitial", custData[4]);
cmd.Parameters.AddWithValue("@EventNum", custData[0]);
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • That did it! Thanks so much. All the other examples I saw were placing the @ in front of the value fields in the Update command. ...I spent about six hours on this today and thought I was going crazy.Thank you again. – John Crispin Nov 06 '14 at 01:08
0

You have this problem because you need to escape single quote by doubling them.

See this thread : How do I escape a single quote in SQL Server?

But anyway, it's a bad practice to exec query directly from user input, use parameters or stored procedure.

Community
  • 1
  • 1
tickwave
  • 3,335
  • 6
  • 41
  • 82