SQL Query not working -- operator missing

Question

I've looked though more SQL problems than I care to count but nothing seems to be working for this particular issue. I'm running a SELECT query against an access database in C# using the following code:

DateTime starttime = new DateTime(2011, 9, 4);
DateTime endtime = new DateTime(2011, 9, 10);

cmd.CommandText = @"SELECT ACT_ENTRY2CASE,ACT_ENTRY2USER FROM SA_TABLE_ACT_ENTRY WHERE ENTRY_TIME>" + starttime + @" AND ENTRY_TIME<" + endtime + @" AND ACT_ENTRY2CASE IS NOT NULL";
reader = cmd.ExecuteReader();

I get the error:

Syntax error (missing operator) in query expression 'ENTRY_TIME>9/4/2011 12:00:00 AM AND ENTRY_TIME<9/10/2011 12:00:00 AM AND ACT_ENTRY2CASE IS NOT NULL'.

I have tried surrounding my variables with various things (parentheses, ' marks, [], etc). Nothing seems to make it work (although with some of those items the error changes to something to the effect of "missing or invalid parameter".

I'm sure it's a matter of some dumb little typo on my part but I can't find it for the life of me. Any ideas?

Use parameterised queries with parameters of correct datatype. Your string literals are not quoted which is the cause of the error. Even if you fix that the datetime format is ambiguous and even if you fix that you still probably will be vulnerable to SQL injection. — Martin Smith
– Martin Smith, Commented Sep 15, 2011 at 16:33

Adriano Carneiro · Accepted Answer · 2011-09-15 16:42:06Z

4

First of all, your code is vulnerable to SQL Injection.

Your should parametrize your query. Here is "teh codez":

cmd.CommandText = @"
   SELECT ACT_ENTRY2CASE,ACT_ENTRY2USER 
   FROM SA_TABLE_ACT_ENTRY 
   WHERE ENTRY_TIME> @starttime 
   AND ENTRY_TIME< @endtime 
   AND ACT_ENTRY2CASE IS NOT NULL";

cmd.Parameters.AddWithValue("@starttime", starttime);
cmd.Parameters.AddWithValue("@endtime ", endtime );

reader = cmd.ExecuteReader();

answered Sep 15, 2011 at 16:42

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

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

3 Comments

MusiGenesis Over a year ago

Minor quibble: the code is only vulnerable to SQL injection if the values being concatenated come from unfiltered user input. Even so, parameterizing the query is the right thing to do.

DarkShadow Over a year ago

That did the trick thanks! I am aware that my code was vulnerable to SQL injection but I wasn't too worried about it since it's only going to be used by me to automate some reports. Nothing public facing.

Adriano Carneiro Over a year ago

You know, it is always like that about SQL Injection. "I'm not worried right now", "It's just something in the Intranet", "It's just Sandbox". You know where I want to get to...

gislikonrad · Accepted Answer · 2011-09-15 16:35:52Z

4

You need to either put your starttime and endtime within quotations (if you just have a string representation of date in your database) or use To_Date() sql function to convert your dates to a sql date object...

However it's always best to parameterize your sql to make it safe and to raise performance.

answered Sep 15, 2011 at 16:35

gislikonrad

3,5912 gold badges24 silver badges28 bronze badges

1 Comment

marc_s Over a year ago

+1 - parametrized queries should be the only way to do this!

Schroedingers Cat · Accepted Answer · 2011-09-15 16:36:55Z

0

I don't know if it is all of the problem, but I think you need to have your dates in quotes. You may have tried this and missed one, which would give you mismatched quotes.

answered Sep 15, 2011 at 16:36

Schroedingers Cat

3,1392 gold badges19 silver badges34 bronze badges

Collectives™ on Stack Overflow

SQL Query not working -- operator missing

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related