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?
In PHP there is a pre-built function that can be used as follows: $insertData = mysql_real_escape_string($data); Assuming $data is the data which you want to insert.
Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query. Use the backslash character to escape a single character or symbol.
Use utf8
encoding to store these values.
To avoid injections use mysql_real_escape_string()
(or prepared statements).
To protect from XSS use htmlspecialchars
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With