PHP & SQL match but script still doesn't work

Question

I am having some issues for the last 5 hours. I've been trying to fix it and I can't seem to find a solution, so I hope you can help me!

So I have this vote.php script:

<?php
if(basename($_SERVER["PHP_SELF"]) == "vote.php") {
    die("403 - Access Forbidden");
}
echo "<h2 class=\"text-left\">Vote</h2><hr/>";
$earnedpoints = false;
$insertnew = false;
$time = time();
$redirect = "";
$account = $mysqli->real_escape_string(preg_replace("/[^A-Za-z0-9 ]/", '', @$_POST['name']));
$siteid = $mysqli->real_escape_string(@$_POST['votingsite']);
$checkacc = $mysqli->query("SELECT * FROM accounts WHERE name = '$account'");
$countcheckacc = $checkacc->num_rows;
$row = $checkacc->fetch_assoc();
if($countcheckacc == 0 && isset($_POST['submit'])) { 
    $funct_error =  "This account doesn't exist!";
}
if($row['loggedin'] > 0 && isset($_POST['submit'])) { 
    $funct_error =  "This account is logged in!";
} elseif ($account == '' && isset($_POST['submit'])) {
    $funct_error = 'You need to put in a username!';
} elseif(empty($_POST['votingsite']) && isset($_POST['submit'])){
    $funct_error = "Please select a voting site";
} elseif(isset($_POST['submit'])) {
    $checksite = $mysqli->query("SELECT * FROM ".$prefix."vote WHERE id = ".$siteid."");
    $countchecksite = $checksite->num_rows;
    if($countchecksite == 0 && isset($_POST['submit'])) {
        $funct_error = "Invalid voting site.";
    } else {
        $result = $mysqli->query("SELECT *, SUM(times) as amount FROM ".$prefix."votingrecords WHERE NOT account='' AND NOT account='0' AND account='".$account."' AND siteid = '".$siteid."'") or die('Error - Could not look up vote record!');
        $row = $result->fetch_assoc();
        $sitequery = $mysqli->query("SELECT * FROM ".$prefix."vote WHERE id = '".$siteid."'");
        $vsite = $sitequery->fetch_assoc();
        $gvp = $vsite['gvp'];
        $gnx = $vsite['gnx'];
        $timecalc = $time - $row['date'];
        if ($row['amount'] == '' || $timecalc > $vsite['waittime']) {
            if($row['amount'] == '') {
                $result = $mysqli->query("INSERT INTO ".$prefix."votingrecords (siteid, ip, account, date, times) VALUES ('".$siteid."', '".$ipaddress."', '".$account."', '".$time."', '1')") or die ('Error - Could not insert vote records!');
            } else {
                $result = $mysqli->query("UPDATE ".$prefix."votingrecords SET siteid = '".$siteid."', ip='".$ipaddress."', account='".$account."', date='".$time."', times='1' WHERE account='".$account."' AND siteid = '".$siteid."'") or die ('Error - Could not update vote records!');
            }
            $earnedpoints = true;
            if ($earnedpoints == true) {
                if ($account != '') {
                    $result = $mysqli->query("UPDATE accounts 
                                                SET $colvp = $colvp + $gvp, 
                                                    $colnx = $colnx + $gnx 
                                                WHERE name='".$account."'") or die ('Error - Could not give rewards. Your site administrator needs to configure the NX and VP settings.');
                }
                $funct_msg = '<meta http-equiv="refresh" content="0; url='.$vsite['link'].'">';
                $redirect = true;
            }
        } elseif($timecalc < $vsite['waittime'] && $row['amount'] != '') {
            $funct_msg = 'You\'ve already voted for '.$vsite['name'].' within the last '.round($vsite['waittime']/3600).' hours!';
            $funct_msg .= '<br />Vote time: '. date('M d\, h:i A', $row['date']);
        } else {
            $funct_error = 'Unknown Error';
        }
    }
}
if($redirect == true) {
    echo $funct_msg;
} else {
    if(isset($funct_msg)) {
        echo '<div class="alert alert-danger">'.$funct_msg.'</div>';
    }
    if(isset($funct_error)) {
        echo '<div class="alert alert-danger">'.$funct_error.'</div>';
    }
    $query = $mysqli->query("SELECT * from ".$prefix."vote");
    if($query->num_rows == 0){
        echo "<div class=\"alert alert-danger\">Your administrator has not added any voting sites yet!</div>";
    } else {
        echo "
        <form method=\"post\">
        <div class=\"form-group\">
        <label for=\"voteSite\">Select Site:</label>
        <select name=\"votingsite\" class=\"form-control\" id=\"voteSite\" required>
        <option value=\"\" disabled selected>Select Site...</option>";
        while($row = $query->fetch_assoc()){
            echo "<option value=\"".$row['id']."\">".$row['name']."</option>";
        }
        echo "</select>
        </div>";
        if(!isset($_SESSION['id'])) {
            echo "<input type=\"text\" name=\"name\" maxlength=\"15\" class=\"form-control\" placeholder=\"Username\" required autocomplete=\"off\"/><br/>";
        } else {
            echo "<input type=\"text\" name=\"name\" maxlength=\"15\" class=\"form-control\" placeholder=\"".$_SESSION['name']."\" value=\"".$_SESSION['name']."\" required autocomplete=\"off\"/><br/>";
        }
        echo "
            <input type=\"submit\" name=\"submit\" value=\"Submit &raquo;\" class=\"btn btn-primary\"/>
            </form>";
    }
}

I have this in votingrecords table in SQL: ip, account, date, times, amount. It's correct.

The problem I'm having is that whenever I type my username to vote for the game, I'm getting this error message "Error - Could not look up vote record!".

Here's the code for this error:

else {
    $result = $mysqli->query("SELECT *, SUM(times) as amount 
                                FROM ".$prefix."votingrecords 
                                WHERE NOT account='' 
                                AND NOT account='0' 
                                AND account='".$account."' 
                                AND siteid = '".$siteid."'") or die('Error - Could not look up vote record!');
    $row = $result->fetch_assoc();
    $sitequery = $mysqli->query("SELECT * FROM ".$prefix."vote WHERE id = '".$siteid."'");
    $vsite = $sitequery->fetch_assoc();
    $gvp = $vsite['gvp'];
    $gnx = $vsite['gnx'];
    $timecalc = $time - $row['date'];

I hope you guys can help me, since I've tried everything and cannot get it to work

php's error reporting and checking for errors on the queries could help shed some light on all this. As would looking at the html source and `var_dump()`. — Funk Forty Niner, May 12 '17 at 23:57
There are no errors showing, just the error message defined in the code `or die('Error - Could not look up vote record!');` — Mattx, May 13 '17 at 00:01
that isn't helping you. You need to check for the *real* error(s). — Funk Forty Niner, May 13 '17 at 00:02
Set to catch and display http://php.net/manual/en/function.error-reporting.php and [`mysqli_error($mysqli)`](http://php.net/manual/en/mysqli.error.php) on the queries, with an `if($X_query){...}` for all which might have knocked off a few hours ;-) — Funk Forty Niner, May 13 '17 at 00:05
If you cannot output a useful error message Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. — RiggsFolly, May 13 '17 at 00:05
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, May 13 '17 at 00:07
Yes, $prefix is defined and it equals to "bit_" so the table is called bit_votingrecords. @RST — Mattx, May 13 '17 at 00:08

score 0 · Answer 1 · answered May 13 '17 at 00:55

0

thanks for helping. I got this issue fixed by executing this SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; in MYSQL. This would not happen if @RiggsFolly didn't tell me to use this code to display real useful errors, so thank you.

Thanks all!

answered May 13 '17 at 00:55

Mattx

37
1
7

Please dont use answers to say thank you. You will only get downvotes. Instead its totally ok to post an answer stating a fix, and even to accept your own answer – RiggsFolly May 13 '17 at 01:10

PHP & SQL match but script still doesn't work

1 Answers1