• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Error Executing Database Query. Incorrect integer value

New Here ,
Aug 03, 2015 Aug 03, 2015

Copy link to clipboard

Copied

I am experiencing an error trying to run the following ColdFusion code:

<cfif IsDefined('FORM.add_button.y')>

<cfquery datasource="#sDSN#">

    INSERT INTO lcf_event(event_id,

                          event_date_time,

                          title,

                          description,

                          type,

                          user_id)     

   VALUES('#CreateUUID()#',

         #CreateODBCDateTime(CreateDateTime(FORM.year, FORM.month, FORM.day, FORM.hour, FORM.minute, 0))#,

         '#FORM.title#',

         '#FORM.description#',

         '#FORM.type#',

         '#GetAuthUser()#')

</cfquery>

<cflocation url="event_calendar.cfm" />

I am using CF11 has the function CreateODBCDateTime has been changed in CF11? I read a post about this function not working in CF8 and CF9 but working in CF7!.

I appreciate if someone can shed some light on this.

Thank you in advance.

Views

374

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 03, 2015 Aug 03, 2015

Copy link to clipboard

Copied

Can you include your stack trace?  Are you sure it is failing on the CreateODBCDateTime()?  Was it the function that threw an error or the query?  Also, I'd recommend using <cfqueryparam />.  Not only does this keep you safer from SQL injection, but it will help you correctly format your parameters (cfsqltype).

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 05, 2015 Aug 05, 2015

Copy link to clipboard

Copied

Thank you for your reply.

I am almost certain it must be a query issue.  But I read somewhere on the web the CreateODBCDateTime() function is not converting the data correctly for the SQL to process.  I let you review the code below and maybe you notice something I might have missed!

<cfif IsUserInRole('basic')>

    <cflocation url="event_calendar.cfm" />

</cfif>

<cfparam name="URL.year" default="#Year(Now())#" />

<cfparam name="URL.month" default="#Month(Now())#" />

<cfparam name="URL.day" default="#Day(Now())#"/ >

<cfset nHour = 12 />

<cfset nMinute = 0 />

<cfset sTitle = "" />

<cfset sDescription = "" />

<cfset sType = "public" />

<cfif IsDefined('FORM.event_id')>

    <cfquery datasource="#sDSN#" name="qEvent">

       

        SELECT *

        FROM lcf_event

        WHERE event_id = '#FORM.event_id#'

    </cfquery>

   

    <cfset URL.year = Year(qEvent.event_date_time) />

    <cfset URL.month = Month(qEvent.event_date_time) />

    <cfset URL.day = Day(qEvent.event_date_time) />

    <cfset nHour = Hour(qEvent.event_date_time) />

    <cfset nMinute = Minute(qEvent.event_date_time) />

    <cfset sTitle = qEvent.title />

    <cfset sDescription = qEvent.description />

    <cfset sType = qEvent.type />

</cfif>

<cfinclude template="../header.cfm" />

<cfif IsDefined('FORM.add_button.y')>

<cfquery datasource="#sDSN#">

    INSERT INTO lcf_event(event_id,

                          event_date_time,

                          title,

                          description,

                          type,

                          user_id)     

   VALUES('#CreateUUID()#',

         #CreateODBCDateTime(CreateDateTime(FORM.year, FORM.month, FORM.day, FORM.hour, FORM.minute, 0))#,

         '#FORM.title#',

         '#FORM.description#',

         '#FORM.type#',

         '#GetAuthUser()#')

</cfquery>

<cflocation url="event_calendar.cfm" />

<cfelseif IsDefined('FORM.update_button.y')>

    <cfquery datasource="#sDSN#">

    UPDATE lcf_event

    SET event_date_time = #CreateODBCDateTime(CreateDateTime(FORM.year, FORM.month, FORM.day, FORM.hour, FORM.minute, 0))#,

    title = '#FORM.title#',

    description = '#FORM.description#',

    type = '#FORM.type#'

    WHERE event_id = '#FORM.event_id#'

    </cfquery>

    <cflocation url="event_calendar.cfm" />

<cfelse>

