Start of week and WEEK mysql date function

[arena]

In wp-includes/general-template.php :

$start_of_week = get_option('start_of_week');
$query = "SELECT DISTINCT WEEK(post_date, $start_of_week) AS `week`, ........

last argument value can lead to malfunction

see ​http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_week

[arena]

Content of $info (sample)

(
    [effective_url] => http://127.0.0.1/wptest/wp-cron.php?doing_wp_cron
    [response_code] => 200
    [total_time] => 0.486
    [namelookup_time] => 0
    [connect_time] => 0.004
    [pretransfer_time] => 0.004
    [size_upload] => 0
    [size_download] => 0
    [speed_download] => 0
    [speed_upload] => 0
    [header_size] => 165
    [request_size] => 251
    [ssl_verifyresult] => 0
    [filetime] => -1
    [content_length_download] => 0
    [content_length_upload] => 0
    [starttransfer_time] => 0.486
    [content_type] => text/html
    [redirect_time] => 0
    [redirect_count] => 0
    [connect_code] => 0
    [httpauth_avail] => 0
    [proxyauth_avail] => 0
    [os_errno] => 0
    [num_connects] => 1
    [ssl_engines] => Array
        (
        )

    [cookies] => Array
        (
        )

    [error] => Operation timed out after 1 seconds with 30492 out of 222394 bytes received
)

[hakre]

nice find. SQL injection possible as well.

[hakre]

function wp_get_archives() uses unsafe queries all over the place. It's adviseable to replace them according to the project's ​database guidelines.

[hakre]

WEEK query patch, other queries still unpatched.

[hakre]

For the tickets ground cause I've applied a patch which already run tested here (at least as far as I was able to). Waiting for a review.

[arena]

@hackre

The point of this ticket is that get_option('start_of_week') can retrieve a value of 0 to 6 (Sunday to Saturday).

WEEK(post_date, $start_of_week) only accept following values :

[dd32]

Patch has a few issues.. Ignoring the fact the syntax is still wrong. One of the WEEK functions is still the same, and you cant jam a "LIMIT 1234" into a %d

Out of curiosity, why does WordPress have the option for start of week?

[Denis-de-Bernardy]

Replying to dd32:

Out of curiosity, why does WordPress have the option for start of week?

in some languages, the week "starts" on a monday rather than on a sunday.

[arena]

Replying to dd32:

Patch has a few issues.. Ignoring the fact the syntax is still wrong. One of the WEEK functions is still the same, and you cant jam a "LIMIT 1234" into a %d

Out of curiosity, why does WordPress have the option for start of week?

As far as i have seen this is for some archive stuff, but quite sure 99% of wp users don't use it !!!

[dd32]

in some languages, the week "starts" on a monday rather than on a sunday.

I was mainly wondering why we support it starting on Thursday.. I know Sunday and Monday are used - thus the support for it in WEEK, But wasnt aware of anything else.

[dd32]

I'm changing the milestone of this to 3.0, This code has been exactly the same since 2.1(earliest the file exists) so its not a regression.

The prepare()'s can be done at the same time, Since they're not using user-provided data (Rather, administrative options), I feel that this is not a change required in the maintainence release - I'll leave that open for a core dev to determine however.

'start_of_week' does however need to have a intval() applied to it on the sanitization list.. I'm going to create a new ticket for that - See #11623

[hakre]

Replying to arena:

@hackre

The point of this ticket is that get_option('start_of_week') can retrieve a value of 0 to 6 (Sunday to Saturday).

WEEK(post_date, $start_of_week) only accept following values :
...

Right, that does not match to each other. So my patch was completely bogus. What's needed is a proper translation from the WP 0-6 value (0: Sunday, 1: Monday, ... and 6: Saturday) to something usefull in a/the mysql query (I'm seeing nothing of that on the mysql manual page related to the date/time functions).

Replying to dd32:

in some languages, the week "starts" on a monday rather than on a sunday.

I was mainly wondering why we support it starting on Thursday.. I know Sunday and Monday are used - thus the support for it in WEEK, But wasnt aware of anything else.

Maybe this can be properly verified against valid sources (I share that opinion but I'd like not to introduce problems here) and if so, it will save us a lot because those arguments can then be properly alligned. Maybe we should reduce that dropdown to two values: Sunday = 0 and Monday = 1.

[arena]

Replying to hakre:

(I'm seeing nothing of that on the mysql manual page related to the date/time functions).

​http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_week

[arena]

if WordPress wants to support iso 8601 as the default

When using "WEEK" MySql function, second argument should have the value : 3

(there are no 0 (zero) week in iso 8601).

"WEEK" MySql function is used in two wp files :

wp-includes/general-template.php

wp-includes/query.php

More info :

​http://en.wikipedia.org/wiki/ISO_8601

​http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_week

[mdawaffe]

10397.diff

1. Simplifies get_weekstartend() and makes it actually work for weird start_of_week values.
2. Introduces _wp_mysql_week() to output the appropriate MySQL WEEK() expression given the start_of_week value.
3. Fixes the w parameter of WP_Query to match the date ranges output by wp_get_archives( weekly ). This means different blogs will display posts from different datetime ranges for queries like year=2010&w=17. This seems ok to me: different blogs already display posts from different datetime ranges for every time based query because of time zone differences.

[nacin]

Looks good. Thanks mdawaffe, kicking the tires on this now.

[nacin]

(In [14499]) Fix start of week and SQL WEEK handling. props mdawaffe, fixes #10397.