Saving text with apostrophe converts to 's

Which table, which field? Is your table and field utf-8? When you read back from the field in FA do you see the normal apostrophe?

Ref: http://stackoverflow.com/questions/3574609/php-html-decoding-help-converting-a-039quote039-is-bbold-b

html_entity_decode('A &#039;quote&#039; is <b>bold</b>', ENT_QUOTES);

should work.

mb_convert_encoding($string, "UTF-8", "HTML-ENTITIES");

replacing UTF-8 with your charset should work.

Where and in which report file you used the mb_convert_encoding() function that solved the issue? We need to see if there is a really generic solution.

The browser automagically chooses the encoding and hence screen display on html will work normally.

What you have done is after the fact.

Manually correct the entry in the table field to be an apostrophe instead of it's encoded version and then see if it displays correctly in the reports without using the mb_convert_encoding() function.

If all is well, then we need to correct it where the field is INSERTed / REPLACEd / UPDATEd. This needs to be done in a generic manner where all string POST variables that need to populate VARCHAR / TEXT fields get correctly stored. Such a solution will also need to take care of special characters specific to certain languages in the text as well.

Thanks. Here is the solution:

All data stored in the tables pass through function db_escape(). The Setup -> Company Setup form acquires the data and stores it using function update_company_prefs() in admin/db/company_db.inc file.

It is the "ENT_QUOTES" parameter that causes the apostrophe to get encoded in the function htmlspecialchars().

The real solution will be to use the htmlspecialchars() function when the mysql_real_escape_string() is not available by altering the function db_excape() in includes/db/connect_db.inc :

function db_escape($value = "", $nullify = false)
{
    $value = @html_entity_decode($value, ENT_QUOTES, $_SESSION['language']->encoding);
    $value = @htmlspecialchars($value, ENT_QUOTES, $_SESSION['language']->encoding=='iso-8859-2' ? 'ISO-8859-1' : $_SESSION['language']->encoding);

      //reset default if second parameter is skipped
    $nullify = ($nullify === null) ? (false) : ($nullify);

      //check for null/unset/empty strings
    if ((!isset($value)) || (is_null($value)) || ($value === "")) {
        $value = ($nullify) ? ("NULL") : ("''");
    } else {
        if (is_string($value)) {
              //value is a string and should be quoted; determine best method based on available extensions
            if (function_exists('mysql_real_escape_string')) {
                  $value = "'" . mysql_real_escape_string($value) . "'";
            } else {
              $value = "'" . mysql_escape_string($value) . "'";
            }
        } else if (!is_numeric($value)) {
            //value is not a string nor numeric
            display_error("ERROR: incorrect data type send to sql query");
            echo '<br><br>';
            exit();
        }
    }
    return $value;
}

to be

function db_escape($value = "", $nullify = false)
{
    $value = @html_entity_decode($value, ENT_QUOTES, $_SESSION['language']->encoding);
    if ($_SESSION['language']->encoding=='iso-8859-2') $value = @htmlspecialchars($value, ENT_QUOTES, 'ISO-8859-1');

      //reset default if second parameter is skipped
    $nullify = ($nullify === null) ? (false) : ($nullify);

      //check for null/unset/empty strings
    if ((!isset($value)) || (is_null($value)) || ($value === "")) {
        $value = ($nullify) ? ("NULL") : ("''");
    } else {
        if (is_string($value)) {
              //value is a string and should be quoted; determine best method based on available extensions
            if (function_exists('mysql_real_escape_string')) {
                  $value = "'" . mysql_real_escape_string($value) . "'";
            } else {
                $value = "'" . mysql_escape_string($value) . "'";
            }
        } else if (!is_numeric($value)) {
            //value is not a string nor numeric
            display_error("ERROR: incorrect data type send to sql query");
            echo '<br><br>';
            exit();
        }
    }
    return $value;
}

We are only conditionally using the line $value = @htmlspecialchars...... for Polish like languages and can later remove it altogether. The old mysql_escape_string() did not use the link identifier and the encoding charset and may have needed @htmlspecialchars in which case it can be moved to just above that function.

The htmlspecialchars() encodes &,',",<,> only.

@joe: want to commit it?

These functions are part of the db_escape() function.

@joe: looks like you can commit it now.

Yes, wonder what characters other than the 5 listed are handled by htmlspecialchars() in iso-8859-1 encoding languages like Polish. Also how would the boolean variables be escaped as they would fit the non string / non numeric conditions and return an error - hence the usage of the db_escape function would have to be prudent.

These filters may be useful.

Example filter_input() from the php manual (List of available Filters):

<?php
$search_html = filter_input(INPUT_GET, 'search', FILTER_SANITIZE_SPECIAL_CHARS);
$search_url = filter_input(INPUT_GET, 'search', FILTER_SANITIZE_ENCODED);
echo "You have searched for $search_html.\n";
echo "<a href='?search=$search_url'>Search again.</a>";
?>

The above example will output something similar to:

You have searched for Me & son.
<a href='?search=Me%20%26%20son'>Search again.</a>

Yes, the db_escape() role is crucial. But actually saving the decoded apostrophe is the problem. When I look the sql_trail table to see how the query looks like. It showed like this

"UPDATE sys_prefs SET value = &#039;Carmen&#039;s &#039; WHERE name=&#039;coy_name&#039;"

-- which is I think the proper: before the db_escape()

UPDATE sys_prefs SET value = 'Carmen's' WHERE name='coy_name';

-- after the db_escape() it should like this

UPDATE sys_prefs SET value = &#039;Carmen's &#039; WHERE name=&#039;coy_name&#039;

Sorry for my bad english.

In some installs, the

'

gets displayed as is without being decoded on screen / in screen field.

It will be better if it is stored as:

UPDATE sys_prefs SET value = 'Carmen\'s' WHERE name='coy_name';

1. Store 3/4" nuts and 4' 2" bolt as a new item in inventory.
2. Edit it and see what you get in the Name field.
3. Make some change say 3/4" nuts with 4' 2" bolt and save.
4. View the item on screen and in the report and check if it gets mangled with double encoding....

What is the problem with using backslashes for single quotes as it is more readable in the sql backup?

What benefit do we get by using htmlspecialchars() when mysql_real_escape_string() is used alone?

What other characters are usefully modified when iso-8859-2 encoding is switched to iso-8859-1 in htmlspecialchars()?

Backport of @itronix fix above is in my FA 2.3 repo.

The fix in my post above will not need any rewriting of the code for displayed back to browser as simple sql escaping will not be touched by the db_escape's decoding code.

You're right about the XSS vulnerability and hence my earlier post's fix should not normally be used. Thanks @itronics for the explanation.

@jnunex: please test with my backport in this post without the earlier post's fix and see if all is well.

@jnunez: Hope the reports display correctly, in which case make sure that double-encoding does not take place when it is saved again.

BTW, for me, it prints and displays on screen correctly though it is stored encoded in the table field for both the sys_prefs table records and the item_codes table as well.

Tested on XAMPP v1.7.3 on WinXP SP3 (and Debian 6) and FF 37 browser.