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 <ilja@ic-s.nl> 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 <blake@barkingspoon.com> 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
>>>>Dbmail-dev@dbmail.org
>>>>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>>>>
>>>
>>>
>>>
>>>
>>_______________________________________________
>>Dbmail-dev mailing list
>>Dbmail-dev@dbmail.org
>>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>>
>
>
>
>