Page MenuHomePhabricator

Some GET parameter cause SQL errors in PostgreSQL (timestamps)
Closed, ResolvedPublic

Description

Author: mathias.behrle

Description:
I am currently at rev 20080:
From time to time I discover errors in my postgres logs, that are caused by
Google Bot. While these errors are not relevant for the regular use of
mediawiki, they shouldn't nevertheless be caused by any input syntax (and AFAIS
are not relevant with mysql as far as I could test on wikipedia itself).

The first one is caused by &offset
First example:
A database error has occurred Query: SELECT rev_timestamp FROM page, revision
WHERE page_id = rev_page AND rev_timestamp > '' AND rev_user_text='Mathiasb'
ORDER BY rev_timestamp ASC LIMIT 20 OFFSET 0 Function: Error: 1 ERROR: invalid
input syntax for type timestamp with time zone: ""

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'SELECT rev_time...',
'', false)
#1 /var/www/wiki/includes/SpecialContributions.php(112): Database->query('SELECT
rev_time...')
#2 /var/www/wiki/includes/SpecialContributions.php(240):
ContribsFinder->getPreviousOffsetForPaging()
#3 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialContributions(NULL,
Object(UnlistedSpecialPage))
#4 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#5 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#6 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#7 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#8 {main}

"GET
/wiki/index.php?title=Spezial:Beitr%C3%A4ge&target=Mathiasb&offset=2006-10-16+11%3A50%3A23&limit=20&go=prev
HTTP/1.1" 500 697 "-" "Mozilla/5.0 (compatible; Googlebot/2.1;

The second one is caused by parameter &go=prev:

URL:
index.php?title=Spezial:Beitr%C3%A4ge&target=Mathiasb&offset=2006-10-16+11%3A50%3A23&limit=20&go=prev

A database error has occurred Query: SELECT rev_timestamp FROM page, revision
WHERE page_id = rev_page AND rev_timestamp > '' AND rev_user_text='Mathiasb'
ORDER BY rev_timestamp ASC LIMIT 20 OFFSET 0 Function: Error: 1 ERROR: invalid
input syntax for type timestamp with time zone: ""

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'SELECT rev_time...',
'', false)
#1 /var/www/wiki/includes/SpecialContributions.php(112): Database->query('SELECT
rev_time...')
#2 /var/www/wiki/includes/SpecialContributions.php(240):
ContribsFinder->getPreviousOffsetForPaging()
#3 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialContributions(NULL,
Object(UnlistedSpecialPage))
#4 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#5 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#6 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#7 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#8 {main}


Version: unspecified
Severity: minor
OS: Linux
Platform: PC

Details

Reference
bz9131

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Event Timeline

bzimport raised the priority of this task from to Low.Nov 21 2014, 9:35 PM
bzimport set Reference to bz9131.

Fixed in r20104, thanks for the report.

Basically, we need to do a regex check for the timestamps Postgres uses as
opposed to the integer used by MySQL for timestamps.

mathias.behrle wrote:

Thanks a lot for the fix!

I think I reported not clear enough, and the errors I described are due to
different problems. I searched once again in my apache log and found the
following URLs left, which now still cause errors:

wiki/index.php?title=Spezial:Gesperrte_IPs&limit=250&offset=50

A database error has occurred Query: SELECT ipblocks.*,user_name FROM
ipblocks,mwuser WHERE (ipb_expiry>'2007-03-01 16:56:17 GMT') AND
(ipb_by=user_id) AND (ipb_timestamp<'50') ORDER BY ipb_timestamp DESC LIMIT 251
Function: IndexPager::reallyDoQuery (IPBlocklistPager) Error: 1 ERROR: date/time
field value out of range: "50" HINT: Perhaps you need a different "datestyle"
setting.

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: date/ti...', 1, 'SELECT ipblock...',
'IndexPager::rea...', false)
#1 /var/www/wiki/includes/Database.php(1073): Database->query('SELECT
ipblock...', 'IndexPager::rea...')
#2 /var/www/wiki/includes/Pager.php(184): Database->select(Array,
'ipblocks.*,user...', Array, 'IndexPager::rea...', Array)
#3 /var/www/wiki/includes/Pager.php(102): IndexPager->reallyDoQuery('50', 251,
false)
#4 /var/www/wiki/includes/Pager.php(296): IndexPager->doQuery()
#5 /var/www/wiki/includes/SpecialIpblocklist.php(204): IndexPager->getNumRows()
#6 /var/www/wiki/includes/SpecialIpblocklist.php(43): IPUnblockForm->showList('')
#7 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialIpblocklist(NULL,
Object(SpecialPage))
#8 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#9 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#10 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#11 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#12 {main}

wiki/index.php?title=Spezial:Beitr%C3%A4ge&target=Mathiasb&limit=20&go=prev

A database error has occurred Query: SELECT rev_timestamp FROM page, revision
WHERE page_id = rev_page AND rev_timestamp > '' AND rev_user_text='Mathiasb'
ORDER BY rev_timestamp ASC LIMIT 20 OFFSET 0 Function: Error: 1 ERROR: invalid
input syntax for type timestamp with time zone: ""

Backtrace:

#0 /var/www/wiki/includes/Database.php(677):
DatabasePostgres->reportQueryError('ERROR: invalid...', 1, 'SELECT rev_time...',
'', false)
#1 /var/www/wiki/includes/SpecialContributions.php(112): Database->query('SELECT
rev_time...')
#2 /var/www/wiki/includes/SpecialContributions.php(249):
ContribsFinder->getPreviousOffsetForPaging()
#3 /var/www/wiki/includes/SpecialPage.php(624): wfSpecialContributions(NULL,
Object(UnlistedSpecialPage))
#4 /var/www/wiki/includes/SpecialPage.php(430): SpecialPage->execute(NULL)
#5 /var/www/wiki/includes/Wiki.php(180): SpecialPage::executePath(Object(Title))
#6 /var/www/wiki/includes/Wiki.php(45):
MediaWiki->initializeSpecialCases(Object(Title), Object(OutputPage),
Object(WebRequest))
#7 /var/www/wiki/index.php(48): MediaWiki->initialize(Object(Title),
Object(OutputPage), Object(User), Object(WebRequest))
#8 {main}

robchur wrote:

(In reply to comment #1)

Basically, we need to do a regex check for the timestamps Postgres uses as
opposed to the integer used by MySQL for timestamps.

It would be a much better idea if we continue to use one standard timestamp
format in interfaces, etc. You can adapt the internal timestamp format
conversion functions to support PostgreSQL timestamps if desired.

Adjusted SpecialContributions to use a standard timestamp format and do some
to_char manipulation as needed for Postgres. See r20808.

Jdforrester-WMF subscribed.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.