WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 4 years ago

#10397 closed defect (bug) (fixed)

Start of week and WEEK mysql date function

Reported by: arena Owned by:
Milestone: 3.0 Priority: normal
Severity: major Version: 2.9
Component: Date/Time Keywords: has-patch dev-feedack
Focuses: Cc:

Description

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

Attachments (3)

10397.patch (1.1 KB) - added by hakre 5 years ago.
WEEK query patch, other queries still unpatched.
OnTheFly018.jpg (33.7 KB) - added by arena 5 years ago.
10397.diff (3.2 KB) - added by mdawaffe 4 years ago.

Download all attachments as: .zip

Change History (23)

comment:1 Denis-de-Bernardy5 years ago

  • Component changed from General to Date/Time
  • Milestone changed from Unassigned to 2.9

comment:2 ryan5 years ago

  • Milestone changed from 2.9 to Future Release

comment:3 arena5 years ago

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
)

comment:4 hakre5 years ago

nice find. SQL injection possible as well.

comment:5 hakre5 years ago

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

comment:6 hakre5 years ago

  • Milestone changed from Future Release to 2.9.1
  • Severity changed from normal to critical

hakre5 years ago

WEEK query patch, other queries still unpatched.

comment:7 hakre5 years ago

  • Keywords has-patch tested added
  • Version set to 2.9

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.

comment:8 hakre5 years ago

  • Keywords dev-feedack added

arena5 years ago

comment:9 arena5 years ago

@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 :
/raw-attachment/ticket/10397/OnTheFly018.jpg

comment:10 follow-ups: dd325 years ago

  • Keywords needs-patch added; has-patch tested removed

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?

comment:11 in reply to: ↑ 10 Denis-de-Bernardy5 years ago

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.

comment:12 in reply to: ↑ 10 arena5 years ago

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

comment:13 follow-up: dd325 years ago

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.

comment:14 dd325 years ago

  • Milestone changed from 2.9.1 to 3.0
  • Severity changed from critical to major

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

comment:15 in reply to: ↑ 13 ; follow-up: hakre5 years ago

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.

comment:16 in reply to: ↑ 15 arena5 years ago

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

comment:17 arena4 years ago

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

mdawaffe4 years ago

comment:18 mdawaffe4 years ago

  • Keywords has-patch added; needs-patch removed

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.

comment:19 nacin4 years ago

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

comment:20 nacin4 years ago

  • Resolution set to fixed
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.