#28908 [Com]: Strange SQL server error when query contains special chars
ID: 28908
Comment by: kimmo at kei-kun dot net
Reported By: skissane at iips dot mq dot edu dot au
Status: Assigned
Bug Type: MSSQL related
Operating System: Linux
PHP Version: 4.3.7
Assigned To: fmk
New Comment:
After a while of Googling I found the following fix:
Start the "Microsoft SQL Server/Client Network Utility" and go to the
tab "DB-Library Option". Uncheck the "Automatic ANSI to OEM
convertion".
Perhaps this information could be added to the php docs in the chapter
regarding MSSQL, since I think many people using MSSQL+PHP and not
speaking English as their first language could benefit from this
information.
Previous Comments:
----
[2004-08-20 17:56:05] kimmo at kei-kun dot net
I have the same problem.
I've been testing some things with MySQL and MSSQL to see how they
behave in accordance to this issue.
I'm using a Swedish test string with special chars in it to see how the
db reacts (I'm from Sweden): 'Detta är en test av special bokstäver.
Även stora. ÅÄÖåäöÜüÉé.'
Testing with MySQL:
==
Inserting the string with the commandline MySQL client:
--
Selecting the text from the client results in:
'Detta är en test av special bokstäver. Även stora. ÅÄÖåäöÜüÉé.'
Selecting the text from Php results in:
'Detta r en test av special bokstver. ven stora. .'
The reverse - inserting the string from Php:
---
Selecting the text from Php results in:
'Detta är en test av special bokstäver. Även stora. ÅÄÖåäöÜüÉé.'
Selecting the text from the MySQL client results in:
'Detta õr en test av special bokstõver. -ven stora. +-ÍÕõ÷_³+Ú.'
---
These test was done on a machine running Windows, with Php and MySQL.
The same test on a machine running Linux didn't have ANY ISSUE OF
CHARATER CONVERTION AT ALL.
I ran all these tests with MSSQL too (on Windows only), and got similar
results as MySQL on Windows.
Original string: 'Detta är en test av special bokstäver. Även stora.
ÅÄÖåäöÜüÉé.'
Returned string: 'Detta är en test av special bokstäver. -ven stora.
+-Öåäö_ü+é.'
The problem seems to be that while MySQL handles all these special
characters convertions without damaging the strings - MSSQL don't.
Probably because it doesn't store some of the chars that the
non-standard-ascii chars get converted into properly. Note that not all
special chars are corrupted, only some of them.
It seems that Php is internally using ISO-8859-1 while Windows and the
database applications on Windows are using the ANSI encoding.
I don't know if there is any easy solution, so for the moment I am
forced to create a workaround by encoding all the text strings into
htmlenteties. The problem with this solution is that I have to disable
the search function on the site that I am setting up :(.
If anyone knows a better solution to this problem - feel free to email
me.
[2004-08-03 19:07:56] webmaster at path dot org
PHP v 4.3.4:
I am having the same issue:
'./configure' '--with-apxs2=/usr/local/apache2/bin/apxs' '--with-mysql'
'--with-mssql=/usr/local'
Freetds v 0.61.2
using protocol 7.0
Connecting to MS SQL Server 2000 (on a separate box, of course)
Ran the same test as above:
$r = mssql_connect("","","");
mssql_query("SELECT '" . chr(183) . "'",$r);
Get the following error:
Warning: mssql_query(): message: Unclosed quotation mark before the
character string '??'. (severity 15) in
/usr/local/apache2/htdocs/apps/consucap/test.php on line 13
Warning: mssql_query(): message: Line 1: Incorrect syntax near '??'.
(severity 15) in /usr/local/apache2/htdocs/apps/consucap/test.php on
line 13
Warning: mssql_query(): Query failed in
/usr/local/apache2/htdocs/apps/consucap/test.php on line 13
[2004-06-29 21:04:02] [EMAIL PROTECTED]
I have testet your script with PHP4 and PHP5 (latest CVS) with the
latest cvs version of FreeTDS and I'm onable to reproduce the error.
Could you try to use the CVS version of PHP ?
[2004-06-24 08:50:10] skissane at iips dot mq dot edu dot au
Also, I tested this, it works fine on 4.3.6. So this is a regression in
4.3.7.
[2004-06-24 08:06:45] skissane at iips dot mq dot edu dot au
Description:
running freetds 0.63
./configure:
'./configure' '--with-apxs2=/usr/sbin/apxs'
#28908 [Com]: Strange SQL server error when query contains special chars
ID: 28908
Comment by: kimmo at kei-kun dot net
Reported By: skissane at iips dot mq dot edu dot au
Status: Assigned
Bug Type: MSSQL related
Operating System: Linux
PHP Version: 4.3.7
Assigned To: fmk
New Comment:
I have the same problem.
I've been testing some things with MySQL and MSSQL to see how they
behave in accordance to this issue.
I'm using a Swedish test string with special chars in it to see how the
db reacts (I'm from Sweden): 'Detta är en test av special bokstäver.
Även stora. ÅÄÖåäöÜüÉé.'
Testing with MySQL:
==
Inserting the string with the commandline MySQL client:
--
Selecting the text from the client results in:
'Detta är en test av special bokstäver. Även stora. ÅÄÖåäöÜüÉé.'
Selecting the text from Php results in:
'Detta r en test av special bokstver. ven stora. .'
The reverse - inserting the string from Php:
---
Selecting the text from Php results in:
'Detta är en test av special bokstäver. Även stora. ÅÄÖåäöÜüÉé.'
Selecting the text from the MySQL client results in:
'Detta õr en test av special bokstõver. -ven stora. +-ÍÕõ÷_³+Ú.'
---
These test was done on a machine running Windows, with Php and MySQL.
The same test on a machine running Linux didn't have ANY ISSUE OF
CHARATER CONVERTION AT ALL.
I ran all these tests with MSSQL too (on Windows only), and got similar
results as MySQL on Windows.
Original string: 'Detta är en test av special bokstäver. Även stora.
ÅÄÖåäöÜüÉé.'
Returned string: 'Detta är en test av special bokstäver. -ven stora.
+-Öåäö_ü+é.'
The problem seems to be that while MySQL handles all these special
characters convertions without damaging the strings - MSSQL don't.
Probably because it doesn't store some of the chars that the
non-standard-ascii chars get converted into properly. Note that not all
special chars are corrupted, only some of them.
It seems that Php is internally using ISO-8859-1 while Windows and the
database applications on Windows are using the ANSI encoding.
I don't know if there is any easy solution, so for the moment I am
forced to create a workaround by encoding all the text strings into
htmlenteties. The problem with this solution is that I have to disable
the search function on the site that I am setting up :(.
If anyone knows a better solution to this problem - feel free to email
me.
Previous Comments:
[2004-08-03 19:07:56] webmaster at path dot org
PHP v 4.3.4:
I am having the same issue:
'./configure' '--with-apxs2=/usr/local/apache2/bin/apxs' '--with-mysql'
'--with-mssql=/usr/local'
Freetds v 0.61.2
using protocol 7.0
Connecting to MS SQL Server 2000 (on a separate box, of course)
Ran the same test as above:
$r = mssql_connect("","","");
mssql_query("SELECT '" . chr(183) . "'",$r);
Get the following error:
Warning: mssql_query(): message: Unclosed quotation mark before the
character string '??'. (severity 15) in
/usr/local/apache2/htdocs/apps/consucap/test.php on line 13
Warning: mssql_query(): message: Line 1: Incorrect syntax near '??'.
(severity 15) in /usr/local/apache2/htdocs/apps/consucap/test.php on
line 13
Warning: mssql_query(): Query failed in
/usr/local/apache2/htdocs/apps/consucap/test.php on line 13
[2004-06-29 21:04:02] [EMAIL PROTECTED]
I have testet your script with PHP4 and PHP5 (latest CVS) with the
latest cvs version of FreeTDS and I'm onable to reproduce the error.
Could you try to use the CVS version of PHP ?
[2004-06-24 08:50:10] skissane at iips dot mq dot edu dot au
Also, I tested this, it works fine on 4.3.6. So this is a regression in
4.3.7.
[2004-06-24 08:06:45] skissane at iips dot mq dot edu dot au
Description:
running freetds 0.63
./configure:
'./configure' '--with-apxs2=/usr/sbin/apxs' '--with-mssql=/usr/local'
'--with-mysql' '--with-curl' '--with-dom' '--with-zlib' '--enable-xslt'
'--with-xslt-sablot' '--with-sablot-js' '--enable-dba' '--with-cdb'
'--with-gd' '--with-socket' '--with-ldap'
Reproduce code:
---
","","");
mssql_query("SELECT '" . chr(183) . "'",$r);
The above query works fine when run directly from Query Analyser.
(where i
