Incorrect syntax on SQL CommandText

Question

I'm trying to build a SQL query and will be passing a string as a parameter. However, I'm getting and incorrect syntax error. I've printed out the values and all seems fine to me though.

I have a string "sqlString" which is built through a series of concatenations, producing this:

sqlString = " and (event_id=60 OR event_id=61 OR event_id=56 OR event_id=73)"

SqlCommand totalCmd = new SqlCommand();

totalCmd.CommandText = "SELECT sum(datediff(hour,[event_start],[event_end])) as Timeworked FROM event WHERE (event_start >= @StartDate and event_end <= @EndDate) @mySqlString";

totalCmd.Parameters.AddWithValue("StartDate", txtStartDate.Text);
totalCmd.Parameters.AddWithValue("EndDate", txtEndDate.Text);
totalCmd.Parameters.AddWithValue("mySqlString", sqlString);

totalDT = SqlComm.SqlDataTable(totalCmd);

This is producing an error

Incorrect syntax near '@mySqlString'

I've ran this query directly in SQL by entering in the parameters, and it runs fine. What is the issue with passing the mySqlString parameter?

You're trying to inject SQL. Parameters prevent SQL injection. — Tim S.
– Tim S., Commented Dec 13, 2013 at 20:34

David S. · Accepted Answer · 2013-12-13 20:34:59Z

3

You can't add sqlString as a parameter. You should concatenate it directly to the CommandText instead:

totalCmd.CommandText = "SELECT sum(datediff(hour,[event_start],[event_end])) as Timeworked FROM event WHERE (event_start >= @StartDate and event_end <= @EndDate) " + sqlString;

The whole point (besides being tidy) of adding values to the Parameters instead of concatenating is to clean the values for malicious commands - i.e. the parameters should only be pure values. So the way you do it now, the sqlString is interpreted as a SQL injection attack, and will essentially be purged by that check.

edited Dec 13, 2013 at 20:34

answered Dec 13, 2013 at 20:29

David S.

6,1332 gold badges45 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karl Anderson · Accepted Answer · 2013-12-13 20:26:49Z

2

You need to put the @ symbol in front of your parameters, like this:

totalCmd.Parameters.AddWithValue("@StartDate", txtStartDate.Text);
totalCmd.Parameters.AddWithValue("@EndDate", txtEndDate.Text);
totalCmd.Parameters.AddWithValue("@mySqlString", sqlString);

Note: The syntax you had was not finding a match, so the parameter value was not being substituted, thus it was passing @mySqlString in the query.

answered Dec 13, 2013 at 20:26

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

1 Comment

Cineno28 Over a year ago

That ended up not being the issue here, but thank you for your note. I will incorporate that as well.

Collectives™ on Stack Overflow

Incorrect syntax on SQL CommandText

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related