whats wrong with this code while passing param

Question

0.00/5 (No votes)

See more:

C#

string sql = "select * from   tblVehicle where CAST(floor( CAST( Vehentrytime AS FLOAT ) )AS DATETIME) = '11/4/2011' ";

the above code works fine
but when the time is in string for example

C#

string myDate = System.DateTime.Now.ToString("MM/dd/yyyy");

and the query is now like

C#

string sql = "select * from   tblVehicle where CAST(floor( CAST( Vehentrytime AS FLOAT ) )AS DATETIME) = "+myDate+"";

the datas are not obtained

is there any error in the second query?
please inform me where is the mistake?
with regards bishnu karki

Posted 3-Nov-11 0:24am

vishnu karki

Updated 3-Nov-11 0:25am

P.Salini

v2

Add a Solution

Comments

_Zorro_ 3-Nov-11 6:31am

Hi,

1. What's the real datatype of Vehentrytime?
2. Are you sure that the database shows the dates in this format (MM/dd/yyyy)?
3. Why not just comparing two dates, as it should be done?
4. What you're trying to do is not a good idea, you're creating a strong dependence between your database server and its OS culture.

3 solutions

Solution 1

Have a look in the debugger at the SQL. Are you missing the single quotes? Also, as a tip I'd use DateTime.UtcNow (if you're in the uk) as this avoids GMT/BST differences.

(Better yet, paramaterise the date).

Posted 3-Nov-11 0:27am

Rob Philpott

Updated 3-Nov-11 0:28am

v2

Comments

_Zorro_ 3-Nov-11 6:33am

You're right, it seems that the single quotes are missing.

_Zorro_ 3-Nov-11 6:42am

+5 For the edit ;)

RaisKazi 3-Nov-11 6:42am

My 5! Had a same doubt of "single quote" problem.

Solution 3

thank you raiskazi ur code really helped me a lot thanks...

Posted 3-Nov-11 0:37am

vishnu karki

Comments

André Kraak 3-Nov-11 6:38am

If you have a question about or comment on a given solution use the "Have a Question or Comment?" option beneath the solution. When using this option the person who gave the solution gets an e-mail message and knows you placed a comment and can respond if he/she wants.

Please move the content of this solution to the solution you are commenting on and remove the solution.
Thank you.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RaisKazi · Accepted Answer · 2011-11-03T00:33:00

Solution 2

Try by adding single quotes.

C#

string sql = "select * from   tblVehicle where CAST(floor( CAST( Vehentrytime AS FLOAT ) )AS DATETIME) = '" + myDate + "'";

Couple of more points -
1) Not sure why you are using CAST ... AS FLOAT.
2) Not sure why you are using floor.
3) Plain concatenated Queries may be risky considering "SQL Injection", instead use Parametrized Queries.

Posted 3-Nov-11 0:33am

RaisKazi

Updated 3-Nov-11 0:35am

v2

Comments

_Zorro_ 3-Nov-11 6:36am

+5 for the SQL Injection warning.

RaisKazi 3-Nov-11 6:40am

Thank you Zoro. :)

BobJanova 3-Nov-11 7:48am

Although the SQL injection warning as a general thing is good to remind the questioner of, there is actually no vulnerability here as myDate isn't created from user input. SQL injection is possible when you concatenate queries *and* the data that you concatenate includes free text input from an untrusted source.

Parameterised queries have other valuable benefits like not requiring ToString on one side and Parse on the other, and they're a good thing to use all the time anyway. But in this particular case (and also common ones like ['select from table where id='+id] where id is a number) there is no security reason.

_Zorro_ 3-Nov-11 9:27am

I may be wrong but there is a security threat with your last example.

[select x from table where id=id OR 1 = 1]

And what about this particular case? How do you know that he won't be reading his date from an input later?

You can't say if the user, in this case vishnu karki is reading it from a textbox, if he validates the field, etc. So, in my opinion, it's a good practice to do it properly from start.

Sure, you could just answer: "hey dude, you miss a single quote", or you can also help him to become better, anyway, if he didn't want to improve his skills he got his answer so it's not a big deal.

BobJanova 3-Nov-11 13:46pm

I think you missed 'where id is a number'. Yes, if id is a free text string, obviously there is a vulnerability. In the specific case in the question, not only is it a non-string type but it's also not generated from user input.

"So, in my opinion, it's a good practice to do it properly from start."
Yes, agreed, hence the first clause of my reply to you ;). And it's still better to use parameterised queries even if there is no injection vulnerability there. But I think it is even better to explain -why- the good practice is good and where it is more important.