/ appear in text when ' is used in mysql?

Question

I have a html form that users of my site can type in, click submit and then mysql inserts this into the database, and for some reason when this gets submitted to my 'bio' column which is longtext and utf8_unicode_ci format, if a user types jame's its showing as jame\'s and i dont want these slashes.

Does anyone know why this is happening and how i can get rid of slashes? thanks

html:

<form action="includes/changebio.php" method="post" id="form1">         
 <textarea id="bio" style="width: 448px; 
    margin-top:3px;
    text-align:left;
    margin-left:-2px;
    height: 120px;
    resize: none; 
    border: hidden;" 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>

php:

<?php ob_start(); ?>
<?php
require_once("session.php"); 
require_once("functions.php");
require('_config/connection.php');
?>
<?php 
session_start();
include '_config/connection.php'; 
$bio = htmlspecialchars($_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 ='".mysql_real_escape_string($bio)."' WHERE id=".$_SESSION['user_id'].""); 
}
header("Location: {$_SERVER['HTTP_REFERER']}");
?>
<?php ob_end_flush() ?>

What version of PHP are you using? – SDC Mar 05 '13 at 12:19 — SDC, Mar 05 '13 at 12:19

score 1 · Answer 1 · answered Mar 05 '13 at 12:14

1

Your server is probably quite old and it has enabled a good old PHP "feature" called Magic Quotes. That feature should have never existed in the first place and the best think you can do is trying to disable it.

answered Mar 05 '13 at 12:14

Álvaro González

142,137
41
261
360

1

It's worth clarifying that Magic Quotes has been deprecated in recent versions of PHP. Switching it off is a good thing; upgrading your PHP to a recent version is even better. – SDC Mar 05 '13 at 12:19

score 0 · Answer 2 · answered Mar 05 '13 at 12:08

0

use strip_slashes

echo strip_slashes($string);

Use this :

<?php echo htmlspecialchars(strip_slashes($profile['bio'])); ?>

answered Mar 05 '13 at 12:08

Prasanth Bendra

31,145
9
53
73

1

This is a possible workaround but I recommend fixing the root problem: magic quotes. – Álvaro González Mar 05 '13 at 12:17
1

if you are going to use a work around like this, at least check the magic quotes setting first so that the fix doesn't cause things to break when they upgrade PHP. – SDC Mar 05 '13 at 12:18

score -1 · Answer 3 · answered Mar 05 '13 at 12:12

-1

you have in your sql query mysql_real_escape_string() this escapes any ' marks by putting in \ before it to become \'

So \' is stored in your database you then need to remove the slashes when you display the output no the screen so you'd do <?php echo stripslashes($profile['bio']); ?> in your text area

Please see stripslashes() and mysql_real_escape_string()

You may also want to look at converting to using PDO or mysqli instead of the old mysql_ functions as they are now deprecated

answered Mar 05 '13 at 12:12

Dave

3,280
2
22
40

You're actually wrong. This is a direct quote from the mysql_real_escape_string() docs. "mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. " This means that if you use it and you're trying to insert a ' into the database it PREPENDS a \ before the ' so that stored in the database is \' it does NOT create valid sql syntax it simply escapes special characters to not break sql inserts – Dave Mar 05 '13 at 12:20
Put stripslashes() around your echo out statement and it should work fine. If its not please repost your new attempt code so we can review it for further faults. – Dave Mar 05 '13 at 12:22
1

@Dave: No! Wrong, wrong, wrong. The slash is **not** stored in the DB when you use `mysql_real_escape_string`. Yes, the slash is added to the string that is sent to the DB, but the DB does not save the slash. Escaping means that the DB treats the slash and the following character as a special coded sequence. If he's seeing slashes in the DB, it means that the string is being double escaped. There's nothing obvious in the code to do that, so this is likely to be because of the magic quotes setting. – SDC Mar 05 '13 at 12:27
No where in his post though is he stating that its storing the \ in the database though he's stating its displaying the \ on output which according to his code posted is because he's not post stripping the slashes on display. It could well be magic quotes but in all reality the number of hosts/installs of php with magic quotes still enabled after all this time is going to be tiny. purely on odds alone its more likely to be a programatic error rather than a configuration error. – Dave Mar 05 '13 at 12:50

/ appear in text when ' is used in mysql?

3 Answers3