Skip navigation
Currently Being Moderated

"Syntax error in INSERT INTO statement." comment posting error.

Apr 25, 2012 6:43 AM

I had this working perfectly on MySQL. Then I was told I needed to do it with an mdb database in Access, now errors are popping up like crazy. I fixed all except this one, I get a 'Syntax error in INSERT INTO statement.' when I try to post a comment on a post in my mini-blog site.

 

The thing is, I use pretty much the exact same code for the comments that I do for the blog posts themselves. The posts work fine, the comments don't.

 

It says the error is on my addcomment_process page on like 119. It's the following (line 119 is underlined, the bolded line is also bolded in the error message):

 

<cfquery name="input_comments"

                    datasource="#DSN#"

                    username="#DSNUSER#"

                    password="#DSNPASS#">

 

INSERT INTO comments(

                         commenter,

                         comment,

                         datetime,

                         post_id

                    )

               VALUES(

                         '#form.commenter#',

                         '#form.comment#',

                         '#form.datetime#',

                         '#form.post_id#'

                   )

</cfquery>

 

 

It's getting all the data fine, just not inserting it, because it says:

"INSERT INTO comments( commenter, comment, datetime, post_id ) VALUES( 'Anonymous', 'test comment.', '25-Apr-12 10:55 PM', '2' )"

 

 

It worked perfectly when it was a MySQL database, now I keep getting errors with this one part of the site now that it's MDB. Anyone know what's going on?

 
Replies
  • Currently Being Moderated
    Apr 25, 2012 6:45 AM   in reply to Abidubbah

    Presumably the post_id column is numeric? In which case, you shouldn't have quotes around it. Also, use queryparams.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:20 AM   in reply to Abidubbah

    In which case it'll be that Access has a different date format to MySQL, and you're trying to push the string "'25-Apr-12 10:55 PM" into Access, which it's not understanding. To get around this, you need to treat your variable as a true date/time object, rather than a formatted string.

     

    CFQueryParams should be used on absolutely every database platform, they'll stop your website being vulnerable to SQL Injection attacks for one, which at the moment you're wide open to. Replace your query with something like this:

     

    <cfquery name="input_comments" datasource="#DSN#" username="#DSNUSER#" password="#DSNPASS#">

          INSERT INTO comments(

                             commenter,

                             comment,

                             datetime,

                             post_id

                        )

                   VALUES(

                             <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.commenter#" />,

                             <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.comment#" />,

                             now(),

                             <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.post_id#" /> )

    </cfquery>

     

    The QueryParams will handle the data types for you, and should pass values around more safely and with fewer issues. Assuming you just want to store the current date and time you don't even need to get ColdFusion involved, as databases have their own functions for this. I believe Access' version is simply NOW(), so I've put that in your query.

     

    Give the above a go.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:21 AM   in reply to Abidubbah

    datetime is a reserved word in Access.

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:22 AM   in reply to Adam Cameron.

    Oh, and why the heck are you downgrading from MySQL to Access??!

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:22 AM   in reply to Adam Cameron.

    Eurgh, Adam knows about Access. I think so much less of you now.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:23 AM   in reply to Adam Cameron.

    Oh, and why the heck are you downgrading from MySQL to Access??!

    This is also a very good question.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:27 AM   in reply to Owain North

    Eurgh, Adam knows about Access. I think so much less of you now.

    Adam knows about Google.

     

    Adam knows an appropriate amount about Access.  IE: nothing.

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:28 AM   in reply to Adam Cameron.

    Adam knows an appropriate amount about Access.  IE: nothing.

    Incorrect - you know the one thing *everyone* should know about Access - it is the darkest spawn of the devil and deserves to be burned with fire.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:31 AM   in reply to Abidubbah

    I'm downgrading because, from what I understand, the server this site is going on doesn't have the ability to use MySQL databases and it needs to be an Access database or something. At least, that's what I was told.

    Joking aside, Access is not server software and should never be anywhere near a server; I'd question whoever made this decision as it's a bad one. MySQL is free and infinitely superior.

     

    I'm glad you fixed your issue though, but please do use params every time you enter a variable into a query.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 7:36 AM   in reply to Abidubbah

    I agree with Owain. In addition to what he says, the datetime value should probably not have quotes either.

     

    Update: Ignore. Despite this contributing nothing, my browser took too long to post it!

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 25, 2012 9:10 AM   in reply to Adam Cameron.

    Regarding, "Adam knows an appropriate amount about Access. IE: nothing."

     

    I know how to use Access.  For Abudubbah, if you have to do this again, there might be a better way.

     

    If you have an odbc connection to the MySql database, you can create linked tables in your Access db.  Then you can do your insert query right in Access and bypass ColdFusion altogether.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points