SQL Server Invalid column name when adding string value

Question

I'm new to SQL Server

I've created my table like this:

CREATE TABLE Accidents (
     Id INT NOT NULL PRIMARY KEY IDENTITY,
     GUID VARCHAR(100),
     Latitude VARCHAR(100),
     Longitude VARCHAR(100),
     PhotoName VARCHAR(100)        
     )

and I've created a web service to insert data to that table, like this:

    SqlConnection con = new SqlConnection(@"workstation id=DatabaseSample.mssql.somee.com;packet size=4096;user id=???;pwd=???;data source=DatabaseSample.mssql.somee.com;persist security info=False;initial catalog=DatabaseSample");

    public string addAccidentToDatabase(string GUID, string imageBase64String, string latitude, string longitude, string photoName)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Accidents (GUID,Latitude,Longitude,PhotoName) VALUES ("
            + GUID + "," + latitude + "," + longitude + "," + photoName + ")", con);

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            return e.Message;
        }
        finally
        {
            con.Close();
        }

        return "succeeded";
    }

All that stuff is hosted to a free hosting server.

When I test the web service from VS2010, and when I pass numbers to all parameters, a new row is successfully added to the table. But when I pass at least one string to the service, for example "a", I get this result:

<string xmlns="http://tempuri.org/">Invalid column name 'a'.</string>

and the row is not added. I wonder why he considered "a" as a column name? Anyone can help?

Answer 1

I wonder why he considered "a" as a column name?

That's not hard to see. You shouldn't develop software, deploy it and hope it runs. You should test it. Extract the executing code into a class method, and call that from your service. When developing, you call this method from a unit test or commandline program or whatever you like to test with.

Your problem: you don't put quotes around the strings (or varchars if you want) in the query. You would've seen it if you just printed the query string to the console for example.

But honestly that's the least of your problems. You shouldn't hand-craft SQL. At least use parameterized queries. So let your query be:

"INSERT INTO Accidents (GUID, Latitude, Longitude, PhotoName) 
                VALUES (@GUID, @Latitude, @Longitude, @PhotoName)"

And bind the parameters:

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

Answer 2

Please, replace your code with this.

 SqlCommand cmd = new SqlCommand("INSERT INTO Accidents (GUID,Latitude,Longitude,PhotoName) " + 
                                 "VALUES (@guid, @lat, @long, @photo)", con);
 cmd.Parameters.AddWithValue("@guid", GUID);
 cmd.Parameters.AddWithValue("@lat", latitude);
 cmd.Parameters.AddWithValue("@long", longitude);
 cmd.Parameters.AddWithValue("@photo", photoName);

Why? Well, suppose that one of your strings contain a single quote.
The query will fail with a syntax error. But do not stop to strings. What about dates and decimal numbers? You need to format them in an way that's agreable to the database globalization settings, just to fail on the next customer with different settings. A parameter will solves this for you.

Worst. Suppose that a malicious user types, in the inputbox for PhotoName, something like this:

 p1.jpg'); DROP TABLE ACCIDENTS; -- 

That's a big, big problem - It is called Sql Injection, and yes, a parameter prevents this. I really hope that you don't write this code on databases where you have sensitive informations.