Allowing punctuation to be submitted to MySQL db

Question

I have everything set up, and this works, but I can't add ' or any other punctuation without getting a MySQL syntax error when I hit submit. I want to know how I can add punctuation to the text row of my form. I have it set up to utf8_unicode_ci under Collation, but I still get a syntax error.

The textarea is where I want to be able to submit with any punctuation or character. Here's what I have.

This is the form I'm using

print "<form method=post action=reply.php>";
print "<h3>Reply:</h3>";

print "<br><textarea name=reply_content textarea rows=4 cols=50></textarea>";
print "<input type=hidden name=reply_user value={$_SESSION['user_name']}>";
print "<input type=hidden name=topic_id value=$id>";
print "<br><input type=submit></form>";

This is the SQL for reply.php

$reply_content=$_POST['reply_content'];
$reply_time=date('Y-m-d H:i:s');
$reply_user=$_POST['reply_user'];
$topic_id=$_POST['topic_id'];

mysql_query("INSERT INTO reply VALUES ('reply_id', '$reply_user', '$reply_content', '$reply_time', '$topic_id')") OR die(mysql_error());

print "<center><h1>Reply Posted Successfully</h1>";

I know there's a way to do this, I just don't know how. If anyone could help me out, I'd really appreciate it!

This is a common and well understood problem. SQL statements require escaping of some characters to be valid, and also to be secure from serious SQL injection vulnerabilities (through which anyone may tamper with your database). Start with [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and begin learning to use PDO instead of `mysql_query()`, as the latter is an old, now deprecated API. No new code should be written with the `mysql_*()` functions, and since you are just learning, it is a great time to learn properly.. — Michael Berkowski, Nov 02 '14 at 22:27
This tutorial [PDO for mysql developers](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) is a good resource, which frames PDO and prepared statements in context of the old `mysql_*()` functions. — Michael Berkowski, Nov 02 '14 at 22:28

score 2 · Accepted Answer · edited May 23 '17 at 10:33

You need to do a number of things but to specifically add values with single quotes, etc you need to do something as simple as:

$reply_content = mysql_real_escape_string($_POST['reply_content']);

That will take $_POST['reply_content'] and add backslashes where necessary so it can be inserted into MySQL. Don't forget to reverse that if you are displaying data back to your users. You don\'t want them to see slashes where they aren\'t expecting them.

Also, based on your example you should...

If you have to use mysqli_, replace all your mysql_* functions with mysqli_* (mysql_* functions are deprecated replaced by their mysqli_* counterparts)
Sanitize any input from your users - don't trust what they send you. The comment from @Michael Berkowski pointing to "How can I prevent SQL injection in PHP" is a great start, "SQL Injection" is another

score 1 · Answer 2 · edited Nov 03 '14 at 06:20

Try this (see mysql_real_escape_string):

$reply_content = mysql_real_escape_string($_POST['reply_content']);

Also, some hints for a correct approach:

You are writing the Username from your Session into a hidden input field. Try it like this:

session_start();
$reply_user = $_SESSION['user_name'];

You have added an attribute 'textarea' in :

<textarea ... textarea>...

Remove this

Edit your HTML Output like this:

print "<form method='post' action='reply.php'>";
print "<h3>Reply:</h3>";

print "<br><textarea name='reply_content' rows='4' cols='50'></textarea>";
print "<input type='hidden' name='reply_user' value='{$_SESSION['user_name']}'>";
print "<input type='hidden' name='topic_id' value='$id'>";
print "<br><input type='submit'></form>";

Or like this:

// Some PHP Stuff
?>
<form method="post" action="reply.php">
<h3>Reply:</h3>
<textarea name="reply_content" rows="4" cols="50"></textarea><br/>
<input type="hidden" name="topic_id" value="<?php echo $id; ?>" />
<input type="submit" />
</form>
<?php
// Go on with php stuff

There's even more to do here but since it seems you're new to PHP this should be fine for now

Just to mention it: Your code is not safe against sql injection

Thank you. When I get this running, I'm going to switch to the new syntax and redo all my code. This is just for testing. But, thank you again! — sandorfalot, Nov 02 '14 at 22:55

score -1 · Answer 3 · answered Nov 02 '14 at 22:27

Try to use mysql_real_escape_string

$content = "<form method=post action=reply.php>";
$content .= "<h3>Reply:</h3>";

$content .= "<br><textarea name=reply_content textarea rows=4 cols=50></textarea>";
$content .=  "<input type=hidden name=reply_user value={$_SESSION['user_name']}>";
$content .=  "<input type=hidden name=topic_id value=$id>";
$content .=  "<br><input type=submit></form>";

$content = mysql_real_escape_string($content);

Thank you, but this didn't work. – sandorfalot Nov 02 '14 at 22:33 — sandorfalot, Nov 02 '14 at 22:33

Allowing punctuation to be submitted to MySQL db

3 Answers3