0

I have been writing a PHP script for a "Contact Us" form that I am using on a website to allow users to comment about the site. It has several fields, including a name field and a textarea for comments. After the user submits the form, the comment is entered into database, which is then queried for the five most recent comments and displays them on the site. However, users cannot currently enter apostrophes (') in either the name field or the comments area and have them display properly.

This is the sequence of events:

"User Submit -> Prepare SQL -> Insert Record -> Query Database -> Return recent Comments"

Currently, this is what I have for PHP:

//Function that will trim the input, and run it through a small security check
function test_input($input)
{
    $input = trim($input);
    $input = stripslashes($input);
    $input = htmlspecialchars($input);
    return $input;
}

$name = test_input($_POST["txtName"]);
$comments = test_input($_POST["txtComments"]);

//Prepared statement to insert a new Contact record
$stmt = $conn -> prepare("INSERT INTO tableName (name, comments) VALUES (?, ?)");

//Create the parameters of the prepared statement
$stmt -> bind_param("ss", $param_name, $param_comments);

//Set the parameters of the statement, running them through a security check
$param_name = mysqli_real_escape_string($conn, $name);
$param_comments = mysqli_real_escape_string($conn, $comments);

//Execute the prepared statement
$stmt -> execute()

As you can see, I am running the variables through both the htmlspecialchars() function and the mysqli_real_escape_string() function. However, I still get a strange output when I run the function.

Input

Name with Apostro'phe
I'm happy with the "/" and "\" of the present.

How It Looks In...

...database

Name with Apostro\'phe
I\'m happy with the "/" and the "" of the present.
  • I understand that the " is from the htmlspecialchars function, and think that the \' is from the mysqli_real_escape_string() function.

..website comment section (populated from database)

Name with Apostro\'phe
I\'m happy with the "/" and the "" of the present.
  • Same here with the \'. How do I get it so that when it redisplays in the HTML after being queried from the database that the apostrophes display normally?

However, this is not what I want. When it displays in the comments section (after being queried from the database), I want it to display as it was input by the user. I don't really care how it looks in the database, as long as it displays properly when it is returned to HTML by the query to populate the list.

What should I do so that users can input apostrophes in their name/comments, and have it display as such after being returned from the database; but at the same time, not run the risk of SQL injection? I have looked at several related questions, but have not found anything that answers my question.

MySQL/PHP: Allow special characters and avoid SQL injections

  • I have already tried the methods in this post without success

php - Allowing special characters in form submit

  • I don't need anything like this, only apostrophes

Let me know if I need to clarify anything!

Community
  • 1
  • 1
Kendall
  • 1,992
  • 7
  • 28
  • 46
  • User `html_entity_decode` for displaying the information maybe? http://php.net/manual/en/function.html-entity-decode.php – Maximus2012 Jul 14 '15 at 19:23
  • You can also use `htmlentities()` before writing the information to the database. Not sure if that is needed though since you are already using `htmlspecialchars()` – Maximus2012 Jul 14 '15 at 19:24
  • @Maximus2012 I don't think that this will be necessary, as it is already displaying the quotations correctly. However, it is the apostrophe that it incorrect. The php manual doesn't seem to indicate that this function covers apostrophes anyway. – Kendall Jul 14 '15 at 19:27
  • Can you try making use of these two functions and see if it works ? – Maximus2012 Jul 14 '15 at 19:29

1 Answers1

2

You're using placeholders with prepared statements. Escaping the text manually is utterly unnecessary. The DB is already doing its own internal escaping as part of the placeholder system, so your escaping just adds an extra layer of escapes that will get entered in the table.

e.g.

name                   = "Miles O'Brien";
manual escape         -> "Miles O\'Brien";
placeholder escape    -> "Miles O\\'Brien";
stored in database    -> "Miles O\'Brien";
retrieved from db     -> "Miles O\'Brien";
displayed to user     -> "Miles O\'Brien";

If you remove your manual escapes, then suddenly everything works as expected:

name                   = "Miles O'Brien";
placeholder escape    -> "Miles O\'Brien";
stored in database    -> "Miles O'Brien";
retrieved from db     -> "Miles O'Brien";
displayed to user     -> "Miles O'Brien";
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • I have removed the `mysqli_real_escape_string()` function, and it appears to be working. However, is this not still vulnerable to attack? I just got slightly confused when reading that question before, as I have been told to ALWAYS use the real escape string function. – Kendall Jul 14 '15 at 19:35
  • 1
    cargo cult programming... read http://bobby-tables.com and http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php to learn what the problem really is, instead of just blindly applying "solutions" that are anything but. – Marc B Jul 14 '15 at 19:41