• From: USA
  • Registered: 03/05/2018
  • Posts: 123

Topic: Why are database values converted to HTML?

Using version 2.4.6
I am seeing things like "&" in my test database while working on this extension and I was wondering if I accidentally pasted that into a name field, but when I create a new customer by entering "Testing Adam & Willy", this is what I get in the database:

[7] => Array
        (
            [branch_ref] => testaw
            [debtor_ref] => testaw
            [name] => Testing Adam &amp; Willy     <-- not the "&amp;" in place of an ampersand.
            [br_name] => Testing Adam &amp; Willy
            [group_no] => 0
        )

Maybe this is just an opinion I've formed after decades of writing business applications, but in my experience, you don't alter the values that are stored in the database. You alter them to suit your display method when they are displayed.

Should I write all of my code to convert HTML entities into their actual values for display in things that don't use HTML, or is this a bug that may someday be fixed?

Forgive me for having a strong opinion on this subject, but I really hope this was not intentional.

oakstreet1's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Why are database values converted to HTML?

We do not want code to be executable like "Bennet Cole &$var Co" where $var can be a malicious bit of code change.

apmuthu's Website

  • From: USA
  • Registered: 03/05/2018
  • Posts: 123

Re: Why are database values converted to HTML?

What's done is done, I guess, but this is a bad design choice, in my experience.

1) using a normal query tool, a user query may fail because search strings will not match the HTML entities
2) A string that should fit in a field will fail to fit because HTML entities expand the string by many chars (e.g. &quote;)
3) There are other ways to deal with escaping strings in the database. Using parameters in queries is the current best practice.
4) Query results look wrong, unless the query tool converts the HTML entities for display.
5) Joining with keys from other databases will fail because FA uses a unique encoding for strings

If your concern was SQL injection, using SQL escaping would not corrupt the stored data.
If your concern was PHP execution, you would escape/convert values *after* selecting them from the database.
There should never be a case where PHP code executes a string from the database. That would just be bad coding. Corrupting the data and interjecting strange, random problems like "Input value is too long", when the input value is no longer than other values that were saved in the same field, is not the answer to bad coding practices.

Even worse, in FA, if you overflow the branch_ref field by adding some special chars, the update silently fails. How do I explain to a client that they can use special chars, as long as their expanded versions don't push the length of the string past the character limit imposed by the database, and, if they do, their update will fail without any indication that it did fail, or why?

I'm too far into my project to give up on FA, but this is a serious disappointment. I started my career in database applications in 1987. I've worked with almost every major DBMS and I've written countless database applications.

oakstreet1's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Why are database values converted to HTML?

Please note that this was done when escaping was really long winded in the old days 10 years or more ago with having to carryover large number of users from PHP 4 and MySQL 3.x.

Also a multitude of languages cause quite some heartache across linux/windows platforms that all this was cobbled together with least pain. The forum is replete with such legacy issues.

Possibly in FA v2.5, better HTML escaping and accommodation of HTML encoding / decoding will be implemented.

Nice to have such experienced users like yourself pitch in. Please study the code as it evolves and make suggestions that hopefully will not break existing users installs.

apmuthu's Website

  • From: USA
  • Registered: 03/05/2018
  • Posts: 123

Re: Why are database values converted to HTML?

I understand why we can't just change it, now. smile

I guess I'm just ranting, because I have to join keys from another database and I just realized that this is going to be a real problem. It's not a marginal case for this client, because they manage an association of emergency medical services, paramedics, and rescue workers. Almost all of the department names have an ampersand in the name. e.g. "Fire & Rescue" or "Rescue & EMS".  Matching records from one database to the other cannot be done in an SQL query in a JOIN.

When I finish this project and get paid, I will be making another cash donation to FA development, and when I get caught up on my deadlines, I will happily donate my time to help out. I have been using FA in my business for almost 2 years, now, and I'm pretty happy with it. From a developer perspective, it has presented some challenges. smile

oakstreet1's Website