Inserting a date value into MySQL using ColdFusion

Get Started
1

Hello,

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?

2

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

e.g.

‘2011-12-16’
‘2011/12/16’
‘2011@12@16’

the separator doesn’t matter

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

3

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?

4

most likely

but you should still do some research on sql injection

5

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.

6

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

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

7

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.

8

The first link from the Google results had some very good info:
http://coldfusion.sys-con.com/node/165921

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.
http://www.thedevshack.com/preventing-xss-cross-site-scripting-attacks-in-coldfusion/

9

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!!!

FAIL, FAIL, FAIL

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”

10

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.

11

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