Ok, so DATE_FORMAT() is a weird one. Consistent and good, but I think I was
expecting usefulness in context, which is a MySQL kind of thing to do, rather
than just plain consistency :-P
It can be used in the VALUES section of an INSERT or in an UPDATE, but I'm not
sure why you would do that. The format must match the column, and avoiding the
hardcoding of the date format was the whole point of all this, although it
gives a little more flexibility for interpreting the date string.
You're correct that STR_TO_DATE() is what we want to be using, but it's MySQL
4.1.1 and higher. Bummer.
Aaron
""Aaron Stone"" <[EMAIL PROTECTED]> said:
> Oh, wow ok oops. Are you developing with PostgreSQL as your primary platform?
> That would be really good, because I'm working with MySQL :-)
>
> The corresponding DATE_FORMAT() function in MySQL does happily go into any
> part of the query, so rather than just quoting the date for MySQL, I'll add in
> the actual function text.
>
> All of this gives rise to a major concern: SQL injection. If we can't easily
> inspect queries to make sure that values are quoted, because some should be
> quoted at the top level and some are quoted further down, it will be very hard
> to discover missing quotes except by trial and error :-\
>
> What I'd like to do down the road is to use a SQL abstraction that does all of
> the quoting and escaping to affirmatively prevent injection attacks and to
> keep from needing to remember which %s should get quotes and which shouldn't!
>
> Aaron
>
>
> Ilja Booij <[EMAIL PROTECTED]> said:
>
> > Hmm,
> >
> > this presents a problem. The statement did not have the quotation marks,
> > because on PostgreSQL the query looked liked this:
> >
> > INSERT INTO physmessage (messagesize, internal_date) VALUES ('0',
> > TO_TIMESTAMP('2004-03-15 08:00:01', 'YYYY-MM-DD HH:MI:SS'))
> >
> > So, no quotes around the TO_TIMESTAMP stuff. We'll have to add the
> > quotes for MySQL on another level, not in the query itself, but in the
> > db_char2date() function.
> >
> > I'll get to it.
> >
> > Ilja
> >
> > Aaron Stone wrote:
> >
> > > Looks like missing quotation marks. Check CVS again in a few minutes...
> > >
> > > Aaron
> > >
> > >
> > > Blake Mitchell <[EMAIL PROTECTED]> said:
> > >
> > >
> > >>This breaks with MySQL 4.0.18
> > >>
> > >>/var/log/maillog.1-Mar 13 17:50:41 sls-cd7p8 dbmail/imap4d[23029]:
> > >>COMMAND: [092G APPEND "Drafts" (\Draft) "13-Mar-2004 12:34:48 -0800"
> > >>{2483}]
> > >>/var/log/maillog.1-Mar 13 17:50:41 sls-cd7p8 dbmail/imap4d[23029]:
> > >>dbmysql.c,db_query: query [INSERT INTO physmessage (messagesize,
> > >>internal_date) VALUES ('0', 2004-03-13 12:34:48)] failed
> > >>/var/log/maillog.1:Mar 13 17:50:41 sls-cd7p8 dbmail/imap4d[23029]:
> > >>dbmysql.c,db_query: mysql_real_query failed: You have an error in your
> > >>SQL syntax. Check the manual that corresponds to your MySQL server
> > >>version for the right syntax to use near '12:34:48)' at line 1
> > >>/var/log/maillog.1-Mar 13 17:50:41 sls-cd7p8 dbmail/imap4d[23029]:
> > >>db.c,db_insert_physmessage_with_internal_date: insertion of physmessage
> > >>failed
> > >>/var/log/maillog.1-Mar 13 17:50:41 sls-cd7p8 dbmail/imap4d[23029]:
> > >>db.c,db_imap_append_msg: could not create physmessage with internal date
> > >>[2004-03-13 12:34:48]
> > >>/var/log/maillog.1-Mar 13 17:50:41 sls-cd7p8 dbmail/imap4d[23029]:
> > >>ic_append(): error appending msg
> > >>
> > >>Cheers,
> > >>Blake
> > >>
> > >>Ilja Booij wrote:
> > >>
> > >>
> > >>>Some more hacking done:
> > >>>
> > >>>IMAP APPEND now handles the optional flag list and internal date. I
> > >>>have the feeling it's a bit of a hack, but it works ;)
> > >>>
> > >>>This should probably tackle the problem of messages having wrong dates
> > >>>after having been copied (in an IMAP-client) from one server to another.
> > >>>
> > >>>Why this behaviour wasn't there before is not clear.. According to the
> > >>>RFC, it should've been in there.
> > >>>
> > >>>Please test it (and break it..)
> > >>>
> > >>>Ilja
> > >>>
> > >>
> > >>_______________________________________________
> > >>Dbmail-dev mailing list
> > >>[email protected]
> > >>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> > >>
> > >
> > >
> > >
> > >
> > _______________________________________________
> > Dbmail-dev mailing list
> > [email protected]
> > http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >
>
>
>
> --
>
>
>
> _______________________________________________
> Dbmail-dev mailing list
> [email protected]
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>
--
