1

I have a query:

SELECT * FROM Table

Which returns the following results:

ID Description
1 1234
2 12345
3 12—
4 123—
5 1234

In the table above, the Description field is defined as Description VARCHAR(5). I believe that my issue resides with the em dash — in that field. This issue also occurs with the en dash – but not a hyphen -.

The actual issue is occurring when I get to the PHP side of things. When I echo out the table returned by sqlsrv_query it will not print any fields after ID #3. I cannot figure out why it stops printing those results.

Here is the simplified code:

<?php
//Connect to the database
include_once("config.php");

//Execute the stored procedure
$query = "EXEC sp_query";
$stmt = sqlsrv_query($conn, $query);
?>
<!DOCTYPE HTML>
<head>
    <meta charset="utf-8">
</head>
<body>
<table>
    <thead>
        <th>ID</th>
        <th>Description</th>
    </thead>

    <?php
        //Loop through the current array
        while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            echo "<tr>";
                echo "<td>".$row['ID']."</td>";
                echo "<td>".$row['Description']."</td>";
            echo "</tr>";
        }

        //Release the result resource
        sqlsrv_free_stmt($stmt);
    ?>
</table>
</body>
</html>

Loading this web page returns the table:

ID Description
1 1234
2 12345
3 12—

Actual HTML Output

If I remove the one em dash from ID #4 then it will return the same exact results as the TSQL query shown at the top of this question. Is PHP somehow thinking that the max field length has been exceeded with the use of the em dash? Does anyone now how I can remedy this?

Things I have tried:

  • Using different charsets
  • CASTing the Description field to VARCHAR(MAX) in the stored procedure
  • Selecting only the first 2 characters from the field LEFT(Description, 2)
  • Removing the emdash before the echo using PHP function str_replace

None of these solved my issue. It may also be worth noting that the sqlsrv_errors function does not return any errors for the stored procedure.

Painguin
  • 1,027
  • 14
  • 26
  • 1
    Since the problem is with the HTML your code outputs, I'd take a look there first – and include it in the question – before assuming it's something on the database side. You should also be using `htmlspecialchars()` for any output to HTML. – miken32 May 26 '21 at 15:32
  • 1
    You question says "I have a query: `SELECT * FROM Table`" but then your code uses a different query. Might want to confirm that your stored procedure can handle Unicode characters. – miken32 May 26 '21 at 15:38
  • @miken32 Thanks for the input! I have included the HTML output in the newest edit. I have also attempted to replace the stored procedure with the actual query in the code and got the same results. – Painguin May 26 '21 at 15:55
  • 1
    Well first thing I would try is proper escaping of your output, then go through the answers [here](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through). If it's only happening with non-ASCII characters, it seems pretty clear that Unicode is your problem. Check especially the encoding on the tables and columns. – miken32 May 26 '21 at 16:08
  • 1
    Solved it. Thank you @miken32 for pointing me in the right direction. – Painguin May 26 '21 at 16:30

1 Answers1

1

It turns out I was missing the CharacterSet parameter in my connection info to the database; adding that solved my issue. I incorrectly assumed that this was being defined by the meta tag of the HTML head: <meta charset="utf-8">

$connectionInfo = array(
  "Database"=>"dbName", 
  "UID"=>"username", 
  "PWD"=>"password", 
  "CharacterSet" => "UTF-8"  //  <-- Added this line to existing connection info
);

A big thank you to @miken32 for pointing me in the right direction!

Painguin
  • 1,027
  • 14
  • 26