Fmdpa Posted August 4, 2010 Share Posted August 4, 2010 I’m attempting to create a simple commenting system right now…at least you’d think it would be simple, just create an HTML form, write a PHP program to process it and pass the data off to MySQL. Well, it is not working at all. Here’s the simple code (the table/db is already created, I verified that in PMA): <?php$con = mysql_connect("localhost","root");if (!$con) { die('Couldn\'t connect:' . mysql_error()); }$database = "mysql_db"; $db = mysql_select_db($database) or die("Unable to select database:" . mysql_error());if ($_POST) { $date = date("Ymdhis"); $ip = $_SERVER['REMOTE_ADDR']; $name = $_POST['name']; $email = $_POST['email']; $post = $_POST['post']; $query = "INSERT INTO comment_test (date,ip,name,email,post) VALUES ($date,$ip,{$name},{$email},{$post})"; $result = mysql_query($query); if (!$result) die ("Database access failed: " . mysql_error());}?><!DOCTYPE HTML><html><body><form name="comment_form" method="POST" action="<?php $_SERVER['PHP_SELF'] ?>"><label for="name">Name:</label> <input type="text" maxlength="75" name="name"><label for="email">E-mail:</label> <input type="email" maxlength="75" name="email"><label for="post">Your comment:</label> <textarea name="post" rows="5" cols="30" maxlength="1000"></textarea><input type="submit" name="submit" value="Submit!"></form></body></html> I am not at all focusing on security yet. I’m just trying to get the basics to work and go from there. This code didn’t work. I got an error saying: Database access failed: 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 '.0.1,Me,me@localhost.com,Sample message…testing)' at line 1 First of all, I don't understand where the line number came from. Line 1 is simply "<?php".But I am thinking it has something to do with how I’m interpolating the variables into the MySQL query. Any thoughts? Link to comment Share on other sites More sharing options...
boen_robot Posted August 4, 2010 Share Posted August 4, 2010 That's line 1 from the SQL string, not the PHP file.I think it has to do with the way you're formatting the date. That's not a proper DATETIME format for MySQL.... I think.A proper format should be $date = date("Y-m-d H:i:s"); As for security, the only thing you should add from now, before you forget, is mysql_real_escape_string() on all $_POST data, like: $name = mysql_real_escape_string($_POST['name'], $con); $email = mysql_real_escape_string($_POST['email'], $con); $post = mysql_real_escape_string($_POST['post'], $con); I'd also add internationalization as a currently standing problem, but I'll let you find it out yourself before I bother you with the "why" and "how to solve it"... just add anything non latin, and view it at different applications. Link to comment Share on other sites More sharing options...
ShadowMage Posted August 4, 2010 Share Posted August 4, 2010 You forgot quotes:...VALUES ('$date','$ip','{$name}','{$email}','{$post}')"; Link to comment Share on other sites More sharing options...
Fmdpa Posted August 4, 2010 Author Share Posted August 4, 2010 That's line 1 from the SQL string, not the PHP file.I think it has to do with the way you're formatting the date. That's not a proper DATETIME format for MySQL.... I think.A proper format should be$date = date("Y-m-d H:i:s"); As for security, the only thing you should add from now, before you forget, is mysql_real_escape_string() on all $_POST data, like: $name = mysql_real_escape_string($_POST['name'], $con); $email = mysql_real_escape_string($_POST['email'], $con); $post = mysql_real_escape_string($_POST['post'], $con); I'd also add internationalization as a currently standing problem, but I'll let you find it out yourself before I bother you with the "why" and "how to solve it"... just add anything non latin, and view it at different applications. I am not submitting the date to a DATETIME column. Here's the table:id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, date INT UNSIGNED NOT NULL, ip VARCHAR(15), name VARCHAR(100), email VARCHAR(100), post TEXT NOT NULL I'm just using it as a way to order the posts, as an alternative to the auto_incrementing id. As for the security, I will run the mysql_real_escape_string function on the submitted strings, as well as the html_entities and the trim() functions. Will mysql_real_escape_string escape SQL wildcards, or just quotes? Link to comment Share on other sites More sharing options...
thescientist Posted August 4, 2010 Share Posted August 4, 2010 i would imagine it would be advisable to still maintain an auto-incrementing id (to be used as the primary key) for every new comment, and just add the date as an additional field. Link to comment Share on other sites More sharing options...
boen_robot Posted August 4, 2010 Share Posted August 4, 2010 Do htmlentities() only on outputting the comments, not on entering them in the DB. It's safer that way (if your DB gets hacked and HTML is injected to it, you can still render that HTML useless).mysql_real_escape_string() escapes any characters that may cause the string to not be a "string" in a MySQL context. In the case of wildcard strings, such as ones for the LIKE statement, this is still a string in a MySQL context. The fact that its contents have a special meaning is not something mysql_real_escape_string() is concerned with. So "No". MySQL does not escape wildcard characters. Link to comment Share on other sites More sharing options...
Fmdpa Posted August 4, 2010 Author Share Posted August 4, 2010 I got the comment to successfully insert into the table. I am now trying to retrieve it. I tried this: $query = "SELECT name,post FROM comment_test"$result = mysql_query($query, $con);print $result; I cannot get any other text to display than "Resource id #4". What's wrong? Link to comment Share on other sites More sharing options...
thescientist Posted August 4, 2010 Share Posted August 4, 2010 because all you're getting is a resource. you need to use PHP to further act upon it and "break apart". Consider using a while loop to extract the data. See here: http://www.w3schools.com/php/php_mysql_select.asp Link to comment Share on other sites More sharing options...
Fmdpa Posted August 4, 2010 Author Share Posted August 4, 2010 Thanks, that solved it. Now how do I cancel the comment submission process if the submitted comment already exists in the table? Link to comment Share on other sites More sharing options...
ShadowMage Posted August 4, 2010 Share Posted August 4, 2010 Now how do I cancel the comment submission process if the submitted comment already exists in the table?Depends on how you're storing the comments. You could check for a record with the same comment text using a select:$query = "SELECT name,post FROM comment_test WHERE post = '".$commentText."'";Then you'd check the resultset with mysql_num_rows and if it's greater than 0, don't run your insert query. Link to comment Share on other sites More sharing options...
Fmdpa Posted August 5, 2010 Author Share Posted August 5, 2010 Here's a more complete script now. <?phprequire_once 'db.php'; //connection file $page_mode = isset($_POST['page_mode']) ? $_POST['page_mode'] : '';$error_string = '';if ($page_mode == 'register'){ $email = trim($_POST['email']); $name = trim($_POST['name']); $post = trim($_POST['post']); if (!isValidEmail($email)) $error_string .= 'Please enter a valid email address.<br/>'; if ($name == '') $error_string .= 'Please enter your name.<br/>'; if ($post == '') $error_string .= 'Please enter your comment.<br/>'; if ($error_string == '') { $result = db_query("SELECT id FROM users WHERE post='" . mysql_real_escape_string($post) . "' AND '" . $ip . "'"); //ensure the user has not double posted a comment if (mysql_num_rows($result) > 0) $error_string .= 'You already posted the comment.<br/>'; else { $date = date("Y-m-d h:i:s"); $ip = $_SERVER['REMOTE_ADDR']; $email = mysql_real_escape_string($email); $name = mysql_real_escape_string($name); $post = mysql_real_escape_string($post); $query = "INSERT INTO comment_test (date,ip,name,email,post) VALUES ('$date','$ip','{$name}','{$email}','{$post}')"; mysql_query($query); header('location:page.php'); } }}//select previously posted comments//find comment$query = "SELECT name,post FROM comment_test ORDER BY id ASC"; $result = mysql_query($query); if (!$result) die ("Database access failed: " . mysql_error()); if ($result) header('location:page.php'); // display the comment while ($post = mysql_fetch_array($result)) { print '<span style="color:#888;">Posted by:</span> <b>' . $post['name'] . '</b>'; print '<br/>'; print '<div style="color:white; background-color:#111; width:300px; border-left:2px solid #888; border-bottom:1px dotted #444; border-top:1px dotted #444; border-right:1px dotted #444; padding:10px; position:relative; left:10px; top:10px;">"' . $post['post'] . '"</div>'; print '<br/></br/>';}function isValidEmail($email = ''){ return preg_match("/^[\d\w\/+!=#|$?%{^&}*`'~-][\d\w\/\.+!=#|$?%{^&}*`'~-]*@[A-Z0-9][A-Z0-9.-]{1,61}[A-Z0-9]\.[A-Z]{2,6}$/ix",$email);}?><!DOCTYPE HTML><html> <head> <title>Register</title> <style type="text/css">.error_text { color: red; width: 400px; text-align: center; font-weight:bold;}html { background-color:#000; color:#fff;}[type=text],[type=email], textarea { background-color:#111; border:1px solid #555; color:#fff;}</style> </head><body> <div class="error_text"><?php echo $error_string; ?></div><form name="comment_form" method="POST" action="<?php $_SERVER['PHP_SELF'] ?>"> <input type="hidden" name="page_mode" value="register"> <h3 for="name">Name:</h3> <input type="text" maxlength="75" name="name"> <h3 for="email">E-mail:</h3> <input type="email" maxlength="75" name="email"> <h3 for="post">Your comment:</h3> <textarea name="post" rows="5" cols="30" maxlength="1000"></textarea> <input type="submit" name="submit" value="Submit"></form> </form></body></html> It should have form validation, comment posting and a section of displayed comments. The comments display just fine, but the comment submission and validation is not so smooth. What did you come across that needs to be changed? Link to comment Share on other sites More sharing options...
boen_robot Posted August 5, 2010 Share Posted August 5, 2010 Apart from "db_query", nothing that would break anything... what's the db_query() function anyway? Shouldn't that be another mysql_query() instead? Link to comment Share on other sites More sharing options...
Fmdpa Posted August 5, 2010 Author Share Posted August 5, 2010 I forgot to change that. BTW, the validation is based off of the tutorial by justsomeguy on this forum (php tips and tutorial). Link to comment Share on other sites More sharing options...
Fmdpa Posted August 5, 2010 Author Share Posted August 5, 2010 The form validation still isn't working for me. Link to comment Share on other sites More sharing options...
thescientist Posted August 5, 2010 Share Posted August 5, 2010 which part? it doesn't appear you are checking for errors. Are you checking the input so you can track it to make sure the script is working the way its supposed to? Link to comment Share on other sites More sharing options...
Fmdpa Posted August 5, 2010 Author Share Posted August 5, 2010 Here's the purpose of this entire script:1. A person can post a comment (while his email and name are given)2. PHP functions (should) check whether he filled the name, email and post fields and (should) check whether the email is valid3. The database is checked to see if that comment has been double posted by the user (ip).4. If all's well, the comment (along with the time, ip, name and email) is submitted to the database/table. 5. The page displays all submitted commentsRight now, the comments submit and display just fine (and don't submit if the fields are blank), but no errors are displayed. That is what I'm trying to figure out. Link to comment Share on other sites More sharing options...
ShadowMage Posted August 5, 2010 Share Posted August 5, 2010 Check the HTML Source code. Does the following div have any text in it:<div class="error_text"><?php echo $error_string; ?></div>If not you need to place echos in your code to see what course the code is taking. Ie: if (!isValidEmail($email)) { $error_string .= 'Please enter a valid email address.<br/>'; echo "Email Error";} if ($name == '') { $error_string .= 'Please enter your name.<br/>'; echo "Name Error";} if ($post == '') { $error_string .= 'Please enter your comment.<br/>'; echo "Post Error";} If the echo's don't show up, and they're supposed to, then something is causing your logic to fail. Link to comment Share on other sites More sharing options...
Fmdpa Posted August 5, 2010 Author Share Posted August 5, 2010 I tried your test, and the echos did not work when the if statements should have returned true. I modified the entire code, and found something interesting. This code validated just fine, but does not retrieve the comments: <?phprequire_once 'db.php';$page_mode = isset($_POST['page_mode']) ? $_POST['page_mode'] : '';$error_string = '';if ($page_mode == 'Post'){ $email = trim($_POST['email']); $name = trim($_POST['name']); $post = trim($_POST['post']); $ip = $_SERVER['REMOTE_ADDR']; $date = date("Y-m-d h:i:s"); if (!isValidEmail($email)) $error_string .= 'Please enter a valid email address.<br/>'; if ($name == '') $error_string .= 'Please enter your name.<br/>'; if ($post == '') $error_string .= 'Please enter your comment.<br/>'; if ($error_string == '') { $result = mysql_query("SELECT id FROM comment_test WHERE post='" . mysql_real_escape_string($post) . "' AND ip='" . mysql_real_escape_string($ip) . "'"); if (mysql_num_rows($result) > 0) $error_string .= 'Your comment was already submitted.<br/>'; else { $email = mysql_real_escape_string($email); $name = mysql_real_escape_string($name); $post = mysql_real_escape_string($post); $ip = $_SERVER['REMOTE_ADDR']; $date = date("Y-m-d h:i:s"); mysql_query("INSERT INTO comment_test (date,ip,name,email,post) VALUES ('$date','$ip','{$name}','{$email}','{$post}')"); header('Location: page.php'); } }}function isValidEmail($email = ''){ return preg_match("/^[\d\w\/+!=#|$?%{^&}*`'~-][\d\w\/\.+!=#|$?%{^&}*`'~-]*@[A-Z0-9][A-Z0-9.-]{1,61}[A-Z0-9]\.[A-Z]{2,6}$/ix",$email);}?><!DOCTYPE HTML><html> <head> <title>Post a comment</title> <style type="text/css"> .error_text { color: red; width: 400px; text-align: center; } .left_box { float: left; width: 150px; text-align: right; padding-right: 5px; } .right_box { clear: right; } html { background-color:#000; color:#fff;}[type=text],[type=email], textarea { background-color:#111; border:1px solid #555; color:#fff;} </style> </head> <body> <div class="error_text"><?php echo $error_string; ?></div> <form action="<?php $_SERVER['PHP_SELF'] ?>" method="post"> <input type="hidden" name="page_mode" value="Post"> <div class="left_box">Email address</div> <div class="right_box"><input type="text" name="email" size="30" maxlength="255" value="<?php if (isset($email)) echo $email; ?>"></div> <div class="left_box">Name</div> <div class="right_box"><input type="text" name="name" size="30" maxlength="255" value="<?php if (isset($name)) echo $name; ?>"></div> <div class="left_box">Your comment</div> <div class="right_box"><textarea rows="5" cols="27" maxlength="800" name="post"></textarea></div> <div class="left_box"> </div> <div class="right_box"><input type="submit" value="Post" size="30"></div> </form> </body></html> But this code (which fetched the previously posted comments from the db) caused the validation to stop functioning again: <?phprequire_once 'db.php';//select previously posted comments (THE CULPRIT)//find comment$query = "SELECT name,post FROM comment_test ORDER BY id ASC"; $result = mysql_query($query); if (!$result) die ("Database access failed: " . mysql_error()); if ($result) header('location:page.php'); // display the comment while ($post = mysql_fetch_array($result)) { print '<span style="color:#888;">Posted by:</span> <b>' . $post['name'] . '</b>'; print '<br/>'; print '<div style="color:white; background-color:#111; width:300px; border-left:2px solid #888; border-bottom:1px dotted #444; border-top:1px dotted #444; border-right:1px dotted #444; padding:10px; position:relative; left:10px; top:10px;">"' . $post['post'] . '"</div>'; print '<br/></br/>';}$page_mode = isset($_POST['page_mode']) ? $_POST['page_mode'] : '';$error_string = '';if ($page_mode == 'Post'){ $email = trim($_POST['email']); $name = trim($_POST['name']); $post = trim($_POST['post']); $ip = $_SERVER['REMOTE_ADDR']; $date = date("Y-m-d h:i:s"); if (!isValidEmail($email)) $error_string .= 'Please enter a valid email address.<br/>'; if ($name == '') $error_string .= 'Please enter your name.<br/>'; if ($post == '') $error_string .= 'Please enter your comment.<br/>'; if ($error_string == '') { $result = mysql_query("SELECT id FROM comment_test WHERE post='" . mysql_real_escape_string($post) . "' AND ip='" . mysql_real_escape_string($ip) . "'"); if (mysql_num_rows($result) > 0) $error_string .= 'Your comment was already submitted.<br/>'; else { $email = mysql_real_escape_string($email); $name = mysql_real_escape_string($name); $post = mysql_real_escape_string($post); $ip = $_SERVER['REMOTE_ADDR']; $date = date("Y-m-d h:i:s"); mysql_query("INSERT INTO comment_test (date,ip,name,email,post) VALUES ('$date','$ip','{$name}','{$email}','{$post}')"); header('Location: page.php'); } }}function isValidEmail($email = ''){ return preg_match("/^[\d\w\/+!=#|$?%{^&}*`'~-][\d\w\/\.+!=#|$?%{^&}*`'~-]*@[A-Z0-9][A-Z0-9.-]{1,61}[A-Z0-9]\.[A-Z]{2,6}$/ix",$email);}?><!DOCTYPE HTML><html> <head> <title>Post a comment</title> <style type="text/css"> .error_text { color: red; width: 400px; text-align: center; } .left_box { float: left; width: 150px; text-align: right; padding-right: 5px; } .right_box { clear: right; } .error_text { color: red; width: 400px; text-align: center; font-weight:bold;}html { background-color:#000; color:#fff;}[type=text],[type=email], textarea { background-color:#111; border:1px solid #555; color:#fff;} </style> </head> <body> <div class="error_text"><?php echo $error_string; ?></div> <form action="<?php $_SERVER['PHP_SELF'] ?>" method="post"> <input type="hidden" name="page_mode" value="Post"> <div class="left_box">Email address</div> <div class="right_box"><input type="text" name="email" size="30" maxlength="255" value="<?php if (isset($email)) echo $email; ?>"></div> <div class="left_box">Name</div> <div class="right_box"><input type="text" name="name" size="30" maxlength="255" value="<?php if (isset($name)) echo $name; ?>"></div> <div class="left_box">Your comment</div> <div class="right_box"><textarea rows="5" cols="27" maxlength="800" name="post"></textarea></div> <div class="left_box"> </div> <div class="right_box"><input type="submit" value="Post" size="30"></div> </form> </body></html> I can't find what is wrong though. Link to comment Share on other sites More sharing options...
justsomeguy Posted August 5, 2010 Share Posted August 5, 2010 The code to display every has a redirect if the query works (I'm not sure why, but it does). That redirect removes everything from $_POST. Link to comment Share on other sites More sharing options...
Fmdpa Posted August 5, 2010 Author Share Posted August 5, 2010 Thanks! It works now that I've removed that redirect (I don't remember why I put it there either). And thanks for that great validation script (in your php tutorial). Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.