How to pass apostrophies from text areas to MySQL using PHP

Question

I have a text area that users add notes too. On the next page I use the $_POST[Comments] to show what was typed. I have an edit button to go back and see what was typed and edit the notes but when I show the $_POST[Comments] it shows everything up to an apostrophe.

Example:

Originally typed: Let's try this.

When Editing: Let

Now when I pass it to the server to do an SQL add I use the following function to protect against SQL injection

function keepSafe($value) {
        if (get_magic_quotes_gpc()) {
            $value = stripslashes($value);
        }
        if (!is_numeric($value)) {
            $value = "'" . mysqli_real_escape_string($value) . "'";
        }
        return $value;
    }

The following is what I use to format the input for SQL insertion.

$Comments = str_replace("\n","<br />",$_POST['CustComments']);
    $Comments = keepSafe($_POST['Comments']);

I need to be able to see all of the apostrophes in the notes section when editing before submission. And I want to make sure that when I do submit it is a SQL injection prevented safe code.

Solution

The problem with the apostrophe's:

You probably use an input like this:

<input type='text' value='<?php echo $value;?>'/>

The problem is that if the value has an apostrophe this happens:

<input type='text' value='Let's play'/>

So the value tag is ended because of the apostrophe in your variable.

To fix it simply use htmlspecialchars with ENT_QUOTES:

<?php 
 $value = htmlspecialchars("Let's play", ENT_QUOTES);
?>
<input type='text' value='<?php echo $value; ?>'/>

That way the apostrophe's get encoded and will be editable in your form

About the SQL injection:

Simply use mysqli's prepared statements and you will be fine. To also keep you safe from XSS, always htmlspecialchars user input in HTML output. Even better is to filter the input to only what you need, and save only the filtered input to your database.

Comments

[Green Black]

Apostrophe's do not matter in textarea fields. And only htmlspecialchars does not encode the apostrophe. You need ENT_QUOTES. By the way: <?= is bad practice as it will only work with shorttags enabled <?php echo is better.

[JukEboX]

Let me add this. How do I store the apostrophe in the text area into the db using myqsql insert

[Green Black]

You need to use prepared inserts. Or you can use mysqli_real_escape_string( "let's play" ); (I recommend a prepared statement. You can find everything about that in the link I provided in my awnser)

[Geo]

John, good points. Just my habits, I guess. Never had a problem.

[JukEboX]

If you can give me a little more of a hint on it as I am not completely familiar with the new mysqli yet.