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.

You first use `$_POST['CustComments']` and then `$_POST['Comments']` in your last example, if that matters (it should, to you). Also (not answer-specific), you should be replacing `\r\n` instead of just `\n` (from a Windows-standpoint). Also also (not answer specific), you should use prepared statements instead of direct-variable insertion (since you're using mysqli). — newfurniturey, Dec 04 '12 at 20:55
[The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) — deceze, Dec 04 '12 at 21:17
It looks like you're getting example code from a very old source. Magic quotes has been removed from PHP a long time ago. As for how to handle this properly, that's what prepared statements are for. — GordonM, Apr 06 '16 at 15:48

score 7 · Accepted Answer · edited Dec 04 '12 at 22:24

7

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.

edited Dec 04 '12 at 22:24

Geo

12,666
4
40
55

answered Dec 04 '12 at 21:13

Green Black

5,037
1
17
29

Let me add this. How do I store the apostrophe in the text area into the db using myqsql insert – JukEboX Dec 04 '12 at 22:32
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) – Green Black Dec 04 '12 at 22:35
If you can give me a little more of a hint on it as I am not completely familiar with the new mysqli yet. – JukEboX Dec 04 '12 at 23:36

score -1 · Answer 2 · answered Dec 04 '12 at 21:29

-1

Use htmlspecialchars() function when creating the textarea tag:

<textarea><?=htmlspecialchars($_POST['Comments'])?></textarea>

answered Dec 04 '12 at 21:29

Geo

12,666
4
40
55

1

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 ` – Green Black Dec 04 '12 at 21:59
John, good points. Just my habits, I guess. Never had a problem. – Geo Dec 04 '12 at 22:50

How to pass apostrophies from text areas to MySQL using PHP

2 Answers2

Linked