Ultimate Guestbook Tutorial: How to build a Guestbook with a honeypot, error checking, IP banning, pagination, e-mail notification and smilies with PHP and mySQL
In order to insert a guestbook entry into the database we need to establish a database connection and have a database with the correct structure. Start by making a new folder in the same directory as index.php, call it 'includes'. Next make a file called config.php and paste this code into it:
-
<?php
-
-
$host = 'localhost';
-
$username = 'yourusername';
-
$password = 'yourpassword';
-
$dbname = 'guestbook';
-
-
-
?>
Unfortunately i cannot help you here with the settings. You will need to ask your server administrator for them or find out for yourself. If you control the server then you should be able to make your own user name and password and create the database on your own. Make sure you have created the database and set its dbname above. Here is the database structure below, simply execute it to create it in your database:
-
CREATE TABLE `entries` (
-
`id` INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
-
`name` VARCHAR( 255 ) NOT NULL ,
-
`email` VARCHAR( 255 ) NOT NULL ,
-
`website` VARCHAR( 255 ) NOT NULL ,
-
`message` TEXT NOT NULL ,
-
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
-
`ip` VARCHAR( 15 ) NOT NULL
-
) ENGINE = MYISAM ;
Next open up index.php and the line after the first opening php tag and add:
-
include('includes/config.php');
Next refresh your index.php page in your browser and make sure no mysql errors popup. If you see no errors on the page that means you have successfully connected to the database (or you didn't read above and include the config.php file).
Now that we have a connection established we can start inserting the entries into the database. We will use the index.php page for this. This is because we will extend the error checking module of the site.
Open up your index.php page and:
Below:
-
$error['message'] = 'Please enter a message';
-
}
Add:
-
$postentry = @mysql_query("INSERT INTO `entries` (name, email, website, message, date, ip) VALUES ('".addslashes($_POST['name'])."', '".addslashes($_POST['email'])."', '".addslashes($_POST['website'])."', '".addslashes($_POST['message'])."', now(), '".$_SERVER['REMOTE_ADDR']."')");
-
}
This is our SQL statement that inserts the entry into our database. It inserts all the form information plus a little bit extra. It puts a timestamp on the post, so that we can show when the post was submitted, plus it also inserts the visitors IP address, so that we can blacklist the IP if we need to.
What you might notice is all the addslashes() functions in the SQL statement. This is an anti-hack0r method which eliminates the possibility of visitors doing sql-injection operations on the database. This is basically done through escaping the SQL statement and inserting their own SQL statement. This is done heavily with spam bots, which may bypass any user input restrictions and basically overtake your websites content. Or it can be used to expose private data in the database. It is quite a big vulnerability. So that's why it is there. If you don't know what i am talking about above, don't worry. You are somewhat safer than before basically.
If the SQL statement executes ok then the variable $postentry will be true and we will use that variable in our skin file now.
Open up the skin.php file and do:
Find:
-
<?php
-
}
-
?>
Add replace it with:
-
<?php
-
}
-
-
if($postentry == true){
-
?>
-
<tr>
-
<td valign="top">Guestbook entry posted successfully. Thank you.</td>
-
</tr>
-
<tr>
-
<td height="10"></td>
-
</tr>
-
<?php
-
}
-
?>
This checks to see if the SQL statement was true, if it was then it shows a success message. If it wasn't then it does nothing! And yes, that's right. I did make a success style. Here is the CSS you can add into your stylesheet:
-
.success {
-
border: #999999 solid 1px;
-
color: #FFFFFF;
-
background: #36c952;
-
font-size: 14px;
-
padding: 10px;
-
}
Before we get going on showing the posts i want to add some features to the form first. This includes some more error handling and the honeypot!
32 Responses to “Ultimate Guestbook Tutorial: How to build a Guestbook with a honeypot, error checking, IP banning, pagination, e-mail notification and smilies with PHP and mySQL”
By VLADIS
on Jun 2, 2008
I make this guestbook.
It go not me. It always write this errors:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /3w/wz.cz/m/medvede/5/includes/actions.php on line 3
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /3w/wz.cz/m/medvede/5/includes/functions.php on line 12
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /3w/wz.cz/m/medvede/5/templates/entries.php on line 15
i have it on http://medvede.wz.cz/5/index.php
Help me, please.
thank you
By VoiDeT
on Jun 2, 2008
Hey Vladis,
Did you make sure you have created the database correctly?
Please make sure you have done this, otherwise this error would definitely show up.
By VLADIS
on Jun 3, 2008
I make this TABLE :
CREATE TABLE `entries` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) collate latin1_general_ci NOT NULL,
`email` varchar(255) collate latin1_general_ci NOT NULL,
`website` varchar(255) collate latin1_general_ci NOT NULL,
`message` text collate latin1_general_ci NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;
and this:
CREATE TABLE `spam` (
`id` int(8) NOT NULL auto_increment,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;
I have in config.php this code:
And it doesn’t go.
By VLADIS
on Jun 3, 2008
I have in config.php this code:
$host = ‘mysql.webzdarma.cz’;
$username = ‘medvede48′;
$password = ‘xxx’;
$dbname = ‘guestbook’; - - I try also entries
$email = ‘your@email.com’;
$connect = mysql_connect($host, $username, $password);
$dbselect = mysql_select_db($dbname);
$items = 10;
By VoiDeT
on Jun 3, 2008
Can you please provide me with your ftp details?
It could be my end, or it could be your end. But i thought i tested this script without any rows in the database. Let me know
By VLADIS
on Jun 3, 2008
I have it on: photoshopsk.wz.cz
password: 7754705
I have files from this tutorial.
By VoiDeT
on Jun 4, 2008
And your username for me to log in please?
By VLADIS
on Jun 5, 2008
(https://www.webzdarma.cz/)
My username on FTP is : photoshopsk.wz.cz
and password: 7754705
(https://www.webzdarma.cz/mysql/index.php)
And username on mysql server is: photoshopsk
password:ragp3s
By VoiDeT
on Jun 7, 2008
Those settings do not work.
I need username, password, and address.
Otherwise i cannot look for you.
By VLADIS
on Jun 7, 2008
Look you:
1) http://photoshopsk.wz.cz/1/1.JPG
2) http://photoshopsk.wz.cz/1/2.JPG
3) http://photoshopsk.wz.cz/1/3.JPG
Do you thing this or no?
If no this, then what you think? What of address?
By Linnea
on Jun 9, 2008
Hi! I just want to say thank you for a wonderful tutorial. I will probably use this at my website when I have finished it, so I can send the link later. Thank you!
By VoiDeT
on Jun 9, 2008
@ Linnea - Thanks alot for your comment. I would love to see your website when you have finished with it!
@Vladis - Doesn’t work dude. Maybe you have limited the IP range of access?
By VLADIS
on Jun 11, 2008
My action what I make.
1.) I am download this tutorial: http://www.jotlab.com/wp-content/uploads/2008/04/guestbook.zip
2.) I give it on a web all. (http://photoshopsk.wz.cz/)
3.) I am create table :
CREATE TABLE `entries` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) collate latin1_general_ci NOT NULL,
`email` varchar(255) collate latin1_general_ci NOT NULL,
`website` varchar(255) collate latin1_general_ci NOT NULL,
`message` text collate latin1_general_ci NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;
and this:
CREATE TABLE `spam` (
`id` int(8) NOT NULL auto_increment,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;
4.) I chanqe in config.php on it :
AND IT NO GO.
You know where is mistake???
By Amanda
on Jun 11, 2008
Hello. I am trying to make a wedding website and want to add a guestbook feature. Everything seemed to be working okay but now I get two big errors.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.magdalen/danfrancis/www/attempt/includes/actions.php on line 3
and
Fatal error: Call to undefined function: pagination() in /home/.magdalen/danfrancis/www/attempt/templates/skin.php on line 48
Any clue what is going on? Any help would be great. The site it’s at right now (just testing it out) is: http://www.danfrancisphotography.com/attempt/index.php
THANKS AGAIN!
By Lily
on Jun 11, 2008
Hello there.
It doesn’t work at myhomepage.. Can you please tell me, what I’ve done wrong?
- Lily.
By VoiDeT
on Jun 11, 2008
Hey people,
I don’t know why you are having these problems. It sounds like an error in the SQL. I am happy to look on your server if you provide me with the correct FTP details or cpanel details.
I have installed this script from the zip file and it works fine.
Thank you
By Lily
on Jun 12, 2008
What do you mean with the correct FTP details or cpanel details?
By VoiDeT
on Jun 12, 2008
However you upload the files to your server,
so i can see what the problem is. Because i cant replicate it
By Lily
on Jun 12, 2008
The only thing I’ve changed is the MySQL otherwise I haven’t touched anything. The same text as Amanda got I have at my page.
By VoiDeT
on Jun 12, 2008
Yep,
what sql did you change?
the connection settings?
By eHobayyeb
on Jun 22, 2008
Amazing!
Everything works fine.
I am new PHPier and found many useful tips & tricks!
Keep it up VoiDeT, I will do all PHP tuts here.
Thanks
Mohammad
hattoon.com
By HCF
on Jul 5, 2008
Hi, awesome tutorial, shows exactly how to use the basics. 2 questions regarding your techniques:
1. What about using mySQLi instead of the usual mySQL (only PHP5, but way better), since it is faster and more secure.
2. I guess this was designed for beginner and advanced user, so it would be useful to show a lil bit of object oriented programming, since it makes the source code more accessible and php more flexible.
Awesome work, greetings from Germany.
By VoiDeT
on Jul 5, 2008
HCF!
Vielen dank für ihre nette antwort. Du hast auf Englisch geschreiben, so ich werde auf Deutsch antworten. Es freut mich so viel das du die tutorial magst. So danke noch mal. Ich hab noch nicht viele mysqli probiert. Aber ich weiß das es OOP ist. Für dieser tutorial will ich sehr einfach machen, aber vielleicht lern ich MySQLi für meine selber projekte.
Vielen dank noch mal,
By Fredrik
on Jul 7, 2008
Hi voidet,
I like the icons you use to identify the country, OS and browser. How did you do that?
By VoiDeT
on Jul 7, 2008
Firestats plugin mate
By Akira
on Jul 30, 2008
Nice guestbook. I like it.
By Dan Bunyard
on Aug 9, 2008
Awesome work! I have a guestbook on my existing site but….to put it mildly….it was hacked together to make it work. You have sure a great example here of the things you can do with PHP/MySQL. Keep up the great work, and happy coding!!
By Franklyn
on Aug 11, 2008
I havent read the code in detail but wouldnt it be better to do the error handling client side ?.
By VoiDeT
on Aug 12, 2008
Doing just client side opens you up to attacks.
This is a server side tutorial anyhow. By just doing client side error checking would be insecure and negligent.
By mm
on Aug 24, 2008
hi - this is really a great tutorial. Im very glad you did this as I was confused about how to add a honeypot.
listen, do you know how to add an ADMIN page/function to this GB, or is it already there? I was testing mine and managed to ban my own IP! I’m not sure how to manage the database at this point. Any help is appreciated.
Thanks!
By VoiDeT
on Aug 24, 2008
Hey,
Simply go into phpmyadmin and delete the entry in the banlist that is associated with your IP address
Thanks for the comments!
By Yousha
on Sep 11, 2008
This form needs CAPTCHA security image.
GL.