Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Correct PHP method to store special chars in MySQL DB

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?

like image 703
Antonio Ciccia Avatar asked Apr 11 '12 20:04

Antonio Ciccia


People also ask

How can I insert special characters in MySQL using PHP?

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.

How do you handle special characters in SQL?

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.


2 Answers

Use utf8 encoding to store these values.

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

To protect from XSS use htmlspecialchars.

like image 154
Botanick Avatar answered Oct 21 '22 03:10

Botanick


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.

like image 43
jokkedk Avatar answered Oct 21 '22 03:10

jokkedk