php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #28908 Strange SQL server error when query contains special chars
Submitted: 2004-06-24 08:06 UTC Modified: 2004-11-22 19:30 UTC
Votes:4
Avg. Score:5.0 ± 0.0
Reproduced:4 of 4 (100.0%)
Same Version:2 (50.0%)
Same OS:2 (50.0%)
From: skissane at iips dot mq dot edu dot au Assigned: fmk (profile)
Status: Not a bug Package: MSSQL related
PHP Version: 4.3.7 OS: Linux
Private report: No CVE-ID: None
 [2004-06-24 08:06 UTC] 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:
---------------
<?
$r = mssql_connect("<servername>","<username>","<password>");
mssql_query("SELECT '" . chr(183) . "'",$r);

The above query works fine when run directly from Query Analyser.

(where <servername> is a SQL Server 2000; freetds protocol version used 8.0.)

Expected result:
----------------
No error.

Actual result:
--------------
Warning: mssql_query(): message: Unclosed quotation mark before the character string '?? (severity 15) in /home/skissane/adm/freetds-0.63.dev.20040622/phptest.php on line 3

Warning: mssql_query(): message: Line 1: Incorrect syntax near '?? (severity 15) in /home/skissane/adm/freetds-0.63.dev.20040622/phptest.php on line 3

Warning: mssql_query(): Query failed in /home/skissane/adm/freetds-0.63.dev.20040622/phptest.php on line 3


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-06-24 08:50 UTC] 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-29 21:04 UTC] fmk@php.net
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-08-03 19:07 UTC] 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("<servername>","<username>","<password>");
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-08-20 17:56 UTC] 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 bokst?ver. ?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-20 18:28 UTC] kimmo at kei-kun dot net
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.
 [2004-11-22 19:30 UTC] fmk@php.net
Unable to reproduce and no feedback relevant to the os in use.
the 'Automatic ANSI to OEM convertion' option only applies to win32 clients and depends on how PHP is called.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Mon Dec 09 06:01:27 2024 UTC