Fix PHP method for storing special characters in MySQL DB

Question

Using PHP, what is the best way to store special characters (like the following) in the MSQUL database to avoid injections.

« " ' é à ù 

Here's how I do it now:

 $book_text=$_POST['book_text']; $book_text=htmlentities($book_text, "ENT_QUOTES"); $query=//DB query to insert the text 

Then:

 $query=//DB query to select the text $fetch=//The fetch of $book_text $book_text=html_entity_decode($book_text); 

Thus, all text is formatted in HTML objects. But I think it takes up a lot of space in the database. So is there a better way?

Answer 1

Use utf8 to store these values.

To avoid injections , use mysql_real_escape_string() (or prepared statements).

To protect against XSS, use htmlspecialchars .

Answer 2

It looks like your question can be generalized to handle and store UTF8 with PHP and MySQL.

To be safe from SQL injection, you must use prepared statements. mysqli and PDO drivers support them.

Prepared instructions are automatically quoted by the driver, so you don’t need to worry about that.

Your database tables must be created with the utf8 and utf8_general_ci character set. These my.cnf options ensure your MySQL server runs UTF8:

 [mysqld] default-character-set=utf8 default-collation=utf8_general_ci character-set-server=utf8 collation-server=utf8_general_ci init-connect='SET NAMES utf8' [client] default-character-set=utf8 

Remember that PHP, as a rule, does not know UTF-8, so you should take care to use iconv or mbstring libraries to process strings. See PHPWACT for a good overview.

Make sure the PHP internal character set is set to unicode

 iconv_set_encoding('internal_encoding', 'UTF-8'); mb_internal_encoding('UTF-8'); 

You must also ensure that the browser knows the encoding by either sending the correct header or by adding the <meta> for the encoding.

That should do it.

Answer 3

yes utf8 encoding ...

you can also use Prepared Statements if you are very worried about injections ...

http://www.linearchat.co.uk/2011/08/why-prepared-statements-in-mysql-are-a-good-thing/

and

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

Answer 4

Your question is a wonderful collection of confusion. You are all confused. Let's try to figure it out.

Using PHP, what is the best way to store special characters (like the following) in the MSQUL database to avoid injections.

These are incomparable matters. Keeping special characters is one thing, and avoiding injections is another. completely different.

 $book_text=htmlentities($book_text, "ENT_QUOTES"); 

This is the funniest part. although it is designed to protect your requests, it actually does nothing. Since instead of the constant ENT_QUOTES, whose value is 3, you use the string ENT_QUOTES, whose numeric value is 0, so you do not set the flag.

But even if you set this flag correctly, it will not automatically protect you. Since the injection code may not contain special characters.

To avoid injections, you should follow entire sets of rules , not just one simple function, make_my_data_safe (). There is no magic wand.
See this my answer for more details.

Regarding special characters, it's simple. The only problem is that there are no special special characters. There are different special characters for different environments.

• 'matter for database and HTML
• <> are relevant only for HTML
• é à ù make sense only for HTML, it depends on the encoding.

You have different formatting rules for each case. Different, not one for everyone.

To use à à ars characters with HTML, you must set the correct HTTP header. To use é à ù with a database, you must set the table encoding to utf8 and the connection encoding to utf 8.