Heine

  • Home
  • Drupal
  • About
Home

Solving getting bogus dates via MSSQL_QUERY

Heine —Thu, 2012/11/29 - 19:24

Summary: If you get bogus dates back from MSSQL, make sure PHP is compiled against the FreeTDS libary that's currently in use. If not, recompile PHP.

I often work on legacy applications where an interface between Drupal and a database residing on a SQL server are necessary. Due to the nature of legacy projects, this often means working against the 2000 version of the database system.

Recently, an issue occurred on the production server where we save a datetime in the language neutral format YYYYMMDD to prevent issues with LANGUAGE and DATEFORMAT settings (think d-m-Y vs m/d/Y etc). We also use mssql.datetimeconvert set to Off to get dates that can reliably be interpreted by a computer system.

On this particular server, we got really strange results from the SQL server.

var_dump(mssql_fetch_object(mssql_query("SELECT CAST('19780930' AS datetime)")));

The above sample script would result in an obviously bogus datetime:

object(stdClass)#1 (1) {
  ["computed"]=>
  string(19) "1978-03-09 32767:07"
}

Another server operating on the same database, as the same database user would return correct results:

object(stdClass)#1 (1) {
  ["computed"]=>
  string(19) "1978-09-30 00:00:00"
}

This clearly pointed to a factor outside of the database and got us looking into the PHP install on the problematic server. The connection between Drupal and the SQL server is done via the MSSQL PHP driver from the php-sybase package and depends upon the FreeTDS library. The final conclusion of our investigation was that PHP was compiled against a different version of FreeTDS and needed to be recompiled against the current one.

Doing so solved our issues.

  • MSSQL
  • FreeTDS
  • datetime
  • date

Recent posts

  • Other vectors for SA-CORE-2014-005?
  • Lazy loading: hook_hook_info is for hook owners only.
  • "Always offline" problem in EA's Origin due to antivirus
  • From bug to exploit - Bakery SSO
  • Solving getting bogus dates via MSSQL_QUERY
more

Security reviews

I provide security reviews of custom code, contributed modules, themes and entire sites via LimoenGroen.

Contact us for a quote.

Follow @Ustima

Copyright © 2016 by Heine Deelstra. All rights reserved.

  • Home
  • Drupal
  • About