inserting special characters in mysql?

Question

can someone please show me what im doing wrong. i have a form and im inserting the data into mysql as text but when u type apostrophes or something like that it puts a \ in.

can someone recomend how to get my mysql to allow special characters?

here's my html form:

<form action="includes/changebio.php" method="post" id="form1">         
 <textarea id="bio" textarea name="bio" data-id="bio" maxlength="710"><?php echo htmlspecialchars($profile['bio']); ?></textarea>
<input type="image" src="assets/img/icons/save-edit.png"class="bio-submit" name="submit" value="submit" id="submit"/>
</form>

my mysql statement:

<?php
require('_config/connection.php');
?>
<?php 
session_start();
include '_config/connection.php'; 
$bio = $_POST['bio'];
$result = mysql_query("SELECT bio FROM ptb_profiles WHERE id=".$_SESSION['user_id']."");
if(!$result) 
{ 
echo "The username you entered does not exist"; 
} 
else 
if($bio!= mysql_result($result, 0)) 
{ 
echo ""; 
    $sql=mysql_query("UPDATE ptb_profiles SET bio ='".addslashes($bio)."' WHERE id=".$_SESSION['user_id'].""); 
}
    if($sql) 
    { 
header("Location: {$_SERVER['HTTP_REFERER']}");
}
?>

Is it your [collation](http://dev.mysql.com/doc/refman/5.5/en/charset-applications.html)? — hd1, Feb 15 '13 at 08:50

score 1 · Answer 1 · answered Feb 15 '13 at 08:52

The apostrophes are normal in order for your string to be saved. and avoid sql injuections imagine your query having a ' at the middle. it will be semething like where name = 'your's' which will cause an error.

what you can do is when you fetch your data. you can remove the \ by adding stripslashes($mystring); in your code

score 0 · Answer 2 · answered Feb 15 '13 at 08:51

2 things.

1) someone is going to tell you to stop using mysql_* functions and use PDO instead. This may infact help you considerably. 2) The addslashes is there to assist with the insertion of special chars and the prevention of SQL injection. I'm sure to achieve this fully there's a whole battery of precautions you should use.

In all honesty, I'd switch over to the PDO way of things using prepared statements etc. This will reduce the possibility of failure and increase reliability. You may find you don't need addslashes to fix these kind of problems.

score 0 · Answer 3 · answered Feb 15 '13 at 08:58

Why do you use adslashes() function ? is it to prevent SQL injection for example ?
use standard function mysql_real_escape_string() instead. it's safe and would solve the problem.
and keep in mind , it's not related to the extension you are using , as some people said to use PDO extension for example

score 0 · Answer 4 · answered Feb 15 '13 at 09:03

if you want to insert some special characters into you Mysql Database then use Unicode formate .set the Collation of table to utf8_unicode_ci.

add <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> into your html file head.

use following code for Database connection.

define('HOSTNAME', 'localhost');
define('USERNAME', 'user_name');
define('PASSWORD', 'password');
define('DATABASE', 'database_name');

$dbcon= mysql_connect(HOSTNAME, USERNAME, PASSWORD); 
mysql_query("SET character_set_results=utf8", $dbcon);
mb_language('uni');
mb_internal_encoding('UTF-8');
mysql_select_db(DATABASE, $dbcon);
mysql_query("set names 'utf8'",$dbcon); 
mysql_query("SET character_set_client=utf8", $dbcon);
mysql_query("SET character_set_connection=utf8", $dbcon); 
mysql_query("SET character_set_results=utf8", $dbcon);

for more info check bellow link.it may be usefull for you..

http://www.oyecoder.com/2013/02/store-any-language-data-in-your-mysql.html

inserting special characters in mysql?

4 Answers4