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.

@apmuthu
I tried without mb_convert_encoding() and without your earlier post's fix, It worked!.

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.

Small explanation to the db_escape function. We are not aware about backup file readability (they are created just for database restoring), our main concern is application security, and this is why db_escape works as is. When you will change the escaping code removing html entities encoding, you will make an application vulnerable to XSS attacks.

To prevent permanent XSS vunerability you have to prevent special html characters to be stored in database, or have to be encoded whenever it is displayed back to browser. We have chosen the former approach  and implemented it in db_escape() function code (beside simple sql escaping).
The latter approach would require rewriting all the data displaying code in FA, which is just time wasting.

Regarding the lacking support for ISO-8859-2 in htmletities, this is old, never solved php problem, so we have to ommit is somehow. As all dangerous chars are included and have the same placement in both ISO-8859-1 and ISO-8859-2 sets, we can just fake encoding declaration here.

Janusz

'

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()?

If you have apostrophe in company name, it should be stored encoded. So exact sql is:

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

So, when you look into your database with e.g. phpmyadmin you will see in one sys_prefs record Carmen's, and this is perfectly OK.
Where you encounter problem with this?
Janusz

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

-- 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 = 'Carmen's ' WHERE name='coy_name'

Sorry for my bad english.

Janusz

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>

Joe

@joe: looks like you can commit it now.

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?