<cfoutput>

    <form action="#CGI.SCRIPT_NAME#" method="post">

      <table>

        <tr>

          <td>

            <img src="/sitecoldfusion/images/spacer.gif" height="15" />

          </td>

        </tr>

        <tr>

          <td class="ltturq">Title:</td>

        </tr>

        <tr>

          <td class="ltturq">

            <input type="text" name="title" class="field" value="#sTitle#" />

          </td>

        </tr>

        <tr>

          <td>

            <img src="/sitecoldfusion/images/spacer.gif" height="15" />

          </td>

        </tr>

        <tr>

          <td class="ltturq">Description:</td>

        </tr>

        <tr>

          <td class="ltturq">

            <textarea name="description" cols="30" rows="4">#sDescription#</textarea>

          </td>

        </tr>

        <tr>

          <td>

            <img src="/sitecoldfusion/images/spacer.gif" height="15" />

          </td>

        </tr>

        <tr>

          <td class="ltturq">Type:</td>

        </tr>

        <tr>

          <td>

            <select name="type">

           

                <option value="public"<cfif sType EQ "public"> select</cfif>>public</option>

                <option value="private"<cfif sType EQ "private"> select</cfif>>private</option>

            </select>

          </td>

        </tr>

        <tr>

          <td>

            <img src="/sitecoldfusion/images/spacer.gif" height="15" />

          </td>

        </tr>

        <tr>

          <td class="ltturq">Date:</td>

        </tr>

        <tr>

          <td class="ltturq">

            <select name="year">

                <cfset nEndYear = URL.year + 4 />

                <cfloop from="#URL.year#" to="#nEndYear#" index="i">

                    <option value="#i#"<cfif i EQ URL.year> selected</cfif>>#i#</option>

                </cfloop>

            </select>

            <select name="month">

                <cfloop from="1" to="12" index="i">

                    <option value="#i#"<cfif i EQ URL.month> selected</cfif>>#MonthAsString(i)#</option>

                </cfloop>

            </select>

            <select name="day">

                <cfloop from="1" to="31" index="i">

                    <option value="#i#"<cfif i EQ URL.day> selected</cfif>>#i#</option>

                </cfloop>

            </select>

          </td>

        </tr>

        <tr>

          <td>

            <img src="/sitecoldfusion/images/spacer.gif" height="15" />

          </td>

        </tr>

        <tr>

          <td class="ltturq">Time:</td>

        </tr>

        <tr>

          <td class="ltturq">

            <select name="hour">

                <cfloop from="0" to="23" index="i">

                <cfif i EQ 0>

                  <cfset sHour = "12 AM" />

                  <cfelseif i EQ 12>

                  <cfset sHour = "12 PM" />

                  <cfelseif i GT 12>

                  <cfset sHour = (i- 12) & "PM" />

                  <cfelse>

                      <cfset sHour = i & "AM" />

                 </cfif>

                 <option value="#i#"<cfif i EQ nHour> selected</cfif>>#sHour#</option>

                </cfloop>

            </select>

            <select name="minute">

                <cfloop from="0" to="59" index="i">

                <option value="#i#"<cfif i EQ nMinute> selected</cfif>>#i#</option>

                </cfloop>

           </select>

          </td>

        </tr>

        <tr>

          <td>

            <img src="/sitecoldfusion/images/spacer.gif" height="15" />

          </td>

        </tr>

        <tr>

          <td>

          <cfif IsDefined('FORM.event_id')>

              <input type="hidden" name="event_id" value="#FORM.event_id#" />

              <input type="image" name="update_button" src="../images/update.gif" />

          <cfelse>

            <input type="image" name="add_button" src="../images/addEvent.gif" />

          </cfif>

          </td>

        </tr>

      </table>

    </form>

</cfoutput>

</cfif>

<cfinclude template="../footer.cfm" />

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Aug 06, 2015 Aug 06, 2015

Copy link to clipboard

Copied

I would like to emphasize what nic originally stated, use cfqueryparam -- "Not only does this keep you safer from SQL injection, but it will help you correctly format your parameters (cfsqltype)."

As it stands right now, from the browser using one of various plug-ins, set the value of FORM.type to "x', 1) /*" and see what happens. Or worse, set FORM.type to: "x', 1) delete lcf_event /*". The syntax may be a hair off but I suggest you make a database backup first.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 07, 2015 Aug 07, 2015

Copy link to clipboard

Copied

LATEST

Thank you for the reminder.  I have noted nic_tunney's recommendation.  I will implement that in the revised code.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Aug 04, 2015 Aug 04, 2015

Copy link to clipboard

Copied

What are the datatypes of your fields?

I notice you use GetAuthUser, which usually returns a string. You are inserting this into user_id, but is this field an integer?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 05, 2015 Aug 05, 2015

Copy link to clipboard

Copied

Hi haxtbh,

Your assumption is on the money, GetAuthUser  returns a string and I think there is an issue with the data conversion to integer.

I hope you guy can suggest a way to over come this .

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Aug 05, 2015 Aug 05, 2015

Copy link to clipboard

Copied

I'd imagine since getAuthUser() returns a string representing the username, change the datatype of the user_id field to varchar.  Either that or retrieve the user_id wherever you keep that and insert it instead.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Aug 06, 2015 Aug 06, 2015

Copy link to clipboard

Copied

Ideally you will want to the user ID to insert into the database if you are using it to reference another table with the user in. You will need to do a query of some kind using the GetAuthUser value to get the userID and then store that in the database when you do the insert. You could store the ID in a variable when you do the cflogin as well, to use later.

If you just want the actual string returned by GetAuthUser then you will need to change the database type on the field from int to varchar.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 07, 2015 Aug 07, 2015

Copy link to clipboard

Copied

Thank you for that will give that a try.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation