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

[Guest]

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?

[Owainnorth]

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

[Guest]

I removed the quotes, nothing changed. I'm fairly new to this, I thought queryparams were a MySQL thing? How would I use them in this situation with mdb?

[Owainnorth]

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.

[Adam Cameron. · Accepted solution]

datetime is a reserved word in Access.

--

Adam

[Adam Cameron.]

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

--

Adam

[Owainnorth]

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

This is also a very good question.

[Owainnorth]

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

[Adam Cameron.]

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

[Owainnorth]

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.

[Dan_Bracuk]

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.

[Guest]

Thank you so much, I changed datetime to post_time and it worked. 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.

Thank you both.

[Owainnorth]

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.

[BKBK]

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!