Inserting a date value into MySQL using ColdFusion


I am trying to insert a date into a MySQL DB.

The form value is 12/16/2011. I can display it many different ways using DateFormat so I know it is a valid date.

I insert it like … INSERT INTO table (date_start) VALUES (“#FORM.date_start#”)

But the value I get in the DB is 0000-00-00 00:00:00

What am I doing wrong?

rewrite your coldfusion code to format the date into year-month-day sequence, which is the only sequence mysql accepts



the separator doesn’t matter

p.s. don’t forget that using an unvetted FORM variable like that is inviting disaster with sql insertion

Thanks Rudy … and sorry but I am very new to MySQL, what do you mean unvetted form variable and sql insertion?

While I was waiting I found something that worked, I converted the form variable like this … #CreateODBCDate(FORM.date_start)# … is that ok?

most likely

but you should still do some research on sql injection

Thanks again for the help Rudy but I still can’t find a real world Example. Could you point me in the right direction?

Thanks again … As always.

whaaa? that search on google turned up ~nothing~ useful?

i don’t think i can do any better than that, i’m afraid

No not at all, I did the google thing and I think I get the gist. But I can’t find an example of how this done. Like how could inserting a form value exposé your database? So for example I have a simple SQL insert, how could a form value dump a databases value? That’s what I am having a hard time finding …

Thanks again Rudy … You have saved more then a few times.

The first link from the Google results had some very good info:

Remember that anyone can type ANYTHING into a form field, so they could easily type in their own SQL commands and do damage to your database.

If data inserted into the database by a user is destined to show up on a web page in the future, you should also look into XSS prevention.

omg omg

i can’t read that site, there’s a huge popup ad covering the page, and i can’t scroll to the “close this window” link because the ad keeps moving!!!


by the way, the classic example of sql injection is a cartoon that you can easily find by searching for the phrase “little bobby tables”

Thanks Guys … I think it get it a little better now.

I am still going to do some more digging, but at least I am on the right path.

haha! I have little bobby tables stuck on my file cabinet, along with “password strength” (also xkcd).