SQL Server Invalid column name when adding string value

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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);
...

Method 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.

Method 3

You need quotes around your strings. You’re just directly substituting in the values, so SQL is trying to parse them as columns.

SqlCommand cmd = new SqlCommand("INSERT INTO Accidents (GUID,Latitude,Longitude,PhotoName)    
       VALUES ('" + GUID + "','" + latitude + "','" + longitude + "','" + photoName + "')", con);

You should note, however, that this is extremely insecure code. It’s very prone to SQL injection. Try using paramaterized queries instead.

Method 4

I’m guessing your GUID value starts with an a. If it started with a 3 you’d probably get something more entertaining.

Since you’re passing it in as a string, and not escaping it with quotes, you receive an error.

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

A few points:

1. You should really avoid building queries in this way. Use a stored procedure or parameterized query.
2. why are you storing a guid as a varchar()? There is a very nice Guid datatype available.
3. Don’t name columns after data types.

Method 5

you need single quotes to string values

But I strightly recommend you to use SQLParameters to avoid any SQLInjections attacks

You can find examples of using SQL Parameters here

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0