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.

Answer 1

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.