How to store the apostrophe into MySQL database from user input?

Question

I have a simple comments page a user enter a text into a textarea and the hit comment button to send the comment to a php page :

<?php
$reply = strip_tags($_POST['reply']);
$comment_id = strip_tags($_POST['id']);
$id = strip_tags($_POST['user_id']);
$date = strip_tags($_POST['date']);
$time = strip_tags($_POST['time']);
$server_root = "./";
if(file_exists("{$server_root}include-sql/mysql.class.php"))
{
    include_once("{$server_root}include-sql/mysql.class.php");
}
include_once("{$server_root}config.php");
$db1;
$db1 = new db_mysql($conf['db_hostname'],
                    $conf['db_username'],
                    $conf['db_password'],
                    $conf['db_name']);
$db1->query("SET NAMES utf8");
$current_server_date =  date('Y-m-d H:i:s');// Your local server time
date_default_timezone_set('Asia/Istanbul');
$current_pc_date = date('Y-m-d H:i:s'); 
$sql = $db1->query(
    'INSERT INTO replies1 (reply, comment_id, date, time, timestamp, user_id) 
    VALUES ("$reply", $comment_id, "$date", "$time", "$current_pc_date", $id)');
?>

the problem is : when a user enter any comment with apostrophe it does not store it in the database ? why does that happened? Is my code has something wrong? I added everything the double quotes and stripe_tags.? did i miss something?

This is because the values are all sent to the query unfiltered, and unescaped. `strip_tags()` is for sanitizing HTML for output later, but does nothing to escape or sanitize database input. The question I linked above thoroughly explores your options for correcting this (which is a SQL injection vulnerability); — Michael Berkowski, Feb 11 '14 at 14:40

score 2 · Answer 1 · answered Feb 11 '14 at 14:40

The strip_tags() seems unnecessary.

Instead, you should

either escape the DB input appropriately
or use prepared statements at the first place.

As you hide your MySQL implementation in an own class, I don't see how you implement these. How to escape or to prepare depends on the MySQL interface you use.

Keep in mind that mysql_*() is deprecated. You should either use mysqli or PDO.

score 2 · Accepted Answer · answered Feb 11 '14 at 14:44

2

You should escape all input which is coming directly from the user with mysqli_real_escape_string()! Otherwise its not only not working properly but its also highly unsafe to hacker-attacks. (mysql-injection)

answered Feb 11 '14 at 14:44

Martin

621
7
16

How to store the apostrophe into MySQL database from user input?

2 Answers2