Correct PHP method to store special chars in MySQL DB

Question

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

« " ' é à ù

This is 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);

This way, all my text is formatted in HTML entities. But I think this takes up a lot of database space. So, is there a better way?

Solution 1

Use utf8 encoding to store these values.

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

To protect from XSS use htmlspecialchars.

Solution 2

It sounds like your question can be generalised to handling and storing UTF8 with PHP and MySQL.

To be safe from SQL injections, you should use prepared statements. The mysqli and PDO drivers both support them.

Prepared statements are automatically quoted by the driver, so you don't need to worry about this.

Your database tables should be created with the character set utf8 and the utf8_general_ci collation. These my.cnf settings will ensure your MySQL server runs UTF8 all through:

[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

Be aware that PHP is generally unaware of UTF-8, so you should take care to use either iconv or mbstring libraries for string handling. See PHPWACT for a good overview.

Make sure PHP's internal character set is set to unicode

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

You should also make sure the browser knows the encoding by sending either the correct header or adding a <meta> tag for charset.

That should do it.