In the phpBB world, the standard registration form allows for image verification. If any of you have used this you know you will still get pounded daily with spam programs that overcome the image verification quite easily.
TextConfirmation developed by http://bbantispam.com/tc/ is an phpBB add-in that uses a random text response question that overcomes autmomated bots.
This code provides a standardized set of routines to provide text confirmation on any PHP form.
First the MYSQL database, we have 2 tables (see Listing 1):
1) ov_question - contains the questions to ask
2) ov_answer - the possible answers to each question
Listing 1. ov.sql
CREATE TABLE ov_question (
question_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
question VARCHAR(100) NOT NULL,
PRIMARY KEY(question_id)
);
CREATE TABLE ov_answer (
answer_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
question_id INTEGER UNSIGNED NOT NULL,
answer VARCHAR(100) NOT NULL,
PRIMARY KEY(answer_id),
INDEX Index_2(question_id)
);
-- sample questions and answers
INSERT INTO ov_question (question) VALUES('Enter the name of a day of the week');
INSERT INTO ov_answer (question_id, answer) VALUES(1, 'sunday');
INSERT INTO ov_answer (question_id, answer) VALUES(1, 'monday');
INSERT INTO ov_answer (question_id, answer) VALUES(1, 'tuesday');
INSERT INTO ov_answer (question_id, answer) VALUES(1, 'wednesday');
INSERT INTO ov_answer (question_id, answer) VALUES(1, 'thursday');
INSERT INTO ov_answer (question_id, answer) VALUES(1, 'friday');
INSERT INTO ov_answer (question_id, answer) VALUES(1, 'saturday');
INSERT INTO ov_question (question) VALUES('Enter a number greater than 5 and less than 8');
INSERT INTO ov_answer (question_id, answer) VALUES(2, '6');
INSERT INTO ov_answer (question_id, answer) VALUES(2, '7');
INSERT INTO ov_question (question) VALUES('How much is 2 plus 2?');
INSERT INTO ov_answer (question_id, answer) VALUES(3, '4');
So, we need a couple of access points:
1) get a question to ask, should be random amongst the set
2) see if the answer provided was valid
3) for management of the set, get and replace the entirety
These functions are provided in Listing 2 (ov.php):
<?php
// ==================================================================
// Author: Dan Toomey(info@oxyscripts.com)
// Web: http://oxyscripts.com
// Name: oxyscripts_verify
// Desc: Class to test whether a human is filling out a form
//
// Please send me a mail telling me what you think of oxyscripts_verify
// and what your using it for or post on the forum
//
// ==================================================================
// ==================================================================
// oxscripts_verify Constants
define("OV_VERSION","1.00");
define("OV", "http://www.oxyscripts.com/item-1159.html");
// ==================================================================
// The Main Class
class oxyscripts_verify {
var $number_of_questions = 0; //how many questions are in the system
var $question_id = 0; //question asked
var $debug = 0; //echo arguments
var $errors = 0; //show error messages
var $last_error = ""; //last error message
var $dbh; //database handle/link
/**
* constructor
*
* @access public
* @param $dbuser string database username
* $dbpassword string database password
* $dbname string database name
* $dbhost string database server
*/
function oxyscripts_verify($dbuser, $dbpassword, $dbname, $dbhost) {
if ($this->debug) {
echo "username = " . $dbuser . "<br/>" .
"psw = " . $dbpassword . "<br/>" .
"name = " . $dbname . "<br/>" .
"host = " . $dbhost . "<br/>";
}
$this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword);
if ( ! $this->dbh )
$this->print_error(0,"<ol><b>Error establishing a database connection!</b><li>Are you sure you have the correct user/password?<li>Are you sure that you have typed the correct hostname?<li>Are you sure that the database server is running?</ol>");
if ( !@mysql_select_db($dbname,$this->dbh))
$this->print_error(0,"<ol><b>Error selecting database $dbname!</b><li>Are you sure it exists?<li>Are you sure there is a valid database connection?</ol>");
}
function __destruct(){
if ($this->dbh) {
//free database @mysql_close();
}
}
function print_error($query=0, $error=0) {
if ($this->errors) {
if ($query)
echo $query;
if ($this->dbh)
echo @mysql_error($dbh);
if ($error)
echo $error;
}
$this->last_error = $error;
die;
}
/**
* pick a question for your form
*
* @access public
* @return string question to ask user on form
*/
function pick() {
//determine how many questions are in system
if ($this->number_of_questions==0) {
$query = "SELECT COUNT(*)
FROM ov_question";
$this->result = @mysql_query($query,$this->dbh);
if (! $this->result)
$this->print_error($query,0);
$row = @mysql_fetch_row($this->result);
$this->number_of_questions = $row[0];
@mysql_free_result($this->result);
if ($this->debug)
echo "number of questions=" . $this->number_of_questions . "<br/>";
if ($this->number_of_questions==0)
$this->print_error($query,"No questions found?");
}
//pick one
srand((double)microtime()*1000000);
$this->question_id = rand(1,$this->number_of_questions);
if ($this->debug)
echo "picked question # = $this->question_id<br/>";
//grab it
$query = "SELECT question
FROM ov_question
WHERE question_id = $this->question_id";
$this->result = @mysql_query($query,$this->dbh);
if (! $this->result)
$this->print_error($query,0);
$row = @mysql_fetch_row($this->result);
$question = $row[0];
@mysql_free_result($this->result);
return $question;
}
/**
* validate a response to a question
*
* @access public
* @param $question_id int
* $response string the answer the user typed in
* @return boolean true if answer is valid
*/
function validate($response) {
$query = "SELECT COUNT(*)
FROM ov_answer
WHERE question_id = $this->question_id
AND answer = '" . $response . "'";
$this->result = @mysql_query($query,$this->dbh);
if (! $this->result)
$this->print_error($query,0);
$row = @mysql_fetch_row($this->result);
@mysql_free_result($this->result);
return $row[0];
}
/**
* update the entire database set
*
* @access public
* @param $questions complex array each element contains array with:
* 'question'
* 'answers' array
*/
function put($questions) {
//reset tables
$query = "TRUNCATE TABLE ov_question";
$this->result = @mysql_query($query,$this->dbh);
$query = "TRUNCATE TABLE ov_answer";
$this->result = @mysql_query($query,$this->dbh);
if (! $this->result)
$this->print_error($query,0);
//for each question
$this->number_of_questions = 0;
foreach($questions as $question_info) {
//add the question
$question = $question_info['question'];
$query = "INSERT INTO ov_question (question) VALUES('" . $question . "')";
$this->result = @mysql_query($query,$this->dbh);
if (! $this->result)
$this->print_error($query,0);
$question_id = @mysql_insert_id($this->dbh);
//add the answers
$answers = $question_info['answers'];
foreach($answers as $answer) {
$query = "INSERT INTO ov_answer (question_id, answer) VALUES($question_id, '" . $answer . "')";
$this->result = @mysql_query($query,$this->dbh);
if (! $this->result)
$this->print_error($query,0);
}
$this->number_of_questions++;
}
}
/**
* retrieve the entire database set
*
* @access public
* @return $questions complex array each element contains array with:
* 'question'
* 'answers' array
*/
function get() {
//get the whole thing at once
$query = "SELECT ov_question.question_id, question, answer
FROM ov_question, ov_answer
WHERE ov_question.question_id = ov_answer.question_id
ORDER BY ov_question.question_id, answer";
$this->result = @mysql_query($query,$this->dbh);
if (! $this->result)
$this->print_error($query,0);
//setup variables so they exist out of loop scope
$questions = array();
$last_question_id = 0;
$answers = array();
$question_info = array();
//walk through every row
while($row = @mysql_fetch_assoc($this->result)) {
//break out columns
$question_id = $row['question_id'];
$question = $row['question'];
$answer = $row['answer'];
//start a new question
if ($question_id != $last_question_id) {
//save last set off
if ($last_question_id!=0) {
$question_info['answers'] = $answers;
$questions[] = $question_info;
}
//start a new one
$question_info = array();
$question_info['question_id'] = $question_id;
$question_info['question'] = $question;
$answers = array();
//rem where we are
$last_question_id = $question_id;
}
//add in this answer
$answers[] = $answer;
}
//save off last one
$question_info['answers'] = $answers;
$questions[] = $question_info;
@mysql_free_result($this->result);
return $questions;
}
}
?>
This is all wrapped in an object, oxscripts_verify. The constructor needs the database credentials to access the database.
The pick() function selects a random question and returns the question to ask. A public member, $ov->question_id, contains the id of the question. The idea is you would either store question_id in a hidden form variable or in a session variable for later confirmation.
Example using pick:
<?php
$ov = new oxyscripts_verify(OV_DB_USER, OV_DB_PASSWORD, OV_DB_NAME, OV_DB_HOST);
$q = $ov->pick();
?>
The validate() function tests whether the response provided matches any of the possible answers to the question being asked, returns 0 or 1=correct. Example:
<?php
$ov = new oxyscripts_verify(OV_DB_USER, OV_DB_PASSWORD, OV_DB_NAME, OV_DB_HOST);
$ov->question_id = $question_id; //probably from a hidden form field
$test = $ov->validate('ok');
//do something with the result
?>
The get() function returns a complex array where every element looks like:
- question_id
- question
- answers[]
The put function takes the same array and updates the database with the question set.
The get and put functions would be used by a maintenance program to update the questions used in the system. A later tutorial will provide a smarty version of this maintenance. |
