Incorrect syntax near ''. Unclosed quotati

问题:

I'm just wondering if someone could point me in the right direction here, I think i've been looking at it for too long so can't see the mistake.

The following code:

SqlCommand updateStyle = new SqlCommand("UPDATE [Lorenz].[dbo].[Layout] SET [bgColour] = '" + bgColour + "' , [textColour] = '" + txtColour + "WHERE <[LoweredUserName] ='" + currentUser + "' ", connection);
updateStyle.ExecuteNonQuery();

Is giving the error:

Incorrect syntax near 'admin'. Unclosed quotation mark after the character string ' '.

回答1:

[textColour] = '" + txtColour + "WH

Missing a single quote:

[textColour] = '" + txtColour + "'WH

EDIT: While I simply pointed out why the error was happening, the poster below me is correct about using parametrized queries for these sorts of things; or perhaps an ORM such as LINQ

回答2:

You should really be using SQL parameters. Not only does it help to protect your app from SQL injection attacks, it will also make SQL syntax errors easier to spot.

SqlCommand updateStyle = new SqlCommand("UPDATE [Lorenz].[dbo].[Layout] SET [bgColour] = @bgColour, [textColour] = @textColour WHERE <[LoweredUserName] = @currentUser", connection);
updateStyle.Parameters.Add(new SqlParameter("@bgColour", bgColour));
updateStyle.Parameters.Add(new SqlParameter("@textColour", textColour));
updateStyle.Parameters.Add(new SqlParameter("@currentUser", currentUser));
updateStyle.ExecuteNonQuery();

回答3:

I think you should rather have a look at

SQL Parameters in C#

SqlParameter Class

to try to avoid SQL injection

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks

All in all, you are leaving a massive gap in your application for attacks based on the query string being dynamically created. The quoted error you have will be handled but also avoid any, lets say DROP TABLE USERS

回答4:

WHERE <[LoweredUserName]

The above syntax especially the < seems incorrect. Try running SQL profiler on the SQL server (if applicable) to see what SQL was sent to the server.

Also use parameters to prevent SQL injection attacks.

回答5:

This is a syntax error from SQL, obviously, not from C#.

You need to get the values you're inserting at runtime -- then you'll see the syntax error in the SQL statement.

EDIT Or you could just do what @zincorp says :)

As a general practice, it's more readable to use String.Format in this type of situation. Even more importantly, you also want to be sure you escape your literals.

回答6:

Indeed Parameterized SQL is safer - I use it as well:

string mySqlStmt = "UPDATE tbSystem SET systemCode_str = @systemCode_str, systemName_str = @systemName_str";

            using (var conn = new SqlConnection(myConnStr))
            using (var command = new SqlCommand(mySqlStmt, conn)
            {

                CommandType = CommandType.Text

            })
            {
                //add your parameters here - to avoid SQL injection
                command.Parameters.Add(new SqlParameter("@systemCode_str", "ABZ"));
                command.Parameters.Add(new SqlParameter("@systemName_str", "Chagbert's Shopping Complex"));

                //now execute SQL
                conn.Open();
                command.ExecuteNonQuery();
                conn.Close();
            }

You will note that with parameterized SQL I do not have to worry about quotation marks in my values as in the quotation " ' " in "Chagbert's Shoppin..." above