Comments not Getting Inserted into MySQL Table

Question

Hello,

I'm trying to use the code below for a comment system. It doesn't work. The info I'm trying to insert into the MySQL table "comment" isn't getting put there. Any idea(s) why it is not working?

Thanks in advance,

John

On comments.php:

echo '<form action="http://www...com/sandbox/comments/comments2.php" method="post"> 
    <input type="hidden" value="'.$_SESSION['loginid'].'" name="uid">
    <input type="hidden" value="'.$submissionid.'" name="submissionid">  

    <label class="addacomment" for="title">Add a comment:</label>
    <input class="commentsubfield" name="comment" type="title" id="comment" maxlength="1000">  

    <div class="commentsubbutton"><input name="submit" type="submit" value="Submit"></div> 
</form>
';

On comments2.php:

$comment = $_POST['comment'];
$uid = $_POST['uid'];
$subid = $_POST['submissionid'];


mysql_query("INSERT INTO comment VALUES (NULL, '$uid', '$subid', '$comment', NULL, NULL)");

Answer 1

try

$query = sprintf("INSERT INTO comment VALUES (NULL, '%s', '%s', '%s', NULL, NULL)", $uid, $subid, $comment);

mysql_query($query);

Answer 2

You need the field names for any INSERT statement. As I don't know the exact ones for your table, I'll make some guesses.

mysql_query("INSERT INTO comment(uid,subid,comment) VALUES($uid, $subid, $comment)");

Answer 3

Does it know what database to connect to? Does it know what schema to talk to?

Have you connected to the database? mysql_connect()

Have you selected the database? mysql_select_db()

Answer 4

Is it possible for your comment to have special chars like quotes and newlines? If it is, you can have problems in your SQL by just inserting this way. Before formatting the statement, you have to treat these situations.

Besides, you might also want to know about Cross Site Scripting (XSS) attacks. If someone writes Javascript code in the comments area, your site can have security problems.

Answer 5

If mysql_query() fails it returns false and mysql_error() can tell you why.
Also take a look at http://docs.php.net/security.database.sql-injection and either use mysql_real_escape_string() or prepared statements.

if ( !isset($_POST['comment'], $_POST['uid'], $_POST['submissionid']) ) {
  echo '<pre>Debug: Something is missing. _POST=',
    htmlspecialchars( print_r($_POST, 1) ),
    '</pre>';
  die;
}
$comment = mysql_real_escape_string($_POST['comment'], $mysql);
$uid = mysql_real_escape_string($_POST['uid'], $mysql);
$subid = mysql_real_escape_string($_POST['submissionid'], $mysql);

$query = "
  INSERT INTO
    comment
  VALUES
    (NULL, '$uid', '$subid', '$comment', NULL, NULL)
";
echo '<pre>Debug query=', htmlspecialchars($query), '</pre>';
$rc=mysql_query($query, $mysql);
if ( !$rc ) {
  die( htmlspecialchars(mysql_error()) );
}

Try this self-contained example (only an example, don't code it this way ;-))

<?php
session_start();
if ( !isset($_SESSION['loginid']) ) {
  login();
}
else if ( !isset($_POST['comment']) ) {
  showForm();
}
else {
  saveComment();
}

function saveComment() {
   if ( !isset($_POST['comment'], $_POST['uid'], $_POST['submissionid']) ) {
    echo '<pre>Debug: Something is missing. _POST=',
      htmlspecialchars( print_r($_POST, 1) ),
      '</pre>';
    die;
  }
  // insert correct values here:
  $mysql = mysql_connect('localhost', 'localonly', 'localonly') or die(mysql_error());
  mysql_select_db('test', $mysql) or die(mysql_error());

  $comment = mysql_real_escape_string($_POST['comment'], $mysql);
  $uid = mysql_real_escape_string($_POST['uid'], $mysql);
  $subid = mysql_real_escape_string($_POST['submissionid'], $mysql);

  $query = "
    INSERT INTO
      comment
    VALUES
      (NULL, '$uid', '$subid', '$comment', NULL, NULL)
  ";
  echo '<pre>Debug query=', htmlspecialchars($query), '</pre>';
  //$rc=mysql_query($query, $mysql);
  //if ( !$rc ) {
    //die( htmlspecialchars(mysql_error()) );
  //}
}


function login() {
  $_SESSION['loginid'] = rand(1, 100);
  echo 'Your new loginid is ', $_SESSION['loginid'],'<br />
    <a href="?">Continue</a>
  ';
}

function showForm() {
  $submissionid = rand(1000, 9999);
  echo '<div>submissionid=', $submissionid, '</div>';
  echo '<div>loginid=', $_SESSION['loginid'], '</div>';

  echo '<form action="?" method="post"> 
    <input type="hidden" value="'.$_SESSION['loginid'].'" name="uid">
    <input type="hidden" value="'.$submissionid.'" name="submissionid">  

    <label class="addacomment" for="title">Add a comment:</label>
    <input class="commentsubfield" name="comment" type="title" id="comment" maxlength="1000">  

    <div class="commentsubbutton"><input name="submit" type="submit" value="Submit"></div> 
  </form>
  ';
}

if this "works" compare it to your real application and find the (essential) differences.

Answer 6

Valid return values from yourform

Does

$comment = $_POST['comment'];
$uid = $_POST['uid'];
$subid = $_POST['submissionid'];

contain valid data?

SQL query valid

http://www.w3schools.com/sql/sql_insert.asp

What does mysql_query return

<?php
$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

?>

what mysql_error do you get for your query.

Use PDO instead of mysql_query()

I would advise you to have a look at PDO which does a lot of heavy lifting for you. It for example makes sure that your SQL query is safe because even if the comments was added to your database it would not be safe at all.

PHP security

You should always validate your users input to prevent SQL injection. Luckily when using PDO(using prepared statements which will also give you a speed boost)right this will be done for you behind the seens. Still I would advise you to read these quick security tips from PHP creator to secure your site.

---

Hopefully this tips will help you in any way.

Answer 7

I've written a simple script on how to insert comment in mysql via php (well, method used was not secure and simplest but may be good for learning purposes). Article name: How to Insert comments on any webpage.

Hope, it may be helpful.