[cpumatt]

So, I have sort of a test website here: http://8thdomain.netii.net/

And I have the making threads down. In fact, you yourself can make a topic, but that's not the thing at hand. I need to know how to make replies to these topics. I looked at other forums and found they have the thread ID in their link, so I did that. Other than that, I can add replies to the database, but have really no idea on how to print them out on a thread, or correlate that comment with the thread. phpmyadmin is really screwing with my head here.

index.php: (The part you need to know)

<?

	$query = "SELECT * FROM posts";
	$results = mysql_query($query);
	
	while ($row = mysql_fetch_array($results)) {
		echo "<font size='3'>Poster: <strong>Anonymous </strong> ID:(". $row['id']  .")</font>
		<a href='reply.php?id=" . $row['id'] . "'>[REPLY]</a>
		<br />";
		echo "<div class='wrap'><p>" . $row['body'] . "<p></div><br />";
	}
	
?>

Code that adds comments as a record in the database:

<?php
	
	require('includes/connect.php');
	
	$comment = $_POST['comment'];
	
	if(empty($comment)) {
		echo "No body supplied. Go <a href='index.php'>back</a>?";
	}
	else {
		$query = "INSERT INTO comments (comment) VALUES ('$comment')";
		mysql_query($query);
		echo "<h1><center>Success! Go <a href='index.php'>back</a>?</center></h1>";
	}
	
?>

Tables in phpmyadmin:
name: comments
fields: comment_id, comment

name: posts
fields: id, body

[creativecoding]

So, you're open up to XSS and SQL injection with that code. You're also not even checking if the query was successful. I suggest you ditch MySQL_* functions and start using PDO (or MySQLi). Also, sanitize your inputs!

[cpumatt]

creativecoding, on 01 October 2012 - 04:30 PM, said:

So, you're open up to XSS and SQL injection with that code. You're also not even checking if the query was successful. I suggest you ditch MySQL_* functions and start using PDO (or MySQLi). Also, sanitize your inputs!

I'm very aware of the security problems. I just want the base code down for now because it's not really for the public. I am currently learning how to use PDO's, and I don't know what sanitizing inputs mean, but please let's keep to the problem at hand! Every single thread I've made here there's always the guy who drags it in some random direction on how outdated some of my methods are.

[CTphpnwb]

The problem at hand is that you think security is less important than 'getting it to work'. It's more important because the act of improving your security will give you insights into not only making it work, but making it work better!

[creativecoding]

I always tell my boss "Security first, making it work second!"^*

It's much easier to practice good code/security from the beginning rather than making it work and trying to patch everything after.


^*this may be why I have no boss, or a job.

This post has been edited by creativecoding: 01 October 2012 - 06:22 PM

[Atli]

cpumatt, on 01 October 2012 - 11:19 PM, said:

I need to know how to make replies to these topics. I looked at other forums and found they have the thread ID in their link, so I did that. Other than that, I can add replies to the database, but have really no idea on how to print them out on a thread, or correlate that comment with the thread. phpmyadmin is really screwing with my head here.

Just for the record, because I can't leave this unsaid. phpMyAdmin is just a front-end for the MySQL database server. It's MySQL that's screwing with your head, really

But, OK. To address the question itself...

In order to add a comment to a thread, you will have to add a field to the comments table that stores the ID of the thread it belongs to. We call those Foreign Keys. In SQL, you create them like this:

CREATE TABLE posts (
    `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(100) NOT NULL,
    `text` LARGETEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE comments (
    `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `text` MEDIUMTEXT NOT NULL,
    `post_id` INTEGER NOT NULL,
    FOREIGN KEY (`post_id`)
        REFERENCES `posts`(`id`)
) ENGINE=InnoDB;

Note the "FOREIGN KEY ... REFERENCES" syntax. That is what links the "post_id" field in the "comments" table to the "posts" table.

Now when you create a comment, you'll have to include a post ID. Otherwise MySQL rejects the comment. The, to display the comments for a post, you just need to query the database for comments that match that post ID. Something like:

<?php
$dbLink = new mysqli("localhost", "user", "pwd", "schema");
if (!$dbLink) {
    trigger_error("MySQL connection failed: " . mysqli_connect_error(), E_USER_ERROR);
}

function printPostComments($postID) {
    if (!$postID || !is_int($postID) || $postID <= 0) {
        trigger_error("Invalid post ID", E_USER_WARNING);
        return;
    }
    
    // Import the global $dbLink. If you don't do this, you can't use it.
    global $dbLink;
    
    // Prepare the query for execution.
    $sql = "SELECT `id`, `text` FROM comments
            WHERE `blog_id` = ?";
    $stmt = mysqli_prepare($dbLink, $sql);
    
    if ($stmt) {
        // Add the post ID to the query.
        mysqli_stmt_bind_param($stmt, "i", $postID);

        // Execute the query.
        mysqli_stmt_execute($stmt);
        
        // Bind variables to the fields the database will return.
        mysqli_stmt_bind_result($stmt, $id, $text);
        
        // Loop through the result set and print each comment.
        while (mysqli_stmt_fetch($stmt)) {
            // Make sure the valuse are safe to be printed.
            $id = (int) $id;
            $text = htmlentities($text, ENT_QUOTES, "UTF-8");

            echo "<div class='comment'>
                    <h2>#{$id}</h2>
                    <p>{$text}</p>
                  </div>
                  ";
        }
        
        // Close the statement.
        $stmt->close();
    }
}

This post has been edited by Dormilich: 01 October 2012 - 10:14 PM
Reason for edit:: fixing typo

[cpumatt]

I'm getting a syntax error with this code:

CREATE TABLE posts (
    `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(100) NOT NULL,
    `text` LARGETEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE comments (
    `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `text` MEDIUMTEXT NOT NULL,
    `post_id` INTEGER NOT NULL,
    FOREIGN KEY (`post_id`)
        REFERENCES `posts`(`id`)
) ENGINE=InnoDB;

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LARGETEXT NOT NULL
) ENGINE=InnoDB' at line 3

[JackOfAllTrades]

It should be LONGTEXT, not LARGETEXT.

How did I find this? By going to the manual.

[no2pencil]

cpumatt, on 01 October 2012 - 07:35 PM, said:

I just want the base code down for now because it's not really for the public.

Not really...? I'm certain that there isn't a measure in internet existence. There either is or isn't. & if your site exists, scammers & automated security bots will find your site, & they will exploit it.

It's been a few years now, but there was a guy that was posting here for help. PHP/Database help, just like you. Take a guess what happened... Someone saw his posts here & forked the crap out of his database because they could see the backend code & how vulnerable it was. & how did they find his 'not really' publicly visible website? He posted the url, just like you did.

If it's on the internet, it's susceptible to internet based attacks.

You want your code to work, granted. We're telling you what we see because we've been there. If you are not going to take the proper measures to secure your code, then you may as well not even bother coding it.