Best practices for saving data to MySQL and form handling
Hi everyone, my first post here. I've searched all over the web and found various answers to my questions, all different, but I guess this is the best place to ask this.
I'm not that good with all this, so I don't know if it's important or not, so I prefer giving more details than not enough. Thanks for your understanding.
Let's start with my environment;
- PHP version is 5.3.29
- Files are UTF-8 encoded
- MySQL version is 5.5.40
- Server charset : UTF-8 Unicode (utf8)
- phpMyAdmin version is 4.2.12
Ok, so I use PHP to make forms and handle them. What I need to do usually is to have a form filled by users and when they submit it, it saves/update the field content to a MySQL database but it also print the submitted field on the same page, sometimes directly on the page and at other times on a form field again.
My main issue is to know the best practices for value handling, specifically regarding single and double quotes. I always have problems saving to MySQL (mysql errors) and/or printing out results back onto form fields.
Let's say I have a simple form that has only one field called 'Location', and that a user insert (Bob's so-called "restaurant"), this becomes a nightmare since I had to use str_replace (ex.: $string = str_replace("'","\'",$string) then $string =str_replace('"','\"',$string), not pretty...) before saving to MySQL and str_replace back again to print that back onto the form field.
With this method, I've always saved single and double quotes directly into MySQL, but when I make resquests and print those results that contains single and double quotes into a form to update something, I have to do that str_replace again.
So, just to make things done 'right', my first question is : Is it ok to save single and double quotes directly into MySQL or I should remove them with something like 'htmlspecialchars' prior saving to MySQL and save them 'encoded'? (ex.: ' becomes ' and " becomes ").
As a subquestion to the first one : if saving single and double quotes directly to MySQL is a bad idea AND 'htmlspecialchars' is not the best solution, which one is the best one, in short, how submitted data from form fields should be saved into MySQL DB?
My third question is : If encoding fields prior to save in MySQL is a good idea, should I only 'encode' single and double quotes, all special chars (<, %, &, etc.) or french accents too (é, à, ç, etc.)?
Is there a caveat of doing so? I mean, if I save everything into the database with encoded strings, would I encounter an issue somewhere like search from the DB or sending emails with HTML code for example?
So, to resume, what would be the best practice to form handling (values) when saving data sent by users prior to save into the MySQL database