2

I am using HTMLBOX editor for getting Notes/RichText Data input from User. I found there are lot of garbage characters in my database and they keep on doubling on each time user save/update notes.

Below is code sample for my PHP to SQL server connection.

<?php
class Model {
    public static $db;

    public static function initialize() {
        self::$db = new PDO('odbc:Driver={SQL Server};Server='.DBCONNECTION_SERVER.';Database='.DBCONNECTION_DATABASE.'; Uid='.DBCONNECTION_USER.';Pwd='.DBCONNECTION_PASS.';', null, null,array(PDO::ATTR_PERSISTENT => true));
        self::$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
        self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES , false);
    }
}
?>

I search for same problem and most of solution suggest to use UTF8 encoding. I set below attribute but no success. :(

self::$db->setAttribute(PDO::SQLSRV_ATTR_ENCODING , PDO::SQLSRV_ENCODING_UTF8);

Please see below screenshot. enter image description here

Update:

Column Type : NVARCHAR(MAX)

User Input : User can enter or Copy/paste anything. Those garbage character appears when user copy paste from Outlook or other Rich Text Source. The single quote(') from outlook get converted to (âÂÂ) garbage character.

closed as unclear what you're asking by Philᵀᴹ, RLF, RolandoMySQLDBA, Andriy M, Marian Jun 14 '16 at 10:18

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Is the target column nchar/nvarchar? – Dan Guzman Jun 4 '16 at 14:14
  • 1
    Where's the code you're using to actually pass the content to the database? (And no, you don't want UTF8 here, AFAIK.) – Aaron Bertrand Jun 4 '16 at 14:24
  • 2
    That looks like a Windows 1252 to UTF8 encoding issue i18nqa.com/debug/utf8-debug.html – Martin Smith Jun 4 '16 at 15:37
1

According to the MSDN page for PDO::setAttribute, PDO::SQLSRV_ENCODING_UTF8 is the default value, so setting it explicitly probably wouldn't change the behavior.

Try the other option, PDO::SQLSRV_ENCODING_SYSTEM, which seems to have worked in this case from the MSDN forums: Some characters improperly retrieved through PDO driver.

  • Thanks for help.. But it doesn't work.. I just uploaded screenshot for that.. I added the PDO::SQLSRV_ENCODING_SYSTEM attribute.. Try to read and updated entire notes.. but doesn't help much.. – Ritesh Chandora Jun 7 '16 at 10:14
  • @RiteshChandora What is the input data that gets that result? What is the exact data in the table? Use SELECT CONVERT(VARBINARY(MAX), field) FROM table WHERE ID = ?? where ?? is the ID for the row shown in the screenshot. What is the encoding / charset of your PHP page (based on HTML tag and/or headers)? What is the datatype of the column? – Solomon Rutzky Jun 7 '16 at 13:58
  • Updated the question. PHP default encoding is utf-8 – Ritesh Chandora Jun 7 '16 at 16:29
  • @RiteshChandora Ok, and how are you inserting this data into SQL Server? A Dynamic SQL statement executed in the PHP code, or a Stored Procedure called by the PHP code? Please update the question to show exactly how the data is getting into SQL Server. – Solomon Rutzky Jun 7 '16 at 17:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.