SpaceDog SpaceDog - 2 months ago 14
MySQL Question

PHP/mySQL - how to encode/decode/escape/whatever needed strings safely before inserting on database

My problem is this: I have a database where all fields are VARCHAR. Some represent html code. This code contains chars like ◉, ⬛︎, ◔, ➜, ★, ✦, etc. These characters are not encoded on the HTML. They are like you see them here.

Other fields on that database are URLs.

I know I have to escape these chars to prevent sql-injection and also have to encode them to allow insertion on the database.

htmlspecialchars
will not handle these symbols because they are not encoded on the source.
mysql_escape_string
will only escape bars and double quotes, etc.

How do I really encode this and escape in
PHP
so I can insert this data on the database and how do I read them back?

thanks.

Answer

Use parameterized queries to prevent SQL injection. And use htmlspecialchars() function when you display data in html. With PDO that I love:

$db = new PDO($dsn, $db_user, $db_password);
$query = 'INSERT INTO table (data1, data2) VALUES (:data1, :data2)';
$statement = $db->prepare($query);                          
$statement->bindValue(':data1',$data1);
$statement->bindValue(':data2',$data2)
$statement->execute();