Logical Captcha from SQL Table and external check

Question

This is my first post here so don't hate if I do something in the wrong way. I want to make a captcha for my site with logical questions. I think it is better than reCaptcha. Anyway, I made a fancy code with lots of "Googleing" but it must ahve some mistakes and I also would like to know, how to validate the answer from the database. My database looks like this:

id question answer

1 example? exampleanswer

My current code is this:

<?php


  $database_db="general";
  $user_db="root";
  $password_db="somepass";
  $host_db="localhost";

  $link=mysql_connect($host_db,$user_db,$password_db) or die ("couldnot connect: ".mysql_error());
  mysql_select_db($database_db, $link)  or exit('Error Selecting database: '.mysql_error()); ;

  $query = "SELECT * FROM `captcha` ORDERED BY RAND() LIMIT 1";
  $question=mysql_query($query);
  $answer=$_POST["answer"];

  $errormessage = "";

  $sql="SELECT * FROM captcha where question='$question' and answer='$answer'";
  $result = mysql_query($sql, $link)  or exit('$sql failed: '.mysql_error()); 
  $num_rows = mysql_num_rows($result);
  if($num_rows==0){
  header("Location: error.php");
  } else {
  header("Location: success.php");
  exit;
  }
?>
<html>
    <body>
        <form method="post">
            <?php echo $question; ?>
            <input type="text" name="answer" id="answer" />
            <input type="submit" name="submit" value="submit" />
        </form>
    </body>
</html>

Success.php is a simple file where the user goes if he did everything well, and error.php is a file which contains the following code:

<?php echo "Captcha is not valid! Please try again"; ?>

Also please note what would you change to optimize this code. If it is ready, I also have an other question: How to implement it in any other, external website.

Thank you for your help, MLL

score 1 · Answer 1 · answered Apr 16 '13 at 12:29

1

It looks like you are trying to both pose a question and check the answer within a single server generated page. A more workable approach might be if the question is asked on one page, and the answer posted and checked by a subsequent request/response.

Are you sure about your 'logical question' plan? One of the reasons captchas are so prevalent is they are designed to have a single, unambiguous, 'correct' answer.

I fear you may get into problems when asking 'what animal chases cats?'. You'd have to then decide which answers out of 'dog', 'dogs', 'a dog', 'the dog', 'hotdog', 'dogfish', 'a labrador', etc are 'correct'

My apologies if I have misunderstood your query.

answered Apr 16 '13 at 12:29

paul

21,653
1
53
54

Van you help with the code? Thank you very much! I am also sure about this. I will have questions like what is the third letters of the abc. I will make questions when the script is ready – MLL Apr 16 '13 at 12:35
In which alphabet? Latin, Asian, Cyrillic, Middle Eastern? Do you mean 'C', 'c', 'Ç', or 'ç'? You may be end up limiting your site to only an English speaking audience, or only an audience that subscribes to the same logic that you do. Please reconsider using a ready-made captcha that will also be much easier to implement. – paul Apr 16 '13 at 13:08
Sorry. I will test my questions, but my site http://malamut.hu is totally Hungarian so I will write questions in Hungarian. Anyway, thank you for correcting my mistake :) – MLL Apr 16 '13 at 13:10

Kickstart · Accepted Answer · 2013-04-16T12:52:24.603

Quick clean up of your code for you (but not tested)

<?php

    $database_db="general";
    $user_db="root";
    $password_db="somepass";
    $host_db="localhost";

    $link = mysqli_connect($host_db, $user_db, $password_db, $database_db);

    /* check connection */
    if (mysqli_connect_errno()) 
    {
        die ("couldnot connect: ".mysqli_connect_error());
        exit();
    }   

    if (array_key_exists("answer", $_POST) AND array_key_exists("id", $_POST))
    {
        $id = intval($_POST['id']);
        $sql="SELECT question, answer FROM captcha WHERE question='$id' AND answer='".mysqli_real_escape_string($link, $answer)."'";
        $result = mysqli_query($link, $sql)  or exit('$sql failed: '.mysqli_error($link)); 
        $num_rows = mysqli_num_rows($result);
        if($num_rows==0)
        {
            header("Location: error.php");
        } 
        else 
        {
            header("Location: success.php");
        }
        exit;
    }
    else
    {
        $query = "SELECT id, question FROM `captcha` ORDER BY RAND() LIMIT 1";
        if ($result = mysqli_query($link, $query))
        {
            if ($row = mysqli_fetch_assoc($result)) 
            {
                $id = $row["id"];
                $question = $row["question"];
            }
        }
    }

?>
<html>
    <body>
        <form method="post">
            <?php echo $question; ?><br />
            <input type="hidden" name="question" id="question" value="<?php echo $id; ?>" />
            <input type="text" name="answer" id="answer" /><br />
            <input type="submit" name="submit" value="submit" /><br />
        </form>
    </body>
</html>

Added some checks of data (you should use mysqli_real_escape_string to avoid SQL injection - or force the input to an integer). Switched to mysqli_ functions from mysql_ functions.

Note the gaping hole in the captcha is that you were storing something that related to the question / answer asked in the form. Hence a dodgy character could just over write the random one you have chosen with their own fixed choice. Better to store the question in a session variable

No doubt a few more things could be changed, but these are my initial changes.

Thank you for your fast reply! I will test this code if I am on my computer. Also, is the HTML part good? — MLL, Apr 16 '13 at 12:43
You should really have a header tags, etc in the HTML. Labels for the text input fields would help the user to know what to type where. You should put out the question and probably a number representing the question (ie, question 1 is such and such, question 2 is so and so). The suggestions made by @paul are also worth bearing in mind. Maybe worth having a table of answers and allowing the user to select the answer (most of the possible answers being wrong so you reject them). Should also point out that ORDER BY RAND() is a bit slow, but probably not an issue on a small table like this. — Kickstart, Apr 16 '13 at 12:49
Thank you! I will correct them. What do you suggest instead of ORDER BY RAND() ? — MLL, Apr 16 '13 at 12:56
For this with a very limited number of rows I wouldn't worry. But if you wanted, say, a random post on a forum with thousands of posts then it becomes an issue. It is fairly complicated to do though. A good post on here that may help is http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand — Kickstart, Apr 16 '13 at 13:05
Thanks :)It is better to think about future. I will also correct it :) — MLL, Apr 16 '13 at 13:07
Okey. I tested it and works absolutely perfectly! Thank you :) But I have a further question. (sorry :D) How to check if answer = question-answer in database? simply like: `if($answer = $question){something}`? — MLL, Apr 16 '13 at 15:23
Okay. I tried to understand the code again. It does not redirect to success.php even if my input is good.. — MLL, Apr 16 '13 at 15:35
You need to put $_POST['answer'] into $answer. Possibly with some basic checks first. — Kickstart, Apr 17 '13 at 08:34

Logical Captcha from SQL Table and external check

2 Answers2