36

I use MySQL to store data and my web pages are all encoded as UTF-8. I have a lot of Portuguese characters such as ç and õ and I'm wondering if I should HTML-escape them before storage.

Should we store & as &, for example? And why (not)? What are the advantages and disadvantages / best practices?

Mohamad
  • 34,731
  • 32
  • 140
  • 219
  • 2
    ç and õ are UTF-8 chars. If DB supports them, and your pages are already encoded to UTF-8, then why convert? – bakoyaro Jan 04 '11 at 22:26
  • It's because I'm used to reading about escaping this stuff that I thought it was standard practice, apparently it's not! – Mohamad Jan 05 '11 at 01:24

6 Answers6

62

Don't HTML-encode your characters before storage. You should store as pure a form of your data as possible. HTML encoding is needed because you are going to display the data on an HTML page, so do the encoding during the processing of the data to create the page. For example, suppose you decide you're also going to send the data in plain text emails. If you've HTML-encoded the data, now the HTML encoding is a barrier that you have to undo.

Choose a canonical form for your data, and store that. UTF-8 is wonderful, and your database supports it (assuming you've created all your tables properly). Just store UTF-8.

Ned Batchelder
  • 364,293
  • 75
  • 561
  • 662
  • 17
    I agree. This is the HTML equivalent of PHP\'s \"magic quotes\" feature. It\'s not a good idea, because not all data needs escaping & it\'s annoying to see escaped data where it shouldn\'t be. – dan04 Jan 05 '11 at 06:58
  • 3
    Isn't it the same, the other way around? That unencoded HTML is a barrier when you need it encoded? I.m.o. its more likely you need to output encoded HTML. In the few cases you want it decoded, you can decode it. It's also safer when a developer forgets to decode than encode right? There can be alot of locations the data is used, so the risk for a developer to forget encoding is real. – feskr Jan 14 '16 at 10:51
  • I personally don't think there is a DO or DON'T answer to this question. Also agree with @feskr that it can be more dangerous if we forget to encode than to decode(If a developer forgets to decode, it will be caught during development). IMO just based on your own scenario and make a call whether you need to store the encoded/safer string and decode when needed or the other way around. – Kyle Huang Jan 24 '19 at 06:13
7

Going by the purpose of Database, its not advisable to HTML encode and store the data. Doing so will make the data desirable only for rendering on HTML pages(the one purpose) and for all other operations(many) you need to again decode. This degrades data consistency(since validity, accuracy, usability are hampered) property of Database.

BraveNinja
  • 203
  • 3
  • 11
2

Do you ever need to search for them? I'm not a MySQL expert but you may have to jump thru hoops to do searches.

Are you concerned about the HTML-ness of the data or the character encoding?

I would say try not to do any special encoding of characters in the DB if you can avoid it. Searching, having to remember special in-bound/out-bound processing, etc.

n8wrl
  • 19,439
  • 4
  • 63
  • 103
  • great point. I hadn't thought that far because I have not implemented search yet. My software is still early in development. But the answer is yes, I will need to search for them. Does encoding them cause problems in that case? Reading your comment, I assume I would have to encode the characters in the search string before sending out the query! – Mohamad Jan 04 '11 at 22:00
  • 2
    I would think so, and even then you'd have trouble with 'near matches.' I'm more familiar with SQL Server which has wildcard matching ('LIKE' - SQL Standard?) which might be problematic with encoding. – n8wrl Jan 04 '11 at 22:06
2

I would argue that encoding on the way into the database is actually a security risk, because it means you presumably won't be encoding between database and browser (as this would lead to double encoding). That means that if there is a route either now or in future for unencoded data to get into your database then that will be sent to the browser unencoded. Better to encode between database and browser and therefore store unencoded IMHO.

Dai
  • 21
  • 1
2

If you are doing 100's or 1000's of page presentations for each write, then encoding on the way in is going to be more efficient. But in most circumstances I guess the difference would be negligible.

But the other reasons (to not encode) are good, no doubt about it - and anyway it's pointless to encode characters which UTF-8 likes.

hawkip
  • 144
  • 1
  • 3
1

I wouldn't encode it in the database unless there's a clear and definite value to doing that. You (and anyone else who will ever work with the data) will have to remember to un-escape when using that data or escape whatever data you insert, update, or compare to that field. I'm not sure what the benefit is to escaping it, but it's probably not worth it.

Trystan Spangler
  • 1,685
  • 11
  • 20