Hi,
I hadn't sent an answer to this message yet, here it goes..
Aaron Stone wrote:
Oh, wow ok oops. Are you developing with PostgreSQL as your primary platform?
That would be really good, because I'm working with MySQL :-)
I'm developing on PostgreSQL mostly yes. Very convenient that you're
working on 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 :-\
Yep, we need to do more checks for quoting to prevent SQL injection
attacks (or prevent them from succeeding anyway)
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!
I'd like to use a printf()-like function for doing queries. At the
moment we're using snprintf() to create a query string, and then we do a
db_query() to execute the query. I think it would be nice to go to a
scheme where we could do something like this:
if (db_query("SELECT user_idnr FROM users WHERE clientid = %llu",
clientid) < 0) {
etc.
The db_query() function then has to take care of all quoting.
I can see that we can have some problems with this, since there are some
functions that create queries on the fly (e.g. functions like
db_set_msgflag_range()), but those functions can probably be refactored.
Ilja
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
