php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #25777 char and varchar fields are being rtrimmed (and also ltrimmed?) using freetds
Submitted: 2003-10-07 09:43 UTC Modified: 2004-06-25 00:13 UTC
Votes:4
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: duh at dowebwedo dot com Assigned:
Status: Closed Package: MSSQL related
PHP Version: 4.3.4RC1 OS: Debian GNU/Linux 3.0
Private report: No CVE-ID: None
 [2003-10-07 09:43 UTC] duh at dowebwedo dot com
Description:
------------
I am busy developing a new improved version of our intranet running on Apache/php/Debian and moved in this version from ODBC to MSSQL/Sybase connections (ODBC gave a lot of overhead and appeared being quite slow).

In several intranet functions we aquire data from the Exact financial suite (http://www.exactsoftware.com) which is largely used in The Netherlands and abroad and which currently uses MSSQL as a database backend. In the most recent versions of exact you can still see their MS-DOS history (exact used btrieve and several other MS-DOS databases in the old days) because several columns are still padded to the maximum column width. Hence the word "hi" in a varchar(8) would be stored as "hi      " (hi+6 spaces). 

Now when using mssql_* functions in php over freetds all selected values are right trimmed, so SELECT hi FROM table will return "hi" instead of the actual data in the table "hi      ". I have currently only tried selecting, i don't know what happens when inserting (probably the same?).

Obviously, this is not what I want since this would lead to data inconsistency (in your financial system!) and an unuseable financial system (which ofcourse is the worst that could happen to a company).

At first I thought it was due to the fact that sybase used to right trim these values so freetds does it as well for compatibility's sake. But when I executed a query command line through the tsql (/usr/local/bin/tsql) application it appeared that then the values were NOT being right trimmed. So appearantly the interface between freetds and my application (which in my opinion can only be php) does the trimming of values with spaces.

Ofcourse one could say that I need to trim or append spaces to these values myself, but since most data and software is dynamic and only some (var)char fields will get appended and some don't, there is no way of telling which columns should be appended (or prepended) and which shouldn't.

For the sake of data consistency I would either like to see this bug fixed, or -if it is no bug but a feature- see a configuration option being introduced to overrule this trimming.

Reproduce code:
---------------
In php the following code will return trimmed values:
$db    = mssql_connect('server','user','pass'); 
$result= mssql_query("SELECT TOP 1 medewerker FROM Efw_001.[dbo].Prres1 WHERE medewerker IS NOT NULL AND medewerker LIKE '%  %'");
$a      = mssql_fetch_assoc($result); 
print_r($a);

but commandline this values are not being trimmed:
# tsql -S server -U user -P pass
locale is "C"
charset is "ANSI_X3.4-1968"
Msg 5703, Level 0, State 1, Server NTS1, Line 0
Changed language setting to us_english.
1> SELECT TOP 1 medewerker FROM Efw_001.[dbo].Prres1 WHERE medewerker IS NOT NULL AND medewerker LIKE '%  %'
2> go
medewerker
'100     ' (size=8)
1> SELECT TOP 1 rtrim(medewerker) as trimmed_medewerker FROM Efw_001.[dbo].Prres1 WHERE medewerker IS NOT NULL AND medewerker LIKE '%  %'
2> go
trimmed_medewerker
'100' (size=3)

(I added single quotes around the results and wrote the sizes behind it so you can see the difference between the two queries)

Expected result:
----------------
Obviously I expect to get exactly the same data that is stored in the database instead of modified (trimmed) data.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-10-14 23:32 UTC] iliaa@php.net
This bug has been fixed in CVS.

In case this was a PHP problem, snapshots of the sources are packaged
every three hours; this change will be in the next snapshot. You can
grab the snapshot at http://snaps.php.net/.
 
In case this was a documentation problem, the fix will show up soon at
http://www.php.net/manual/.

In case this was a PHP.net website problem, the change will show
up on the PHP.net site and on the mirror sites in short time.
 
Thank you for the report, and for helping us make PHP better.


 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 03:01:29 2024 UTC