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

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

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

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();

Solution 2

[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

Solution 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

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

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

Solution 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

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply