How to fix System.Data.SqlClient.SqlException: 'Incorrect syntax near ''.'

Question

im creating a website using asp.net in front end along with c#. I have two tables and im trying to update the columns of one table using the below query. however im getting the below error. Can anyone please help me resolving it.

string sql = "UPDATE CurrentStudent SET CurrentStudent.DateOfJoining ='" + dateOfJoining.Text + "',CurrentStudent.DateOfLeaving = '" + dateOfLeaving.Text + "',CurrentStudent.Course = '"+ "'FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email'"+"'";


System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Incorrect syntax near ''.
  Source=.Net SqlClient Data Provider
  StackTrace:
<Cannot evaluate the exception stack trace>

Beware this query (if it actually worked) will likely be susceptible to abuse by your class mates and strangers with an sql injection attack — TheGeneral, Apr 22 '19 at 07:21
"UPDATE CurrentStudent SET CurrentStudent.DateOfJoining ='" + dateOfJoining.Text + "',CurrentStudent.DateOfLeaving = '" + dateOfLeaving.Text + "',CurrentStudent.Course = '' FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email" try this one — BeiBei ZHU, Apr 22 '19 at 07:25
Possible duplicate of [System.Data.SqlClient.SqlException: Incorrect syntax near '='](https://stackoverflow.com/questions/28426247/system-data-sqlclient-sqlexception-incorrect-syntax-near) — Tayyab, Apr 22 '19 at 07:44
In general, whenever you have a large string built up from many pieces and you need to debug it, it's often helpful to output the the entire string to the console so you can see it in its final form. That way, it's a lot easier to spot the errors yourself. Of course, in this particular case, if you use a parameterized query as has been suggested, the query string becomes a single piece and this advice is no longer needed. But it's still useful in other scenarios. — aleppke, Apr 22 '19 at 17:14

score 2 · Answer 1 · edited Apr 27 '19 at 16:09

The actual problem is the use of string concatenation. Even if the extra quote or missing space are fixed, it's always possible to enter an invalid string, a date that doesn't match the server's locale, 22.04.2019 for example, or an actual malicious string that results in SQL injection.

Using parameterized queries with strongly typed parameters is actually easier then string concatenation:

var sql = @"UPDATE CurrentStudent 
SET CurrentStudent.DateOfJoining =@joinDate
    CurrentStudent.DateOfLeaving = @leaveDate,
    CurrentStudent.Course = ''
FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email";

using(var conn=new SqlConnection(...))
using(var cmd=new SqlCommand(sql,conn);
{
    var joinDate=cmd.Parameters.Add("@joinDate",SqlDbType.Date);
    var leaveDate=cmd.Parameters.Add("@leaveDate",SqlDbType.Date);

    //Set a DateTime, not a string
    joinDate.Value=joinDaterPicker.Value;
    leaveDate.Value=leaveDatePicker.Value;

    conn.Open();
    cmd.ExecuteNonScalar();
}

You can use a microORM like Dapper to simplify the code even more:

var sql = @"UPDATE CurrentStudent 
SET CurrentStudent.DateOfJoining =@joinDate
    CurrentStudent.DateOfLeaving = @leaveDate,
    CurrentStudent.Course = ''
FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email";

using(var conn=new SqlConnection(...))
{
    conn.Execute(sql,new { joinDate  = joinDaterPicker.Value, 
                           leaveDate = leaveDatePicker.Value});
}

Tayyab · Answer 2 · 2019-04-23T03:36:59.407

1

You are missing out a space between the From and possible string end:

"UPDATE CurrentStudent SET CurrentStudent.DateOfJoining ='" + dateOfJoining.Text + "',CurrentStudent.DateOfLeaving = '" + dateOfLeaving.Text + "',CurrentStudent.Course = '"+ Course.Text +"' FROM CurrentStudent SI INNER JOIN UserDetails UI ON SI.Email = UI.Email'"+"'"

Note the "' FROM added space.

Try the above string and also i will recommend you use parametrized queries. Some useful links:

link1

link2

Also a bit more on why to use parametrized queries:

SQL Injection

why use parameterized queries

edited Apr 23 '19 at 03:36

answered Apr 22 '19 at 07:39

Tayyab

1,207
8
29

There's also an additional pair of single quotes at the very end of the query, which is likely causing a problem too. – aleppke Apr 22 '19 at 17:04
Have added another 'assumed variable' replacing the empty quotes at the end in case someone else go through the query to avoid ambiguity in syntax. – Tayyab Apr 23 '19 at 03:38

How to fix System.Data.SqlClient.SqlException: 'Incorrect syntax near ''.'

2 Answers2