why isn't this coldfusion query not working?

Question

I am having an issue with trying to update an SQL table with this cfquery. Here is the code in cold fusion:

    <CFSET dateTimes=DateFormat(Now(),"mm\dd\yyyy")>
    <CFQUERY NAME="updateTime" DATASOURCE="#this_datasource#">
        UPDATE users
        SET ACTIVITYDATE = CAST(#dateTimes# AS smalldatetime)
        WHERE username = '#Form.login_username#'
        AND Password = '#Form.Password#'
    </CFQUERY>

When trying to execute this it gives me this:

    Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]   [SQLServer]Incorrect syntax near '\25'. 
    The error occurred on line 19.

Also another thing is the type of sal_var the ACTIVITYDATE is smalldatetime. I have also tried doing it without the cast and just doing the plain #dateTimes# var. I have also tried the cfqueryparam which also did not work. Thank you in advance for your help!

You should use `cfqueryparam` for the values you are entering into the DB. You can then get rid of the `CAST()`. — Scott Stroz, Mar 25 '14 at 20:51
I have tried that with cfsqltype equal to cf_sql_date, but even that didn't work. I will play around with the different types I guess. — Sovr Sov, Mar 25 '14 at 20:52
You should be using `cfqueryparam` for the username and password as well. — Scott Stroz, Mar 25 '14 at 20:56
I haven't tested this, but I believe you need to be using forward slashes instead of back slashes. Also, as previously expressed, use cfqueryparams and no CAST. — Chris Tierney, Mar 25 '14 at 20:57
ok well I just got out of work, so I will have to try tomorrow. If I have any other problems I will post again on here and thank everyone for helping. — Sovr Sov, Mar 25 '14 at 21:16
Arguably more important than the lack of cfqueryparam (though that is an important issue) is that you're storing the password in plaintext - which is something that should ***never*** be done. — Peter Boughton, Mar 25 '14 at 23:21
Don't use `dateFormat()` when passing a date to a DB. Use a date object (in your case just `now()` is fine). Also - as others have said - do not hardcode your parameter values into your SQL string, pass them as parameters with ``. Do all that first, then let's have a look at your error(s)... — Adam Cameron, Mar 25 '14 at 23:21

score 3 · Accepted Answer · edited Mar 26 '14 at 04:44

3

The cfqueryparam cfsqltype to use for SQL Server datetime / smalldatetime values is cf_sql_timestamp. You can re-write the code as follows:

<CFSET dateTimes=DateFormat(Now(),"mm/dd/yyyy")>
<CFQUERY NAME="updateTime" DATASOURCE="#this_datasource#">
    UPDATE users
    SET ACTIVITYDATE = <cfqueryparam value="#dateTimes#" cfsqltype="cf_sql_timestamp">
    WHERE username = <cfqueryparam value="#Form.login_username#" cfsqltype="cf_sql_varchar">
    AND Password = <cfqueryparam value="#Form.Password#" cfsqltype="cf_sql_varchar">;
</CFQUERY>

Using cfqueryparam is recommended to avoid SQL injection vulnerabilities.

See: https://wikidocs.adobe.com/wiki/display/coldfusionen/cfqueryparam

edited Mar 26 '14 at 04:44

Leigh

28,765
10
55
103

answered Mar 25 '14 at 21:40

Jeremy

575
4
7

1

The dateTimes variable is a string, not a dateobject. If a ColdFusion value is to be passed to the db, now() is the appropriate value. – Dan Bracuk Mar 25 '14 at 22:36
2

ColdFusion will convert a string to a date - assuming it is in a common date format - when it passes data to the database with `cfqueryparam` – Scott Stroz Mar 26 '14 at 12:42
it seemed like this way of doing it worked. I have to double check with the DB as soon I get access, but I did a test query in the code to check the activity date and it gave back the correct activity date :-). – Sovr Sov Mar 26 '14 at 16:43

score 0 · Answer 2 · answered Mar 25 '14 at 21:45

0

Change this:

SET ACTIVITYDATE = CAST(#dateTimes# AS smalldatetime)

to this:

SET ACTIVITYDATE = GetDate()

or, if you don't want the time component

SET ACTIVITYDATE = cast(GetDate() as date)

While you can send a ColdFusion variable, it's not necessary so why bother?

answered Mar 25 '14 at 21:45

Dan Bracuk

20,699
4
26
43

2

`cfqueryparam>` should always be used. – Matt Busche Mar 25 '14 at 22:16
3

Not when using database functions to get the current date and time. – Dan Bracuk Mar 25 '14 at 22:35
it's still a good habit to get into especially when the user has posted no queryparam's in their code at all. – Matt Busche Mar 25 '14 at 22:41
@SovrSow - Just to clarify, you should always use `cfqueryparam` with variables like in your original example. Primarily to prevent sql injection, but it has other benefits as well. However, it *cannot* be used when the value is a database function like `getDate()`. The reason is `cfqueryparam` prevents sql from being executed, so those two are mutually exclusive. – Leigh Mar 26 '14 at 02:22

why isn't this coldfusion query not working?

2 Answers2