Using PHP's odbc to grab a varchar(max) column

Question

I'm putting together a PHP website for a class project, and we are using a MS SQL Server 2008 database to populate fields on the site. However, one of the fields is outputting garbage onto the page instead of what is actually stored in the database.

The field in question, called description, is a varchar(MAX) field; a stored procedure queries the database for a tuple and dumps the values from its table into text boxes on the page; the description field is output to a textarea control.

Here is the PHP that handles pulling the information from the database:

$res = odbc_exec($dbhandle, "exec dbo.usp_ProgramGet " . $_GET["program"]);
$id = $_GET["program"];
$name = odbc_result($res, "title");
$desc = odbc_result($res, "description");

The $name variable works as expected (in the database, it is of type char(15)). However, if (for example) the description field contains "This is a test" then $desc will result in "�$ime�����", which is what gets dumped into the textarea control, instead of what's stored in the database.

I've searched all over and found no solutions to this problem yet, although it sounds like a bug in PHP itself although I'm not sure.

---

Update

I am using SQL Server queries to update the varchar values. I tried putting in a really long string and I got this:

�,ime�������stringDayToInt��É������à‰,���N={���������������������������������������������

"stringDayToInt" is the name of a PHP function I wrote that lives in a totally different file that got included into the page I'm trying out. Very bizarre.

Answer 1

I had the same Ugly problem on PHP 5.3...

The .php page displayed random characters where must be the column value.
Some times was ramdom characters and script sections of executed php page. This is not good.

SQL Server: MS SQL Server 2008 R2

My odbc connect driver: Driver={SQL Server Native Client 10.0}

My PHP version: PHP 5.3

I was looking around but I did not find the solution.
The workaround I tried and work for me is:

select cast(the_column_varcharmax as text) as column_name from table_name

Try it, I hope it works for you.

Answer 2

This bug is in MSSQL/ODBC/PHP for long time and I lost the hope it will be fixed in my life. The solution is converting it to text in the procedure:

select name, convert(text,description) as description. .....

I do it so for many years.

Answer 3

This is a known bug in PHP.

Either compile your own patched version, or use the workaround above.

Answer 4

I had the same problem with PHP 5.3 and MS SQL 2008 R2. Rather than workaround with a cast (which I did not find satisfactory), I switched drivers from ODBC to SQLSRV, which seems to work just fine.

Answer 5

Had a similar problem: php remove/identify this symbol �

Replacement Character

FFFD � REPLACEMENT CHARACTER

used to replace an incoming character whose value is unknown or unrepresentable in Unicode

UPDATE:

What does this output:

echo mb_detect_encoding($desc).' ';
var_dump($desc);

If mb_detect_encoding doesn't work

if(is_utf8($desc)) {
    echo "UTF-8 Encoded";
} else {
    echo "Oops, not UTF-8";
}

var_dump($desc);

function is_utf8($str) {
    $c=0; $b=0;
    $bits=0;
    $len=strlen($str);
    for($i=0; $i<$len; $i++){
        $c=ord($str[$i]);
        if($c > 128){
            if(($c >= 254)) return false;
            elseif($c >= 252) $bits=6;
            elseif($c >= 248) $bits=5;
            elseif($c >= 240) $bits=4;
            elseif($c >= 224) $bits=3;
            elseif($c >= 192) $bits=2;
            else return false;
            if(($i+$bits) > $len) return false;
            while($bits > 1){
                $i++;
                $b=ord($str[$i]);
                if($b < 128 || $b > 191) return false;
                $bits--;
            }
        }
    }
    return true;
}